SQL string length limitation?

[jculp]

I am still in evaluation mode, but lots of experience scripting with other tools (mostly VBA). Currently trying to move working SQL code into MS for querying Oracle tables. I had success with a relatively short test SQL, but the code I am trying to automate is over 1,500 characters -- multiple tables, columns, where-statements, etc., and I can't find a reason for why it isn't working.

Is there an absolute limit of 255 chars for any SQL? I have tried breaking up the SQL into <255 chunks, assigning each to a variable, then ConCat the variables into one variable. No success. DBConnect is fine. Variable assignment is fine. When I MessageModal my SQL and paste the result string into Notepad, it's fine - spaces where they should be, quotes and parentheses where they should be. I can even paste that back into my SQL tool (TOAD), and it works. Just not in the script.

Any help appreciated. If I can't find a work-around, then MS may not be the tool for me.

[Marcus Tettmar]

No, there is no limit to the length of the SQL. Macro Scheduler's DBQuery is simply passing everything through to the ADO/ODBC driver which is responsible for the actual communication with the database. Perhaps the connection string isn't quite right? Could you send your code to [email protected] or post it here and let us take a look?

Be interesting to know if the same connection string and SQL works using the VBScript method instead:

http://www.mjtnet.com/blog/2006/02/20/a ... databases/
http://www.mjtnet.com/vbsdb.htm

[jculp]

OK, thanks. With that information, I went back and reviewed my DSN settings, and discovered that the query was probably timing out. I removed the timeout option in my ODBC manager; not my preferred solution, but at least it allows the query to run, and it returns the correct number of rows and fields. I may be able to find a way to set that from my connection string.

Next question: once I retrieve the recordset, is there a way to put the entire array into an Excel spreadsheet without having to cycle through all the individual array members? With Excel VBA, I would do this with the CopyFromRecordset method of the Range object. Sometimes the recordset will have thousands of rows, and it takes several minutes to cycle through using the rset_x_y approach. I have been working on a way to implement this with VBScript, but so far haven't figured it out. Is this even possible?

I'm pleased with most of what I've seen in MSched so far. It talks to the ERP app my company uses (which does not implement Windows copy/paste functionality!), and my users are all quite excited about the possibilities I have shown them already.

[JRL]

No time for a good explanation but here is a sample that I have that does more or less what you want to do. I don't believe there is a way to NOT cycle through all of the array elements.

Notice the range locations in the vbscript, you may need to change those and also notice the file name and make that work for you. This VBScript creates a new file so you also may need to alter that. Obviously the database portion of Macro Scheduler will not work for you.

Hope this helps,
Dick

Code: Select all

Let>file1=%temp_dir%CreditHold.xls

VBSTART
Sub OpenExcelFile(filename)
   Set xlApp = CreateObject("Excel.Application")
   xlApp.visible = True
   Set xlBook = xlApp.Workbooks.open(filename)
   xlBook.ActiveSheet.Range("A:A").Select
   xlApp.Selection.NumberFormat = "@"
'xlApp.Selection.Replace What="RemoveThis", Replacement="", LookAt=xlPart, SearchOrder=xlByColumns, MatchCase=False, SearchFormat=False, ReplaceFormat=False
   xlBook.ActiveSheet.Range("A1").Select
   xlBook.ActiveSheet.Paste
   xlBook.ActiveSheet.Cells.Select
   xlBook.ActiveSheet.Cells.EntireColumn.AutoFit
   xlBook.ActiveSheet.Range("A1").Select
end sub
VBEND
VBEval>timer,starttime
 Message>Gathering Data Please Wait...
Let>SQL=SELECT  "V_CUSTOMER_MASTER"."CUSTOMER", "V_CUSTOMER_MASTER"."NAME_CUSTOMER", "V_CUSTOMER_MASTER"."FLAG_CREDIT_HOLD"  FROM "V_CUSTOMER_MASTER" WHERE "V_CUSTOMER_MASTER"."FLAG_CREDIT_HOLD" Like 'Y' ORDER BY "V_CUSTOMER_MASTER"."CUSTOMER"
Let>connstr=Driver={Pervasive ODBC Client Interface};ServerName=Server;dbq=@ODBC;Uid=username;Pwd=password;
DBConnect>connstr,dbH

DBQuery>dbH,SQL,Job,numrecs,numfields

DBClose>dbH

IfFileExists>%file1%
  DeleteFile>%file1%
EndIf

Let>data=CUSTOMER NUMBER%TAB%CUSTOMER NAME%TAB%ON CREDIT HOLD%CRLF%

Let>comma=,
Let>kk=0
Repeat>kk
  Add>kk,1
  Let>value1=Job_%kk%_1
  Let>value2=Job_%kk%_2
  Let>value3=Job_%kk%_3
  ConCat>data,%value1%%TAB%%value2%%TAB%%value3%%CRLF%

Until>kk,%numrecs%

WriteLn>%file1%,wres,
PutClipBoard>%data%

 VBRun>OpenExcelFile,%file1%

VBEval>timer,endtime
Let>TotalTime=%endtime%-%starttime%
mdl>TotalTime

[jculp]

Thanks for the suggestion. I will try this; if I read the code correctly, you are moving the array matrix values into a single variable, copying that variable to the clipboard, then pasting it into Excel -- and that is probably faster than writing each individual cell in Excel... still not as fast as copying the entire recordset, though, I would imagine.

[Marcus Tettmar]

I think Jculp has already hinted at the way to do it without cycling through the array. Do it all in VBScript. If you read the data in with VBScript you can also run any Excel VBA routine from VBScript. So you should be able to call CopyFromRecordset.

Use VBScript to read in the data as explained here:

http://www.mjtnet.com/blog/2006/02/20/a ... databases/
http://www.mjtnet.com/vbsdb.htm

And then hook into Excel and call CopyFromRecordset

http://www.mjtnet.com/blog/2008/04/28/c ... -vbscript/