Fill Command

[SBTBILL]

In VB there is a command called fill which moves a record set into a grid or other container. Anybody know of something similar in VFP.

 

[cbasoz]

Here is a sample code that shows one of the problems:

Clear
* Test Data yarat - Create test data
lnHandle = Sqlstringconnect('Driver={SQL Native Client};Server=.\SQLExpress;Trusted_Connection=yes')
CursorSetProp("MapBinary",.T.,0)
TEXT TO lcCreate noshow
IF NOT EXISTS ( SELECT  name
                FROM    sys.databases
                WHERE   name = N'ImageDb' )
    Create DATABASE [ImageDb]
ENDTEXT
SQLExec(m.lnHandle,m.lcCreate)
SQLExec(m.lnHandle,'Use ImageDb')
SQLExec(m.lnHandle,'Create Table ImageTable1'+;
  ' (pkId uniqueidentifier rowguidcol default NewID(),'+;
  ' fName varchar(20),lName varchar(20),'+;
  ' personPicture image)')
SQLExec(m.lnHandle,'Create Table ImageTable2'+;
  ' (pkId uniqueidentifier rowguidcol default NewID(),'+;
  ' fName varchar(20),lName varchar(20),'+;
  ' personPicture varbinary(max))')


lcImagePath = _samples + 'data\graphics'
For ix=1 To Adir(aFiles, Addbs(m.lcImagePath)+'*.*')
  Image = Createbinary(Filetostr(Addbs(m.lcImagePath)+aFiles[m.ix,1]))
  fname = Proper(Left(aFiles[m.ix,1],4))
  lName = Proper(Substr(aFiles[m.ix,1],5,4))

  SQLExec(m.lnHandle, ;
    "insert into ImageTable1 (fName,lName,personPicture)"+;
    " values (?m.fname,?m.lname,?m.image)")
  SQLExec(m.lnHandle, ;
    "insert into ImageTable2 (fName,lName,personPicture)"+;
    " values (?m.fname,?m.lname,?m.image)")
Endfor
SQLDisconnect(m.lnHandle)
* Test Data yarat - Create test data

* Form ile goster - Show on a form
Public oForm1, oForm2
oForm1 = Createobject('myFormWithODBC')
oForm1.Show

oForm2 = Createobject('myFormWithADO')
oForm2.Left=200
oForm2.Top=200
oForm2.Show

Define Class myFormWithODBC As Form
  DataSession = 2
  Height = 400
  Width = 600
  Caption='ODBC - Bug'

  Add Object myGrid1 As Grid With Height = 200, Top =   0,Width = 400, RecordSource = 'Sample1'
  Add Object myGrid2 As Grid With Height = 200, Top = 200,Width = 400, RecordSource = 'Sample2'
  Add Object myImage1 As Image With Left = 410,Top =   0,Height=200,Width=190,Stretch=1
  Add Object myImage2 As Image With Left = 410,Top = 200,Height=200,Width=190,Stretch=1

  Procedure Load
    lnHandle = Sqlstringconnect('Driver={SQL Native Client};Server=.\SQLExpress;Trusted_Connection=yes')
    CursorSetProp("MapBinary",.T.,0)
    SQLExec(m.lnHandle,'select * from ImageDb..ImageTable1','Sample1')
    SQLExec(m.lnHandle,'select * from ImageDb..ImageTable2','Sample2')
    SQLDisconnect(m.lnHandle)
  Endproc

  Procedure myGrid1.AfterRowColChange
    Lparameters nColIndex
    If This.RowColChange%2 = 1
      Thisform.myImage1.PictureVal = Sample1.personPicture
    Endif
  Endproc

  Procedure myGrid2.AfterRowColChange
    Lparameters nColIndex
    If This.RowColChange%2 = 1
      Thisform.myImage2.PictureVal = Sample2.personPicture
    Endif
  Endproc

  Procedure Init
    Thisform.myImage1.PictureVal = Sample1.personPicture
    Thisform.myImage2.PictureVal = Sample2.personPicture
  Endproc
Enddefine

Define Class myFormWithADO As Form
  DataSession = 2
  Height = 400
  Width = 600
  oCa1 = null
  oCa2 = null
  Caption="CursorAdapter with ADO - Correct"

  Add Object myGrid1 As Grid With Height = 200, Top =   0,Width = 400, RecordSource = 'Sample1'
  Add Object myGrid2 As Grid With Height = 200, Top = 200,Width = 400, RecordSource = 'Sample2'
  Add Object myImage1 As Image With Left = 410,Top =   0,Height=200,Width=190,Stretch=1
  Add Object myImage2 As Image With Left = 410,Top = 200,Height=200,Width=190,Stretch=1

  Procedure Load
    Local cn As 'AdoDb.Connection'
    cn = Createobject('AdoDb.Connection')
    cn.ConnectionString = "Provider=SQLNCLI10;server=.\SQLExpress;Trusted_Connection=yes"
    cn.Open()
    rs1 = cn.Execute("select * from ImageDb..ImageTable1")
    rs2 = cn.Execute("select * from ImageDb..ImageTable2")

    this.oCa1 = Createobject('Cursoradapter')
  	this.oCa2 = Createobject('Cursoradapter')
    This.oCa1.DataSourceType = "ADO"
    This.oCa2.DataSourceType = "ADO"
    This.oCa1.Alias = 'Sample1'
    This.oCa2.Alias = 'Sample2'
    This.oCa1.CursorFill(.F.,.F.,0,rs1)
    This.oCa2.CursorFill(.F.,.F.,0,rs2)
    cn.Close()
  Endproc

  Procedure myGrid1.AfterRowColChange
    Lparameters nColIndex
    If This.RowColChange%2 = 1
      Thisform.myImage1.PictureVal = Sample1.personPicture
    Endif
  Endproc

  Procedure myGrid2.AfterRowColChange
    Lparameters nColIndex
    If This.RowColChange%2 = 1
      Thisform.myImage2.PictureVal = Sample2.personPicture
    Endif
  Endproc

  Procedure Init
    Thisform.myImage1.PictureVal = Sample1.personPicture
    Thisform.myImage2.PictureVal = Sample2.personPicture
  Endproc
Enddefine

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Cetin,

Thanks for that. I was aware of the problems with the new data types (or, at least, some of them), but have more-or-less managed to work round those that affect me (it's perhaps more of an issue in 2008 and than 2005, by the way).

Personally, I would rather deal with the problems as they arise, rather than change the fundamental architecture of my applications. Rightly or wrongly, I have used SPT and ODBC for years, and I would be reluctant to throw out my generic code (and my knowledge) unless I really had to.

But not everyone is in my situation. No doubt others will agree with your advice.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Cetin,

you may be right that those drivers work ok with other languages, but there are different ODBC driver versions and one of them we use works ok with Varchar(MAX) fields. So it's also not purely Foxpro, which can't cope with this type.

I don't use VarBinary(max) fields, so that doesn't hurt me.

Nevertheless I got an SQL2005 VarBinary(Max) field successfully into a foxpro Blob field (type W) and back via Cursoradapter set to ODBC using the SQL Native Client Driver:

DEFINE CLASS cadImageTable2 AS cursoradapter

 SelectCmd = "Select pkId, personPicture from ImageTable2"
 CursorSchema = "pkId C(36), personPicture W"
 Alias = "v_imagetable2"
 DataSourceType = "ODBC"
 Flags = 0
 CompareMemo = .F.
 FetchMemo = .T.
 WhereType = 1
 KeyFieldList = "pkId"
 Tables = "ImageTable2"
 UpdatableFieldList = "personPicture"
 UpdateNameList = "pkId ImageTable2.GUID, personPicture ImageTable2.personPicture"
 MapBinary = .T.
 UseCursorSchema = .T.
 Name = "cadImageTable2"

 PROCEDURE Init()
    This.DataSource = SqlStringConnect([Driver=SQL Native Client;Trusted_Connection=Yes;Server=...;Database=...;])
 ENDPROC

ENDDEFINE

oCa = CreateObject("cadImageTable2")
oCa.CursorFill()

Append Blank in v_imagetable2
Select v_imagetable2
Append Memo personPicture From (GetFile("JPG"))

TableUpdate(.T.,.T.,"v_imagetable2")
oCa = .null.

oCa = CreateObject("cadImageTable2")
oCa.CursorFill()
Copy Memo personPicture To C:\Public\restore.jpg

Bye, Olaf.

 

[Olaf Doschke]

Beware of the broken line in Init().

Bye, Olaf.

 

[Olaf Doschke]

And to round up the driver issue:

We use DRIVER={SQL Server} in a connecting to SQL Server 2005, which more precise is SQLSRV32.DLL Version 2000.85.1132.

Bye, Olaf.