How can I transfer a XML file content to a MS SQL database by stored procedure using LabWindows/CVI SQL Toolkit?

[Cake]

Hi,

I have a problem to transfer a XML file content to a MS SQL database by a given/fixed stored procedure. I'm able to transfer the content of the file by using following method ...

hstmt = DBPrepareSQL (hdbc, EXEC usp_InsertReport '<Report> ..... </Report>');

resCode = DBExecutePreparedSQL (hstmt);

resCode = DBClosePreparedSQL (hstmt);

... but in this case I'm not able to fetch the return value of the stored procedure! 

I have tried to follow the example of the stored procedure in the help documentation (DBPrepareSQL) but I miss a datatype for xml?!?

Any idea how to solve my problem?

KR Cake  

[Cake]

After some additional trials I found a solution by calling the stored procedure in this way

DBSetAttributeDefault (hdbc, ATTR_DB_COMMAND_TYPE, DB_COMMAND_STORED_PROC);

DBPrepareSQL (hdbc, "usp_InsertReport");

DBCreateParamInt (hstmt, "", DB_PARAM_RETURN_VALUE, -1);

DBCreateParamChar (hstmt, "XMLCONTENT", DB_PARAM_INPUT, sz_Buffer, (int) strlen(sz_Buffer) + 1 );

DBExecutePreparedSQL (hstmt);

DBClosePreparedSQL (hstmt);
DBGetParamInt (hstmt, 1, &s32_TestId);

where sz_Buffer is my xml file content and s32_TestID the return value of the stored procdure (usp_InsertReport(@XMLCONTENT XML))

Now I face the problem, that DBCreateParamChar limits the buffer size to 8000 Bytes.

Any idea to by-pass this shortage??

[DianaS]

Hi Cake,

I think you should split up the file content then and transfer it to the database

in multiple steps.

[Cake · Accepted solution]

Hi DianaS,

Thanks for your input. Meanwhile I found another way which fullfill my requirements:

resCode = DBSetAttributeDefault (hdbc, ATTR_DB_COMMAND_TYPE, DB_COMMAND_TEXT);

sprintf(sz_SqlStatement, "DECLARE @TESTID INT EXEC @TESTID = usp_InsertReport '%s' SELECT 'RetVal' = @TESTID", sz_Buffer);
hstmt = DBActivateSQL (hdbc, sz_SqlStatement);

 while ((resCode = DBFetchNext (hstmt)) == DB_SUCCESS)

{

   /* Get values into the record. */

   resCode = DBGetColInt (hstmt, 1, &s32_TestId);
 }

 resCode = DBDeactivateSQL (hstmt); 

sz_Buffer is the XML file content!

KR Cake