Hi Armin,

I would really like to see an improved XMLUPDATEGRAM() function in the future, that would provide us with this and other possibilities.

I second that along with native cursor <--> ADO.Recordset conversions.


Thanks for testing! The same as on my machine. I really don't know ... I noticed that the function didn't include memos and posted the quetsion here. Someone told me to set the KEyFieldList. I did that and it worked. Having a look at the generated updategrams some days later, the memo content wasn't there anymore! Probably the evaluation period of XMLUPDATEGRAM() timed out < vbg >.


I found the original thread with your question where Jim Slater pointed it out. I also posted another question there and a sample of my use of ADO.Streams: Re: XMLUpdateGram() and memo fields Thread #688966 Message #704229

Digging deep, I found this on the Internet, and it happens to be the VFP 7 readme! The short of it is that we'd need to use an explicit schema, which I don't want to end up doing. I'm going to see if I can some how parse out the memo fields from the 'before' section. The other option I found out is simply don't use text fields in SQL Server. Use a size 4000 varchar. Strange as it might sound, it's what a VB developer at the site I'm working at said to do. He steers away from text fields for various reasons, one of which I think was performance or something like it's a pain to do search/replace on them or something... didn't sound like it was as easy as VFP :)


- Brian


From VFP 7 Readme:
------------------
"XMLUpdateGram( ) and issues with Memo and General field data By default, when XMLUpdateGram( ) function is run on a table with a Memo or General field, that data is not included in the updategram. By setting the KeyFieldList property of the cursor, you can force the updategram to include that data. However, that alone will not allow the updategram to be successfully written to SQL Server.
The SQL XML UpdateGram specification dictates that "No values can be specified in the or blocks for SQL table columns of text, ntext, image, binary, and varbinary data types. Any attempt to specify values for these columns in the or blocks generates miscellaneous implicit conversion errors from SQL Server. The only way to avoid this is to declare an explicit schema in which these column mappings are marked with the attribute sql:datatype="text|ntext|image|binary"

The default schema implementation is used for updategrams created in Visual FoxPro 7, which does not support text, image, image or binary data. You could create an explicit schema, which would need to be deployed in the SQL XML IIS vroot. You would also have to modify the updategram Visual FoxPro generates to point to that schema. For more information about annotated schema and SQL Updategrams, see the XML for SQL Server 2000 Web Release 1 (WR1) at http://msdn.microsoft.com/msdn-files/027/001/554/Search.asp.

To get the updategram to include memo data, set the KeyFieldList property.

Set Multilocks On
Create Cursor Test (mField M, cField C(10))
Append Blank
CursorSetProp("Buffering",5)
CursorSetProp("keyfieldlist",'cfield') 
mValue = REPLICATE('x ',500)
Replace mField with mValue, cField with "Test"
? XMLUpdategram()
RETURN 

You must use an explicit schema on the SQL Server and reference the schema in the updategram to allow the memo data in the updategram to be written to SQL Server."

VFP6 SP5, VFP8 SP1, VFP 9 SP 1 and Win XP SP 3 (unless otherwise specified)


www.wulfsden.com