SQL Server interpreter sample


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