Microsoft KB Archive/195525

= PRB: SQLPREPARE Limited to 254 Characters =

Article ID: 195525

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q195525



SYMPTOMS
When you use the SQLPREPARE command to prepare a SQL statement that has more than 254 characters in it, the following error message appears:

Command contains unrecognized phrase/keyword.

Visual FoxPro does not allow the statement to have over 254 characters.



STATUS
This behavior is by design.



MORE INFORMATION
The following three methods explain how to work around this limitation. The first two methods assume that the Visual FoxPro ODBC driver is being connected to and points to the customer table in the Testdata.dbc in the SAMPLES folder.

Method 1
You add up to 254 characters to each variable string, and then add the variables together in a second command to complete the SQL statement as follows: lnHand = SQLCONN && Connect to the Visual FoxPro ODBC driver pointing && to the testdata.dbc.

string1 = "SELECT cust_id, company, contact, title, address, city,  region, postalcode, country, phone, fax, maxordamt FROM customer WHERE   cust_id = 'ABOUT' AND company = 'Around the Horn' AND contact = 'Thomas   Hardy' AND title = 'Sales Representative'"

string2 = " AND address = '120 Hanover Sq.' AND city = 'London'"

? LEN(string1) ? LEN(string2)

string1 = string1 + string2 ? LEN(string1) y = SQLPREPARE(lnHand, string1) ? y  z = SQLEXEC(lnHand) ?z =SQLDISCONN(lnHand)

Method 2
This method builds a dbc to store the view that you use to build the SQL statement. Visual FoxPro allows you to build a SQL statement that is longer than 254 characters when you create a view programmatically. You delete the dbc after the statement has been created because the view is no longer needed. lcdbcname = SYS(2023)+ "\" + SYS(3) CREATE DATABASE (lcdbcname)  && Make new bogus DBC in temp directory.

* Another way to build an easy SELECT statement. CREATE SQL VIEW testview as ; SELECT * FROM customer WHERE .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T. and .T.

* The DBGETPROP statement gets the SQL statement that was just created. lcSqlText = DBGETPROP('testview', 'view', 'sql') CLOSE DATABASE ALL DELETE FILE lcdbcname+'.dbc'       && Delete the DBC now that the && SQL statement is saved to a                                      && variable. DELETE FILE lcdbcname+'.dct'

lnHand = SQLCONN ? LEN(lcSqlText) ? SQLPREPARE(lnHand, lcSQLText, 'ctest') ? SQLEXEC(lnHand) ? SQLDISCONN(lnHand)

Method 3
You can save each SELECT statement to a memo field. When you need to run the SELECT statements, just point the SQLPREPARE statement to the memo field. lnHand = SQLCONN y = SQLPREPARE(lnHand, mytable.mymemo) ? y  z = SQLEXEC(lnhand) ? z  ? SQLDISCONN(lnhand)

Keywords: kbsqlprog kbdatabase kbprb KB195525

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.