Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"SQL: Statement Too Long" Error

Status
Not open for further replies.
Sware

Sware

Programmer
Apr 19, 2005
124
US
Our VB6 app uses the Visual FoxPro Driver (6.00.8167.00) to work with a .DBF database. A recordset is created via rs1.Open "SELECT * FROM......" We then fill an Array with the 87 recordset fields and do record processing in the Array, which results in some (not all) changes of the original Array values. At the end of record processing the following code is executed:

For i = 0 To 86
If rs1.Fields(i) <> PlyrArray(i) Then
rs1.Fields(i) = PlyrArray(i)
End If
Next i
rs1.Update

We get the "SQL: Statement Too Long" error on the rs1.Update statement. What could be causing this error? Thanks.
 
Sort by date Sort by votes
Hi SWare,

In Visual FoxPro, a single statement is limited to 8,192 characters. I don't know if that's the same with the driver that you are using, but I would think it is.

(By the way, is this an ODBC driver or an OLE DB provider? It probably doesn't affect your problem, but in general the OLE DB provider is the better choice. Using ODBC could be a problem if you are accessing a recent version of a FoxPro database.)

Can you check the length of the statement that your update command is actually sending? My guess is that if it contains 87 fields, and if the values being inserted are large, then you could be breaking the 8,192-character limit. But there's no way for me to confirm that.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Upvote 0 Downvote
Mike,

Thanks for your input. Each record is 75 characters so there are 6,525 characters involved (75 x 87). That would not seem to violate the limit of 8,192. How do I check the length of the statement actually being sent by the Update command?

I believe it's the ODBC Driver. The connection string includes: "Provider=MSDASQL;Driver=(Microsoft Visual FoxPro Driver)....." We had previously tried the OLE DB Driver (Provider-vfpoledb) but got Multi-Step errors.

 
Upvote 0 Downvote
It's not just the number of characters multiplied by the number of fields. You also have to allow for all the separators and other characters that make up the Update statement.

That said, you're probably right that it won't exceed the 8,192-limit.

Don't worry too much about the drive issue. The fact that you can even access the database suggests that that is not the problem.

You asked how to check the length of the Update statement. Given that the statement in question is being generated within VB, that's something you would have to ask a VB expert.

My approach would be to try updating a much smaller number of fields. Gradually increase that number until the error occurs. Doing that would at least tell you whether there was anything wrong with your overall logic, or whether it is indeed some maximum that's being exceeded.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Upvote 0 Downvote
Thanks again Mike. We'll continue to "fiddle." If anyone else has suggestions they will be appreciated.
 
Upvote 0 Downvote
As Mike said 8192 is the limitation for the whole statement not just values it includes:
Code: 
UPDATE MyTable SET Fld1 = Val1, Fld2 = Val2, ......., Fld87 = Val87 WHERE ......
You could reach the limit if your field names are long and you have long where clause.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Thanks Borislav. I guess I'm confused between the use of rs1.Update and UPDATE MyTable SET Fld1 =....... Does not the former result in the internal generation of a statement like the latter? If so, how can I see what is generated?
 
Upvote 0 Downvote
Sware,

I guess I'm confused between the use of rs1.Update and UPDATE MyTable SET Fld1 =....... Does not the former result in the internal generation of a statement like the latter?
Click to expand...

Yes, that' probably correct.

UPDATE MyTable SET ... is standard SQL syntax. It is likely that your rs1.Update is generating this standard SQL to send to the driver.

rs1.Update is nothing to do with VFP. It is something within VB. That's why I suggested you ask a VB person. In fact, your best bet would be to find out how to view the internal statement that this rs1.Update (whatever it is) is generating. Perhaps someone in a VB-related forum can tell you how to do that.

Once you have seen the generated UPDATE statement, let us know, and we can take it from there.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk
 
Upvote 0 Downvote
IF you use ODBC driver to connect to VFP you could TRACE what is the command you sent. Control Panel->Administrative Tools->Data Sources (ODBC). Go to Tracing page and press "Start Tracing now" button (you could change the default LOG file first). After you run rs.Update, Stop Tracing and check the Log file to see what is the command generated from the record set. This is only valid for ODBC connections , didn't test it with OLEDB.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Another way:
I ran into this many many moons ago, with a VB Point Of Sale system using VFP databases. the solution was “Let Fox Talk to Fox…” no ODBC etc.
Create a COM (Public) class in VFP. Put your array, table update etc in it.
In VB do a createobject(), call the method that does the processing etc. That’s it…
 
Upvote 0 Downvote
Agree, the only annoyance is that you must have VFP run-times installed.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Being a VFP and database neophyte I don't understand how to implement the solution offered by Imaginecorp. However, I think I've found the root cause of the problem and it's NOT the length of the Update statement generated by the VB rs1.Update statement. I did the trace recommended by Borislav. The generated Update statement is less than 1800 characters so it doesn't violate the 8192 limit.

I've discovered that the length of a FEW of the 87 array data elements used to change recordset fields does not match the length of the corresponding recordset field (in all such cases the data length is less than the field length). I think I read in one of the several forums I've visited that the unequal lengths situation can cause the "SQL: Statement Too Long" error (although, as is often the case, the title of the error and related documentation certainly don't reveal that possibility).

So, unless I'm missing something else, I think the solution is for me to find out how I screwed up in a few cases the length of the data stored in the recordset fields before issuing the rs1.Update statement. Additional comments on this possible solution will be appreciated.
 
Upvote 0 Downvote
Sware, what Database you use for BackEnd?
VFP6, VFP8 or VFP9?
Keep in mind that the LAST ODBC driver for VFP was written for VFP 6. if you use any new field types (introduced in later versions) you may have troubles.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Borislav:
I am sorry but I disagree, you do Not need VFP Runtime or any VFP related files installed on the machine to run a COM (DLL) object. Maybe, my use of the word COM rather than a DLL was misleading.

Sware:
In a VB front end, unless VB has improved, it is better to do all your processing in a VFP created object and have it update a VFP database, rather than using OLE DB or ODBC. Its cleaner and more efficient. Creating a COM (DLL) object is really very simple. Its a class declared OlePublic built as a DLL then registered. But you do have to have some knowledge of VFP:

VFP Help: Visual FoxPro and Advanced COM
 
Upvote 0 Downvote
Blind Alley! I have modified my processing code and verified that the lengths of the array data elements placed in the recordset for all 87 fields are the same as the lenghts of the recordset fields. But, I still get the "SQL: Statement Too Long" error when the rs1.Update statement is executed.

Any other ideas? Thanks.
 
Upvote 0 Downvote
Imaginecorp,
When you create a COM class in VFP no matter how you compile it - DLL or EXE you need VFP runtimes to be installed to get that COM to work.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
I respectively disagree.
Yes you may need runtime etc, if building the .DLL on the fly within a VFP program like the code below. But once created it can be copied to another machine that has no clue about Fox, But it has to be manually registered (Regsvr32). In VB a simple CreateObject() will work.

Sware:
if you are willing to try this and have a machine that does not have fox installed. Create the dll on a fox machine first, then copy the .dll AND the .tlb file to the non fox machine.
I do not know what the "rs" object is, but put your VFP Select and Processing code in the procedure processarray.

Register the .dll with run command. Then in VB access the dill and the procedure.

ox = Createobject("vbserver.for_vb")
? ox.processarray()

Code: 
****Cut and paste into a program
**** CALL IT FOR_VB
**** Then run it

If Program() != "FOR_VB"
	?"this file MUST BE NAMED 'for_vb.prg'"
	Return
Endif
If File("for_vb.dll")
	Declare Integer DllUnregisterServer In for_vb.Dll
	DllUnregisterServer()
	Clear Dlls
Endif

Build Project vbserver From for_vb
Build Dll vbserver From vbserver recomp

*uncomment to test this object
*!*	ox = Createobject("vbserver.for_vb")    && create the server object
*!*	?ox.processarray()    &&& get the result

Define Class for_vb As Session OlePublic
	Procedure processarray()
		****Put your processing code here
		**** and remove the following, but return something so you will know it worked
            
		Select 0
		Use Home(1)+'samples\data\customer'
		Select * From customer Where country = "France" Into Array carray
		Return Alen(carray,1)

	Function Error(nError, cMethod, nLine)
		Comreturnerror(cMethod+'  err#='+Str(nError,5)+;
			'  line='+Str(nLine,6)+' '+Message(),_vfp.ServerName)
Enddefine
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

kyletreyfield
  • Locked
  • Question
VFP9 won't start because of &quot;insufficient memory&quot;
Replies
15
Views
560
Piotr Czekalski
Piotr Czekalski
T
  • Locked
  • Question
long vs short filenames
2
Replies
20
Views
1K
Joe Crescenzi
Joe Crescenzi
Filip Brnic
  • Locked
  • Question
Help with setting up the report (error message band 1 is too large to fit 1
Replies
8
Views
997
Chris Miller
Chris Miller
GTGeek88
  • Question
SQL query syntax
Replies
7
Views
206
GTGeek88
GTGeek88
Brian_Calnan
  • Locked
  • Question
VFP &quot;Contains&quot; syntax 2
Replies
4
Views
415
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top