Using ADO & Stored Procedures in C/SIDE

TimSimmonds

Hi,

I wondered if anyone had overcome the issue of assigning ADO Command Text and NAV only being able to have text vars with a max 1024 chars?

We require to run a SQL query that is built dynamically so a Stored Procedure can't be used.

I thought we could perhaps make use of an ADO Stream and then assign the Stream to the ADO CommandStream property. However we get the "VT_" type errors so tried to convert the Stream to a Variant first. Still no luck....we get the error saying "The call to member Execute failed. ADODB.Command returned the following message: Object or provider is not capable of performing requested operation."

Any ideas or pointers would be gratefully received.

---

Connection string:
Driver={SQL Server};PROVIDER=SQLOLEDB;Server=myserver;Database=mydb;Uid=Tim;Pwd=nottelling

Extract of code we tried:

Repeat
ADOStream.WriteText(QueryText);
Until "no more QueryText in chunks of 1024 chars"

ADOStream.Position := 0;

// convert ADO Connection to Variant
activeConnection := ADOConnection;

CREATE(ADOCommand);
ADOCommand.ActiveConnection := activeConnection;

// convert ADO Stream to Variant
activeStream := ADOStream;

ADOCommand.CommandStream := activeStream;
ADOCommand.CommandTimeout := 100;
ADORecordSet := ADOCommand.Execute;

---

ara3n

Here is a workaround
http://mibuso.com/blogs/ara3n/2010/11/2 ... on-in-nav/

TimSimmonds

Thanks Rashed but I don't understand your example. That looks to be how to read in text of greater than 1024. My problem is how to issue a sql query that is greater than 1024 chars. I've found a similar post and VB script was suggested. I think I will have to go with that for now...
http://www.mibuso.com/forum/viewtopic.php?f=33&t=11523&start=0&hilit=ado+stream

rmv_RU

TimSimmonds wrote:

My problem is how to issue a sql query that is greater than 1024 chars. I've found a similar post and VB script was suggested

You can use xmlTextNode objects to pass long SQL queries into ado objects.
xmlTextNode.appendData(query1);
xmlTextNode.appendData(query2);
xmlTextNode.appendData(query3);
xmlTextNode.appendData(query4);
xmlTextNode.appendData(query5);
xmlTextNode.appendData(query6);
xmlTextNode.appendData(query7);
adoRs.Open(xmlTextNode.nodeValue, adoConn);

If you want modify data using SQL pay attention to http://www.mibuso.com/forum/viewtopic.php?f=5&t=59746

TimSimmonds

rmv_RU wrote:

You can use xmlTextNode objects to pass long SQL queries into ado objects.

Hi rmv_RU,

That code looks promising but I'm new to XML though. Does the automation var need assigning to something else or something? I can't "CREATE" it on it's own. Nav returns an error.

I've searched for further help but nothing complete come up.

Thanks in advance...

rmv_RU

TimSimmonds wrote:

That code looks promising but I'm new to XML though. Does the automation var need assigning to something else or something? I can't "CREATE" it on it's own. Nav returns an error.

You must create XML document first.

Name	DataType	Subtype	Length
xmlTextNode	Automation	'Microsoft XML, v3.0'.IXMLDOMText	
xmlDoc	Automation	'Microsoft XML, v3.0'.DOMDocument	
CLEAR(xmlDoc);
CREATE(xmlDoc);
xmlTextNode:=xmlDoc.createTextNode('');
xmlTextNode.appendData(query1);
xmlTextNode.appendData(query2);
xmlTextNode.appendData(query3);
xmlTextNode.appendData(query4);
xmlTextNode.appendData(query5);
xmlTextNode.appendData(query6);
xmlTextNode.appendData(query7);
adoRs.Open(xmlTextNode.nodeValue, adoConn);

TimSimmonds

Many Thanks rmv_RU, that works great \:D/