Inserting and Updating images file from VFP9 to SQL Server 2005

Inserting and Updating images file from VFP9 to SQL Server 2005

• Mike Wachal

  
  Hola he logrado grabar los datos como lo indicas , el detalle que tengo ahora es como visualizo esa imagen en un objeto image
  al usar strtofile(), el archivo que me crea dice que esta corrupto o danado, estoy usando windows vista, te agradeceria me ayudaras con eso.
  
  
  

• bbastiaensen

  Can you add a BLOB field to the SQL Server table That would be the easiest.

  I don't work with images and have never used an image field in SQL Server. I did try once with BLOB in SQL Server 2000. I assume SQL Server 2005 has the field type available too.

  If you cannot add a BLOB, I guess you could get away with a text field. A BLOB is actually text. Run the example in VFP 9 and loook at the table content. Your image is converted to text. If your image sizes are not too big (unfortunately BMP tends to be big because it is not compressed), you might get away with TEXT or VARCHAR I guess.

  
  

• Thicks18

  You can use a BLOB field:

   

  Simple example:

  Create Table images (nID i,bImage blob)
  local liBlob
  
  * select a JPG:
  liBlob = FileToStr(GetPict())
  
  * we can save it in a table (SQL Server supports it)
  Insert into images (nID,bImage) values (1,liBlob)
  
  Browse Last Nocaptions
  
  * say we retrieved the BLOB and we want to save it back to a JPG:
  StrToFile(liBlob, "c:\temp\myImage.jpg")
  

  
  

• Ron Strong

  
  Hi, Thanks for you help, i excuse me, because my english write is no very good that i say, so your code was usefull for my system and other give me this code that either work
  
  cString='Provider=SQLOLEDB.1;Password=MYPASSWORD;Persist Security Info=True;User ID=MYUSER;Initial Catalog=MYBASEDATOS;Data Source=SERVER'
  
  oAdoConexion= CREATEOBJECT('adodb.Connection')
  oAdoConexion.ConnectionTimeout = 30 &&2 minutos
  oAdoConexion.OPEN(cString)
  oAdoConexion.CommandTimeout = 240
  
  *!* for load to sql
  x=CREATEOBJECT("ADODB.RECORDSET")
  X.Open("SELECT id,imagen FROM par_opr_imagenes WHERE id=10", oAdoConexion, 01, 03)
  IF x.recordcount>0
  xarchbin=Createbinary(Filetostr(thisform.image1.Picture))
  x.fields(1).value = xarchbin
  x.update()
  ELSE
  thisform.image1.PictureVal=""
  EndIf
  
  *!* for pull from sql
  x=CREATEOBJECT("ADODB.RECORDSET")
  X.Open("SELECT imagen FROM par_opr_imagenes WHERE id=10", oAdoConexion, 01, 03)
  IF x.recordcount>0
  thisform.image1.PictureVal=x.fields(0).value
  ELSE
  thisform.image1.PictureVal=""
  EndIf
  
  i hope that help someelse
  
  
  
  

• zkarolyi

  hello CetinBasoz

  thanks for the answer. It is really nice and I finally got it.

   

  Once again. thank you

  Vishal

  
  
  

• Plamen Neykov

  Check your other question:

      lcContent = "0X" + STRCONV(FILETOSTR(lcFile),15)
      lnID = thisform.txtnId.value
      lcsql = "Update tblusers set us_gsignature = m.lcContent  where Us_nid = m.lnID" 

  Change your sqlReturnCursor method to accept parameters suitable to call above lcSQL directly. 
  PS: Image datatype on SQL server is right datatype.

  
  

• Seith

  Yes using ADO is simpler. ADO's problem however, you need a version that can use cursoradapters or do all of your work using ADO codes. Not common for VFP programmers (there are utilities to convert to and from ADO/Cursor but full blown ADO is simply too big to be represented by cursors - ie: chapter datatype, multivalued columns).

  
  

• Aravindakshan

  Hello alex,

    Actually I have a "Image" as datatime in the server and it is one of blob type so I tried your solution but it didn't work. It is still giving me error. Please help

  
  
  

• Shubha Iyer

  You must have text type field on SQL server

  Pass to SQL server :

  STRCONV(FILETOSTR('ImageFile'),15)

  When select from SQL server this text field, You will be get memo field.

  Then give property to image control:

  YourForm.ImageControl.PictereVal=STRCONV(sqlresult.MemoField,16)

  Easy&Simple, ther is no nead to have blob or general field to pass and get picture from SQL server

  I was try with .gif image, SQL server 2005, and VFP 9.0

  
  
  

• Hartog

  Thanks Alex,

     Acutally I am inserting into the sQL Server 2005 and it does not have blob field in the database. Any idea how to I do it.

   

  thanks,

  vishal

  
  
  

• SQL User

  The data stored in an image field but it doesn't contain OLE information. When you read it back it reads into a "General" field but in fact it's not. You need to get the bits as is. There are several ways to do that. Here is a complete sample with writing and reading.

  Los datos que estan almacenados en un campo “image” no contienen informacion OLE. Cuando los lees de vuelta ellos se reciben en un campo “general” pero no lo hace bien. Necesitas recibir los bits directamente como vienen. Hay varias maneras de lograr eso. Aqui tienes un ejemplo completo incluyendo leer y escribir.

  PS: Hopefully someone would check and correct the translation that I made from Google. (done)

  Code Snippet

  Local lcImagePath, lcTargetPath, lcDatabaseName, lnHandle,ix
  Local Array aFiles[1]
  lcImagePath = _samples+'data\graphics'
  lcTargetPath = Addbs(m.lcImagePath)+'fromSQLServer'
  Md (m.lcTargetPath)

  * Create sample database and store file contents as is
  lcDatabaseName = "myFileStore"
  lnHandle=Sqlstringconnect('DRIVER=SQL Server;SERVER=.;Trusted_connection=Yes')
  If SQLExec(m.lnHandle, "create database "+m.lcDatabaseName) < 0
  Do errHand
  Return
  Endif
  mySQLExec(m.lnHandle, "use "+m.lcDatabaseName)
  mySQLExec(m.lnHandle, ;
  "create table myFileTable"+;
  " (myID int not null identity, fName varchar(100) not null, fContent image)")
  SQLPrepare(m.lnHandle, ;
  "insert into myFileTable (fName,fContent) values ( m.filename, m.fileContent)")

  For ix=1 To Adir(aFiles, Addbs(m.lcImagePath)+'*.*')
  fileName = aFiles[m.ix,1]
  fileContent = Createbinary(Filetostr(Addbs(m.lcImagePath)+aFiles[m.ix,1]))
  SQLExec(m.lnHandle)
  Endfor
  SQLDisconnect(m.lnHandle)

  * Reconnect and get data we stored
  lnHandle=Sqlstringconnect('DRIVER=SQL Server;SERVER=.;Trusted_connection=Yes')
  mySQLExec(lnHandle, "select myID,fName,fContent from "+m.lcDatabaseName+"..myFileTable","CheckData")
  SQLDisconnect(m.lnHandle)

  * Extract contents and place in lcTargetPath
  * Start by changing field type to M from G
  Local lcTemp, fieldPos
  lcTemp = Forcepath(Sys(2015)+'.dbf',Sys(2023))
  Copy To (m.lcTemp)
  handle = Fopen(m.lcTemp,12)
  fieldPos = 3 && field number of fContent
  Fseek(m.handle,32*m.fieldPos+11,0)
  Fwrite(m.handle,'M')
  Fclose(m.handle)

  Use (m.lcTemp)
  Scan
  Strtofile(fContent, Addbs(m.lcTargetPath)+Trim(fName))
  Endscan
  Use
  Erase (m.lcTemp)

  * Verify that what we have written and read exactly matches
  Local lAllMatch
  lAllMatch = .T.
  For ix=1 To Adir(aFiles, Addbs(m.lcImagePath)+'*.*')
  If !( Filetostr(Addbs(m.lcImagePath)+aFiles[m.ix,1]) == ;
  Filetostr(Addbs(m.lcTargetPath)+aFiles[m.ix,1]) )
  lAllMatch = .F.
  'File mismatch', aFiles[m.ix,1]
  Endif
  Endfor
  If m.lAllMatch
  'Match verified'
  Endif

  Function mySQLExec
  Lparameters tnHandle, tcSQL, tcCursorName
  tcCursorName = Iif(Empty(tcCursorName),'',tcCursorName)
  If SQLExec(tnHandle,tcSQL,tcCursorName) < 0
  Do errHand With tcSQL
  Endif
  Endfunc

  Function errHand
  Lparameters tcSQL
  lcError=tcSQL+Chr(13)
  Aerror(arrCheck)
  For ix=1 To 7
  lcError = lcError+Trans( arrCheck [ix])+ Chr(13)
  Endfor
  Messagebox(lcError,0,'Error def.')
  Endfunc
  

  
  

• TCS BAJA

  SOYGAMA wrote:

  Hola he logrado grabar los datos como lo indicas , el detalle que tengo ahora es como visualizo esa imagen en un objeto image al usar strtofile(), el archivo que me crea dice que esta corrupto o danado, estoy usando windows vista, te agradeceria me ayudaras con eso.

  SOYGAMA,

  Cetin te ha dado una solucion. Espero te sea de ayuda.

  Este es un foro en ingles. Se debe siempre tratar de escribir en ingles para que todo el mundo pueda beneficiarse del intercambio de informacion. Si quieres preguntar sobre Visual FooxPro en castellano, hay varios foros internacionales, entre ellos los NewsGroups (NNTP) de Microsoft de VFP en espanol.

  Translation:

  Cetin has given you an example. Hope it is of help to you.

  This is a forum in English. You should always strive to write in English so everybody else can benefit from the information exchange. If you want to ask about Visual FoxPro in Spanish, there are several international forums, among them the Visual FoxPro (Spanish) - Microsoft NewsGroups (NNTP).

  
  

• R9

  Sorry for bug you still..

  
  I tried using the method that You sent, but I am still having problem. Actually what do you mean by sql ReturnCursor method....

  I am executing using SQLExec command method ..

  please help

  thanks,

  vishal

   

  
  
  

• Svetik

  In your code there were an sqlReturnCursor method that you pass your SQL to. I meant that.

   

  lcContent = "0X" + STRCONV(FILETOSTR(lcFile),15)
  lnID = thisform.txtnId.value
  lcsql = "Update tblusers set us_gsignature = m.lcContent  where Us_nid = m.lnID" 

  lnHandle = SQLStringConnect('Driver=SQL server;'+;
     'server=(local);Trusted_connection=yes')
  SQLExec(m.lnHandle, m.lcSql)
  SQLDisconnect(m.lnHandle)

  
  
• Inserting and Updating images file from VFP9 to SQL Server 2005