Using ADO & Stored Procedures in C/SIDE

13»

Comments

  • TimSimmonds
    TimSimmonds Member Posts: 47
    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
    ara3n Member Posts: 9,258
    Here is a workaround
    http://mibuso.com/blogs/ara3n/2010/11/2 ... on-in-nav/
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • TimSimmonds
    TimSimmonds Member Posts: 47
    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
    rmv_RU Member Posts: 119
    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
    Looking for part-time work.
    Nav, T-SQL.
  • TimSimmonds
    TimSimmonds Member Posts: 47
    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
    rmv_RU Member Posts: 119
    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);
    Looking for part-time work.
    Nav, T-SQL.
  • TimSimmonds
    TimSimmonds Member Posts: 47
    Many Thanks rmv_RU, that works great \:D/

Categories