Wiki Home

Vfp Cursor To Ms Sql


Namespace: WIN_COM_API

TSQL example of creating a cursor from XML.


DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
< ?xml version = "1.0" encoding="Windows-1252" standalone="yes"?>
< VFPData>
	< person firstname="George" lastname="Harrison"/>
	< person firstname="John" lastname="Lennon"/>
	< person firstname="Paul" lastname="McCartney"/>
	< person firstname="Ringo" lastname="Starr"/>
< /VFPData>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/VFPData/person')
            WITH (firstname  varchar(10), lastname varchar(10))
GO


And now, some code that will run in VFP.

Clear

CREATE TABLE Person ( FirstName c(20), LastName c(20) )
INSERT INTO Person VALUES ( "George", "Harrison" )
INSERT INTO Person VALUES ( "John", "Lennon" )
INSERT INTO Person VALUES ( "Paul", "McCartney" )
INSERT INTO Person VALUES ( "Ringo", "Starr" )

* show the local cursor
list

CursorToXML(0, "lcXML",2)

Text to lcSql noshow
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, ?lcXml

SELECT    *
FROM       OPENXML (@idoc, '/VFPData/person')
            WITH (firstname  varchar(10), lastname varchar(10))
            order by firstname desc
EndText

lh = SQLConnect( "pw", "pwuser", "pw" )  && get a connection to MsSql Server 2000
? SQLExec( lh, lcSql )

* Show what comes back from the server
List

Return


VFP code that shows the use of #Table.
* XMLtoServer.prg
Clear
Clear Error

Local ;
	lh, ;
	lcXML, ;
	lcSql
	
Local ;
	array laE(1)

* create some data on the VFP side
CREATE CURSOR Person ( FirstName c(20), LastName c(20) )
INSERT INTO Person VALUES ( "George", "Harrison" )
INSERT INTO Person VALUES ( "John", "Lennon" )
INSERT INTO Person VALUES ( "Paul", "McCartney" )
INSERT INTO Person VALUES ( "Ringo", "Starr" )

* make an xml version of the data (it all gets stored to the lcXML var)
CursorToXML(0, "lcXML",2)
Set Memowidth to 100
? lcXML

* connect to the server
lh = SQLConnect( "pw", "pwuser", "pw" )

* TSQL code to create #person (a server side temporay table)
Text to lcSql noshow
Create Table #person (
	firstname varchar(10),
	lastname varchar(10) )
EndText
? "#1 Create Table #person...", SQLExec( lh, lcSql )
if AError( laE ) > 0
	? laE[2]
EndIf

* put the XML into #person
Text to lcSql noshow
DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, ?lcXml

Insert into #person
	Select *
	FROM OPENXML(@idoc, '/VFPData/person')
	With #person	

EndText

? "#2 Insert into #person from OpenXML(....", SQLExec( lh, lcSql )
if AError( laE ) > 0
	? laE[2]
EndIf

lcSql ="Select firstname+' '+lastname from #Person"

? "#3",lcSql, SQLExec( lh, lcSql )
if AError( laE ) > 0
	? laE[2]
EndIf

List

Return


Notes--

BOL says: Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

This means that if you call sp_executesql() with a CREATE #TABLE command, #table is deleted when sp_executesql() ends. in VFP, if you use ? in your SqlExec(h,lcSql) call, VFP uses sp_executesql() to seperate the lcSql from the value. So if you try to create #table and use ? in the same SqlExec(), the #table is only available to that one SqlExec(h,lcSql) call. So don't try to do SELECT * into #table FROM OPENXML() if you want #table to be avalible to other SqlExec() calls. If this doesn't make any sense now, it will when you trying to figure out why you are getting “Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name '#Person'.” like I did.

There is also ##table: "Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server."

SQL by Ron Darling, VFPatized by Carl Karsten
( Topic last updated: 2006.04.04 12:13:02 PM )