using ADO and SQL queries longer than 1024 char.

jjanauskasjjanauskas Member Posts: 49
edited 2015-06-26 in SQL General
Hello,

Has anyone got an idea how to execute sql queries longer than 1024 characters using ADO in Navision.

The query I want to executed is generated dynamically during run-time, so stored procedures do not help here.

What I did: I output the generated sql to text file and then used special my written automation server (SQLFileExecuterX) which executes text files as sql queries...

But I thought, maybe somehow it is possible to do that without using SQLFileExecuterX, but using such features like BIGTEXT or Stream, or at least some standard software which goes together with navision/ado/windows...?

huh?

Answers

  • jjanauskasjjanauskas Member Posts: 49
    I found another possible solution to the problem. You can use BSTRConverter automation server from NATHash.dll which comes together with Navision installation.

    BSTRConverter is used to handle long text and has a method which returns BSTR type result. You can pass that result to ADO Recordset...

    ADORecset.Open(BSTRConverter.BSTR, ....
    
  • krikikriki Member, Moderator Posts: 9,039
    [Topic moved from Navision forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • ara3nara3n Member Posts: 9,249
    None of the above solution work

    xp_execresultset is removed from sql 2005

    and ADORecordset.Open(NavHash.BSTR); errors with
    The length of the text string exceeds the size of the string buffer.

    Any other solution?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,249
    Still no answer to a way to execute a sql statement that is longer than 1024?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,249
    I think I've found a solution. I will post an update.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,249
    Not really a solution. I had to change the sql statement to be shorter.
    DECLARE @D Date Set @D = '10/07/10'
    (Select [Item No_],[Location Code],[Quantity], QtyToAdd =
     Case   when  ILE.[Positive] = 1 then
      (Select isnull(sum(IAE.[Quantity]),0)
      from [KRONUS$Item Application Entry] as IAE ,[KRONUS$Item Ledger Entry] as ILE2
      where IAE.[Posting Date] >= @D and ILE2.[Posting Date] <= @D and
      IAE.[Outbound Item Entry No_] <> 0 and IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
      and ILE.[Entry No_] = IAE.[Inbound Item Entry No_])
     else (Select isnull(sum(IAE.[Quantity]),0)
      from [KRONUS$Item Application Entry] as IAE,[KRONUS$Item Ledger Entry] as ILE2
      where IAE.[Posting Date] >= @D and ILE2.[Posting Date] <= @D and
      IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
      and ILE.[Entry No_] = IAE.[Outbound Item Entry No_])
     end,
    (Select isnull(sum(VEntry.[Cost Amount (Expected)]+ VEntry.[Cost Amount (Actual)]),0) 
    from [KRONUS$Value Entry] as VEntry where VEntry.[Posting Date] <= @D and
    VEntry.[Item Ledger Entry No_] = ILE.[Entry No_]) as [Inventory Value] 
    
    from [KRONUS$Item Ledger Entry] AS ILE
    where ILE.[Posting Date] <= @D)
    

    Changed to
    DECLARE @D Date Set @D = '10/07/10'
    (Select [Item No_],[Location Code],[Quantity], QtyToAdd =
      (Select isnull(sum(IAE.[Quantity]),0)
      from [KRONUS$Item Application Entry] as IAE ,[KRONUS$Item Ledger Entry] as ILE2
      where IAE.[Posting Date] >= @D and ILE2.[Posting Date] <= @D and
      IAE.[Outbound Item Entry No_] <> 0 and IAE.[Item Ledger Entry No_] = ILE2.[Entry No_]
      and  ((ILE.[Positive] = 1 and ILE.[Entry No_] = IAE.[Inbound Item Entry No_]) OR 
          (ILE.[Positive] = 0 and ILE.[Entry No_] = IAE.[Outbound Item Entry No_]))),
    (Select isnull(sum(VEntry.[Cost Amount (Expected)]+ VEntry.[Cost Amount (Actual)]),0) 
    from [KRONUS$Value Entry] as VEntry where VEntry.[Posting Date] <= @D and
    VEntry.[Item Ledger Entry No_] = ILE.[Entry No_]) as [Inventory Value] 
    from [KRONUS$Item Ledger Entry] AS ILE
    where ILE.[Posting Date] <= @D)
    
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • rdebathrdebath Member Posts: 383
    It's not that difficult to solve this problem, just use the VBScript OCX or DLL.
    I'm using this technique to automatically generate some rather large trigger code, about 128K in 3000 lines for example.
      VBScript Automation  'Microsoft Script Control 1.0'.ScriptControl
    or ..
      VBScript OCX         ScriptControl Object
    
    VBScript.Language('VBScript');
    
    Session.SETRANGE("My Session", TRUE);
    Session.FIND('-');
    
    VBScript.AddCode('set SQL = CreateObject("ADODB.Connection")');
    VBScript.AddCode('SQL.open("Provider=sqloledb;Data Source=(local);Initial Catalog=' +
                      Session."Database Name" + ';Integrated Security=SSPI")');
    
    VBScript.AddCode('StrData = ""');
    VBScript.AddCode('Sub AddLine(str) : StrData = StrData & str & vbCRLF : End Sub');
    VBScript.AddCode('Sub RunSQL : SQL.Execute(strData) : StrData = "" : End Sub');
    
    VBScript.ExecuteStatement('AddLine("' +
              'drop table zxyzzy' +
              '")');
    
    VBScript.ExecuteStatement('RunSQL');
    

    You probably want to use BSTRConverter to build the string to avoid the horrible quoting issues.
    VBBS     Automation  'Navision Attain Hash 1.0'.BSTRConverter
    VBScript.AddObject('BS', VBBS, FALSE);
    
    VBBS.BSTR := LineData;
    VBBS.AppendNextStringPortion(LineData2);
    VBScript.ExecuteStatement('StrData = StrData & BS.BSTR & vbCRLF');
    

    BTW: for the record, VBScript does seem to be the answer to any little problems you may have with Navision ... :)
  • rmv_RUrmv_RU Member Posts: 119
    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);
    Looking for part-time work.
    Nav, T-SQL.
Sign In or Register to comment.