Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Is there a limit to the length of a SQLXL select? (Read 7828 times)
Steve Taylor
YaBB Newbies
*




Posts: 4
Is there a limit to the length of a SQLXL select?
11.10.05 at 10:46:27
 
I have a very long select statement that works fine in TOAD but returns "invalid column name" when I run it via SQLXL. The SQLXL message window shows only part of the select statement, which suggests that it may be truncating the statement.
 
I'm executing it via
 
v_sql = <select ...... lots of stuff..... >
SQLXL.Sql.SetText p_sql
etc
 
I've tried assigning p_sql to a worksheet cell and it contains the whole statement (this is how I was able to cut and paste the statement into TOAD to ensure that it was indeed a valid select statement).
 
Is there a limit to the length of a SQLXL select statement?
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Is there a limit to the length of a SQLXL sele
Reply #1 - 11.10.05 at 10:58:38
 
No, there is no limit. Well, everything has its limit but it should be soo large that you never hit it. The limit will be the maximum  length of a string in VB. I don't know what it is but it is very large.
 
When you run the SQL through VBA - you seem to do this - there is a limit in the VBA environment that you encounter. VBA does not like very long code lines. So if you have a 2000 character long SQL statement and when you code it like this it will be a problem:
SQLXL.SQL.setText "select .......2000 characters long....."
 
You need to break up the lines to make them shorter than the maximum length. I think that should be around 250 characters. In VBA the lines are coloured red when they are too long. A good technique is to use a variable. Note that the current macro recorder in SQL*XL already takes care of this. Consider the folloing example:
 
Dim strSQL as String
 
strSQL = "select * from "
strSQL = strSQL & " emp where empno = "
strSQL = strSQL & " 12345;"
 
SQLXL.SQL.setText strSQL
 
This technique can be used to add pieces of 200 characters or so to the SQL statements
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Tony Nilsson
YaBB Newbies
*




Posts: 9
Re: Is there a limit to the length of a SQLXL sele
Reply #2 - 11.10.05 at 14:01:46
 
Hi,
 
The best way to get records with a large statment is to create a view in the database and read from that insted.
 
You can also do this if you use VBA.
 
Record with a smal statment that works.
Insert a Dim statment e.g
Dim Text1, Text2, Text3 as string
 
In the code before the 'SQLXL.Sql.SetText' split the code like:
 
Text1 = "Select * "
Text2 = "From emp "
Text3 = "Where ID > 10 "
 
Then put it together like this:
SQLXL.Sql.SetText Text1 + Text2 + Text3
 
 
Best Regards
Tony
 
 
 
 
Quote from Steve Taylor on 11.10.05 at 10:46:27:
I have a very long select statement that works fine in TOAD but returns "invalid column name" when I run it via SQLXL. The SQLXL message window shows only part of the select statement, which suggests that it may be truncating the statement.

I'm executing it via

v_sql = <select ...... lots of stuff..... >
SQLXL.Sql.SetText p_sql
etc

I've tried assigning p_sql to a worksheet cell and it contains the whole statement (this is how I was able to cut and paste the statement into TOAD to ensure that it was indeed a valid select statement).

Is there a limit to the length of a SQLXL select statement?

Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Is there a limit to the length of a SQLXL sele
Reply #3 - 13.10.05 at 16:59:02
 
Another customer posted a problem today that touched on this problem too. I have decided to change the program code to avoid this problem alltogether. In the new version I have changed the way you can set the SQL text to the SQL object. The current syntax is still available too - to allow backwards compatibility. The macro recorder will look at the SQL text and decide whether it is short enough to code a normal setText or whether it should use the new syntax.  
 
The new syntax involves three operations the setText is doing internally also: A clear operation to remove any previous statements,  a series of appendText statements to add chunks of SQL to the SQL text and a final Parse statement to let the SQL object parse the SQL string into individual commands.
 
Note that this is now all dealt with automatically for you. Your old code with the old workarounds still work too.
 
Also note that I read that the maximum string length in VB is approximately 65,400 characters. This will be the maximum length of the SQL SQL*XL will support.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Tony Nilsson
YaBB Newbies
*




Posts: 9
Re: Is there a limit to the length of a SQLXL sele
Reply #4 - 11.11.05 at 14:53:45
 
In what version is this new thing available ?
 
 Smiley
 
Best Regards Tony
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Is there a limit to the length of a SQLXL sele
Reply #5 - 11.11.05 at 23:36:02
 
This should be implemented in SQL*XL 4.0.26.  
 
Look here for the release log:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130772637
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1