Partager via

SQL Server interpreter sample

  • Article

I was writing some code to test Microsoft SQL Server: SQL Server 2005 Express Edition (which I believe you can download for free)

It connects to SQL Express (It’s simple change to use SQL Server 2005), creates a database called Test, creates a table called Cust, adds some records, removes the Test database, so all is cleaned up.

It creates a text string of lines that are executed sequentially. If the line starts with a “!”, then it’s executed by VFP locally.

Note that it also uses the new XML data type.

SQLDISCONNECT(0) && close All connections

cstr="driver=sql server;server=(local)\sqlexpress"

*cstr="driver=sql server;server=calvinh6"

nh=SQLSTRINGCONNECT(cstr)

?"Handle =",nh

IF nh<0

          ?AERROR(aa)

          LIST MEMORY LIKE aa

          RETURN

ENDIF

IF SqlDoIt(nh,"use test")<0 && If we can't use the test database (alt: use sp_helpdb)

          SqlDoIt(nh,"Create database test")

          SqlDoIt(nh,"use test")

ENDIF

SET TEXTMERGE ON TO memvar myvar NOSHOW && put the following text into a variable called myvar

          SQLTABLES(nh) && get a table of tables

          SELECT * FROM sqlresult WHERE table_type="TABLE" AND table_name = "cust" INTO CURSOR foo

          IF _tally>0

                   \DROP TABLE cust

          ENDIF

          \CREATE TABLE cust (name char(10),amount tinyint,tstamp datetime,myxml xml)

          FOR i = 1 TO 10

                   \INSERT INTO cust (name,amount,tstamp,myxml) VALUES ('test<<TRANSFORM(i)>>',

                   \\<<TRANSFORM(i)>>,'<<DATETIME()-i*86400>>',

                   \\'<MyTag MyAttr="val<<i>>">Mydata</MyTag>')

          ENDFOR

          \select * from cust

          \!list off name,amount,tstamp,myxml

*!* \sp_tables

* \!brow last

          *use master database, so test is not used anymore

          \use master

          \drop database test

          \sp_helpdb

          \!list PADR(name,20),db_size

SET TEXTMERGE to

n=ALINES(aa,myvar) && put the lines into an array

FOR i = 1 TO n && for each line

          ?i,aa[i]

          IF SqlDoIt(nh,aa[i])<0 && Execute it

                   ?AERROR(aErrs)

                   LIST MEMORY LIKE aErrs

                   EXIT

          ENDIF

ENDFOR

SQLDISCONNECT(0) && close All connections

RETURN

PROCEDURE SqlDoIt(nH as Integer, cCmd as String)

          nRetval=0

          IF LEFT(cCmd,1)='!' && use "!" for Fox commands

                   cCmd=SUBSTR(cCmd,2)

                   &cCmd && execute the command

          ELSE

                   nRetval= SQLEXEC(nH,cCmd)

          ENDIF

          RETURN nRetval

Comments