Hi All! I have to pass very long sql-queries to MS SQL Server using ado The length of the queries is more than 1024 limitation of Navision text datatype. Now I solve this problem by using arrays of text, But the problem is that I don’t know exact length of my strings, since I form strings dinamically. So my solution with arrays doesn’t seem wise to me. Does anyone can give me advice how to create and manage strings with the length more than 1024 char
Hi, You can use temporary table to solve this problem.
Hi Rajeshj Could you give little more hints. What did you mean? Now when I pass my strings to ado recordset, I have to do things like this: adoRS.Open(SQLText + SQLText + … + SQLText[N], adoConn); How temp table can help me. I would appreciate some code example.
Hi, What I understand is that your problem is, you don’t know how big the string and what the length of Array you have to define. By using temporary table you can do the same thing what you are doing with array. Temporary table is nothing but same as normal table only difference is it’s stores data temporarly in memory not in physical table. So you can break the SQL string as per your need and can store in different rows (here no predefined array index kind of limitation).
hi, as i understand your issue tis some kind of attempt to initialise c/al variable by value created by external application. As to me it should be able to do following: 1) declare the variable of same object of yours which evaluates the variable (“My object”); 2) add the global variable into your object (e.g. “Value Accepted” - boolean) 3) in the “on run” trigger complete evalution using combination “evaluation” method and error(’’). “error(’’)” messages nothing and enables to continue program execution; 4) evaluate “My object”.“Value Accepted” if everything OK. I hadn’t opportunity to do this kind of operation but if tis possible it should be great. BR, Anthony
i must add that tis all about “.runmodal” method.
…and error should break any current execution, so code “Value Accepted”:=false; if evaluate(“Test C/AL String”,format(“Probed Extrenal String”)) then “Value Accepted”:=true; must be preferable… Thank you
Have you tried using the Command automation object? Maybe you can use its CommandText property to “build up” your query string outside of Navision. Just a thought…
Hi Rajeshj Yes, I know about temp tables, since I often use them myself, but I still can’t understand how to apply them in my case. That’s why I requested for code example. To Nelson There is one problem with the ADODB.Command object. It has some problems with ActiveConnection property. It doesn’t want to accept ADODB.Connection object. And I don’t know whose the problem is: my or not. That’s why I can’t say is your advice could help me or not To kopyurff My problem is how to pass over 1024 char limitation of Text datatype.
Humm, yes, I think I do remember having some problems with the Command object. I just don’t remember if it was with the ActiveConnection property. Eventually, I didn’t need it so I just used RecordSet.Open (my queries were all under 1000 chars). And it was only a suggestion with remote chances of success, because if you need to do someting like: adoCommand.CommandText := adoCommand.CommandText + NewQueryPart; I think you will still get the overflow error as C/AL is trying to handle the strings anyway… not sure… If I am able to try it out, I will post my conclusions.