Insertion dans un champ image

Bonjour,

je voudrais savoir comment insert-on un fichier en binaire dans une table a partir d'un fichier présent sur le disque en sql?

Merci

Essai un champs de type BLOB.

Tu lis le fichier et tu l'ecris dans ce champs.
Je l'ai pas essayé, mais sa doit marcher.
:?:

Le champ est obligatoirement de type image je peux pas le changer. Et je sais pas comment on lit le fichier pour l'insérer.

Voici un ptit exemple tiré de l'aide de ms sql2000 SP3.
j'ai pas eu assez de patience :x pour le lire en entier mais je pense que sa doit t'aider.

Managing Long Data Types
Long data types include ntext, text, and image data types. ntext, text, and image data can be so large that they cannot be retrieved in one operation or fit into memory. If the long data can fit into memory, the Value property of the Field object can be used to retrieve all the data in one operation. If the long data is too large to fit into memory, the data must be retrieved or written in chunks. You can manipulate long data in chunks through the Field object or through the Parameter object.

The Field object allows you to write and read long data through the Recordset object. The AppendChunk method of the Field object allows you to append data at the end of the current data when the query has already been executed. The GetChunk method allows you to read the data in chunks.

With the Parameter object, there is no GetChunk method, and there is no Recordset object when you are dealing with long data at run time. With the Parameter object, long data is bound at run time and executed with the Command object.

There are some restrictions for long data when using MSDASQL. If no server cursor is used, all long columns must be to the right of all nonlong columns. If there are multiple long columns, the long columns must be accessed in order (from left to right).

This example shows how to use ADO with SQLOLEDB to read and write image data. The critical routines are the while loops that copy the long data (image) to a variable and write the variable to a record in chunks (using the GetChunk and AppendChunk methods).

Before setting up the destination table in this example, make sure to run the sp_dboption stored procedure:

EXEC sp_dboption 'pubs', 'Select into/bulkcopy', 'True'

The destination table is a copy of the pub_info table in the pubs database. Create the table by running:

Code:

---

1 2 3 4

USE pubs SELECT * INTO pub_info_x FROM pub_info GO

---

The pub_info_x table is the destination table in which the long data will be inserted.

The ADO code is:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

Public Sub AppendChunkX()   Dim cn As ADODB.Connection Dim rstPubInfo As ADODB.Recordset Dim strCn As String Dim strPubID As String Dim strPRInfo As String Dim lngOffset As Long Dim lngLogoSize As Long Dim varLogo As Variant Dim varChunk As Variant   Const conChunkSize = 100   ' Open a connection. Set cn = New ADODB.Connection strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;"   cn.Provider = "sqloledb" cn.Open strCn   'Open the pub_info_x table. Set rstPubInfo = New ADODB.Recordset rstPubInfo.CursorType = adOpenDynamic rstPubInfo.LockType = adLockOptimistic rstPubInfo.Open "pub_info_x", cn, , , adCmdTable   'Prompt for a logo to copy. strMsg = "Available logos are : " & vbCr & vbCr   Do While Not rstPubInfo.EOF strMsg = strMsg & rstPubInfo!pub_id & vbCr & _ Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr rstPubInfo.MoveNext Loop   strMsg = strMsg & "Enter the ID of a logo to copy:" strPubID = InputBox(strMsg)   ' Copy the logo to a variable in chunks. rstPubInfo.Filter = "pub_id = '" & strPubID & "'" lngLogoSize = rstPubInfo!logo.ActualSize Do While lngOffset < lngLogoSize varChunk = rstPubInfo!logo.GetChunk(conChunkSize) varLogo = varLogo & varChunk lngOffset = lngOffset + conChunkSize Loop   ' Get data from the user. strPubID = Trim(InputBox("Enter a new pub ID:")) strPRInfo = Trim(InputBox("Enter descriptive text:"))   ' Add a new record, copying the logo in chunks. rstPubInfo.AddNew rstPubInfo!pub_id = strPubID rstPubInfo!pr_info = strPRInfo lngOffset = 0 ' Reset offset.   Do While lngOffset < lngLogoSize varChunk = LeftB(RightB(varLogo, lngLogoSize - _ lngOffset),conChunkSize) rstPubInfo!logo.AppendChunk varChunk lngOffset = lngOffset + conChunkSize Loop   rstPubInfo.Update   ' Show the newly added data. MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _ "Description: " & rstPubInfo!pr_info & vbCr & _ "Logo size: " & rstPubInfo!logo.ActualSize   rstPubInfo.Close cn.Close   End Sub

---

TOUJOURS :f1: :f1: :f1: :f1: TOUJOURS

D'une manière générale, l'insertion des images dans une base de données bien que déconseillée, n'est possible que depuis un outils clients comme ADO ou ADO.Net
Sachez que vous ne pouvez pas le faire en tout MS-SQL server.
Quel est votre language de programmation ?

Je voulias le faire en tout SQL mais sinon j'utilise du VBScript

Alors, utilise le code ADO qui t'a été donné.
N'oublie pas également de faire usage des objects parameter pour les performances !

Ok merci je vais essayer

Moi je l'ai fait en C++ (Avec ADO) si ça peut t'aider :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

//******************************************************************* // Met des données binaires dans un variant //******************************************************************* BOOL PutBinaryIntoVariant(COleVariant * ovData, BYTE * pBuf, long cBufLen) {   BOOL fRetVal = FALSE;     VARIANT var;   VariantInit(&var); //Initialize our variant     //Set the type to an array of unsigned chars (OLE SAFEARRAY)   var.vt = VT_ARRAY | VT_UI1;     //Set up the bounds structure   SAFEARRAYBOUND rgsabound[1];     rgsabound[0].cElements = cBufLen;   rgsabound[0].lLbound = 0;     //Create an OLE SAFEARRAY   var.parray = SafeArrayCreate(VT_UI1,1,rgsabound);     if(var.parray != NULL) {   void * pArrayData = NULL;     //Get a safe pointer to the array   SafeArrayAccessData(var.parray,&pArrayData);     //Copy bitmap to it   memcpy(pArrayData, pBuf, cBufLen);     //Unlock the variant data   SafeArrayUnaccessData(var.parray);     *ovData = var; // Create a COleVariant based on our variant   VariantClear(&var);   fRetVal = TRUE; }   return fRetVal; }   //Puis   //Où DBRst est un _RecordsetPtr et varBlob est un COleVariant remplis par PutBinaryIntoVariant...   this->DBRst->Fields->GetItem(_bstr_t(Fld))->AppendChunk(varBlob);

---

Citation:

---

Envoyé par Bradarys

Essai un champs de type BLOB.

---

Le type Large Object Binary sous MS-SQL est IMAGE !
Pas de type BLOB sous MS-SQL...