Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

CURSORTOXML() issues

Status
Not open for further replies.

Mike Lewis

Programmer
Jan 10, 2003
17,516
Scotland
I am trying to use XML to move data from VFP to SQL Server. I'm nearly there, but I've got a couple of irritating problems. I hope someone can help.

Basically, I have a bunch of native VFP tables (DBFs), and a similar number of back end tables on SQL Server 2000. The DBFs have essentially the same structures as the corresponding back-end tables.

I need to copy selected records from the DBFs to the back end. I want to do it in a generic way, that is, such that my code does not need to know any of the column names.

My idea is to create an XML representation of the relevant records in the DBF; send this as a string to a stored procedure; the stored proc will then use SQL Server's XML features (specifically sp_xml_preparedocument and OPENXML()) to create a record set, which I can then insert into the back end tables.

I'm using the following VFP code to create the XML:

Code:
CURSORTOXML(SELECT(),"SomeVariable",3)

I've more-or-less got this working, except for the following problems:

(i) VFP is outputting all the field names in lower case. SQL Server wants them to be in the same case as the column names on the back end. I don't see any way of achieving this, other than to standardise all the column names in the SQL tables to lower-case. That's not possible, as I have no control over the database schema.

(ii) VFP outputs logical fields as text strings, "false" and "true" respectively. As these are mapped to bit fields, SQL Server expects them to be 0 and 1.

Has anybody any experience in this area that might help? I'm sure I'm not the only person who is using XML to move data from VFP to SQL Server, but it seems to be much more difficult than I expected.

I know I can generate INSERT commands programmatically, but I thought XML would be a neat way to achieve my goal.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
A quick update .... I've now abandoned XML here, at least for now. I decided it would be simpler to generate an INSERT command for each of the records, and to send this directly to the back end.

I'd still be interested in any comments on my original problem. I might return to using XML some time. But I don't want anyone to spend a lot of time on this.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Hi Mike.

I think the only way to handle this is to process the generated XML.

First, SQL Server isn't normally case-sensitive on object names unless you configure it that way, so one option is to change the SQL Server configuration to not be case-sensitive. If that isn't possible, then use SQLCOLUMNS() with "NATIVE" as a parameter to get the column names with their actual case and use STRTRAN() in the XML to convert the names to the desired case.

Second, you could use STRTRAN() to convert "true" and "false" to "1" and "0".

Doug
 
Hi Doug,

Thanks for that.

I hadn't taken in that SQLCOLUMNS() could return column names in their original case. It's useful to know that, especially as I have no control over the server's config settings.

I thought of programmatically changing the "true" and "false" to 1 and 0. However, I ruled out STRTRAN() because the data might include memo fields, which might include the words "true" and "false". I considered actually parsing the XML DOM instead to make the change, but it was at that point I started getting a wee bit discouraged.

As I said in my second post, I can achieve my goals in other ways. I just think it's a pity that it isn't easier with XML, as it's always being touted as a good way of transferring data between different DBMSs.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
you could also generically generate an updatable SPT cursor to your SQLTable and append to it the vfp data you want to insert into SQL-Server.

Bye, Olaf.
 
Hi Olaf,

Thanks for the suggestion. I've used updateable SPT cursors in the past.

One factor in this case is that the IT people at my client's site have made a rule that all client access to the database must be via stored procedures. So, for example, I can't send an INSERT command directly via SPT, but I can call a stored procedure that executes an INSERT command.

I have no idea why they insist on this rule (other than the generic catch-all excuse of "security"). I am allowed to write whatever stored procedures I like, and I can call those stored procedures in any way I like, but I can't communicate with the server in any other way.

So I wrote a stored proc that simply receives an arbitrary SQL command as a parameter, and executes it. That allows me to do whatever I like on the server, while still satisfying the "security" requirement.

I'm only mentioning all this to explain why I didn't use updateable cursors in this case. But it's a good suggestion for other scenarios.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
FYI - Another issue to keep in mind with CURSORTOXML() and XMLTOCURSOR() is that they expect simple structures. They weren't designed to handle nested, child or multiple child relationships.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top