Command contains unrecognized phrase/keyword in visual fox pro9

Question

I am trying to join a 5 tables, using left outer join from ms sql server server using visual fox pro 9. but I am getting this error "Command contains unrecognized phrase/keyword."

Here is my code.

SQLEXEC(lnConn4,"SELECT [TIARA$Sales Invoice Header].No_,;
        [TIARA$Sales Invoice Header].[Sell-to Customer No_],;
        [TIARA$Sales Invoice Header].[Bill-to Name],;
        [TIARA$Sales Invoice Header].[Bill-to Address],;
        [TIARA$Sales Invoice Header].[Posting Date],;
        [TIARA$Sales Invoice Header].[VAT Registration No_],;
        [TIARA$Sales Invoice Header].[External Document No_],;
        [TIARA$Sales Invoice Header].[Shortcut Dimension 1 Code],;
        [TIARA$Sales Invoice Header].[Bill-to City],;
        [TIARA$Sales Invoice Line].No_,;
        [TIARA$Sales Invoice Line].Description,;
        [TIARA$Sales Invoice Line].[Unit of Measure],;
        [TIARA$Sales Invoice Line].Quantity,;
        [TIARA$Sales Invoice Line].[Unit Price],;
        [TIARA$Sales Invoice Line].[VAT %],;
        [TIARA$Sales Invoice Line].[Line Discount %],;
        [TIARA$Sales Invoice Line].[Amount Including VAT],;
        [TIARA$Sales Invoice Line].[Invoice Price],;
        [TIARA$Sales Invoice Line].Amount,;
        TIARA$Item.[Manufacturer Code],;
        TIARA$Customer.[Territory Code],;
        [TIARA$Sales Shipment Header].Note,;
        [TIARA$Sales Shipment Header].[Order No_],;
        [TIARA$Sales Shipment Header].No_;
    FROM    [TIARA$Sales Invoice Header];
    LEFT OUTER JOIN [TIARA$Sales Invoice Line];
    ON   [TIARA$Sales Invoice Header].No_ = [TIARA$Sales Invoice Line].[Document No_];
    LEFT OUTER JOIN TIARA$Item;
    ON  [TIARA$Sales Invoice Line].No_ = TIARA$Item.No_;
    LEFT OUTER JOIN TIARA$Customer;
    ON  [TIARA$Sales Invoice Line].[Sell-to Customer No_] = TIARA$Customer.No_;
    LEFT OUTER JOIN [TIARA$Sales Shipment Header];
    ON  [TIARA$Sales Invoice Header].[Order No_]     = [TIARA$Sales Shipment Header].[Order No_];
    WHERE   [TIARA$Sales Invoice Header].No_ = THISFORM.TEXT1.TEXT;
    ORDER BY [TIARA$Sales Invoice Header].[Posting Date] DESC;
    INTO TABLE  c:\tiarasys\temp\invoice")

Answer 1

SQLEXEC(lnConn4,"Select [Posting Date],;
        [Bill-to Name],;
        [VAT Registration No_],;
        [Bill-to City],;
        No_,;
        [Order No_],;
        [Bill-to Address],;
        [Shortcut Dimension 1 Code];
         from [TIARA$Sales Invoice Header];
         where No_ = ?inv;
         order by [Posting Date] desc")
         
        
        COPY TO C:\Tiarasys\Temp\invoice_header
        SELECT 1
        USE C:\Tiarasys\Temp\invoice_header EXCLUSIVE ALIAS invoice_header
        orno = Order_no_
        
SQLEXEC(lnConn4, "Select [Unit of Measure],;
        Quantity,;
        No_,;
        [Document No_],;
        [Unit Price],;
        [Sell-to Customer No_],;
        Amount,;
        [Amount Including VAT] from [TIARA$Sales Invoice Line];
        where [Document No_] = ?inv") 
        
        COPY TO C:\Tiarasys\Temp\invoice_line
        SELECT 2
        USE C:\Tiarasys\Temp\invoice_line SHARED ALIAS invoice_line
        sellno = Sell_to_cu
        num = No_
        
SQLEXEC(lnConn4, "Select No_,;
        [Manufacturer Code] from TIARA$Item;
        where No_ = ?num") 
        
        COPY TO C:\Tiarasys\Temp\items
        SELECT 3
        USE C:\Tiarasys\Temp\items EXCLUSIVE ALIAS items
        
SQLEXEC(lnConn4, "Select No_,;
        [Territory Code] from TIARA$Customer;
        where No_ = ?sellno") 
        
        COPY TO C:\Tiarasys\Temp\customer
        SELECT 4
        USE C:\Tiarasys\Temp\customer EXCLUSIVE ALIAS customer
        
SQLEXEC(lnConn4, "Select No_,;
        [Order No_],;
        Note,;
        [Sell-to Customer No_] from [TIARA$Sales Shipment Header];
        where[Order No_] = ?orno")
        
        COPY TO C:\Tiarasys\Temp\shipment_header
        SELECT 5
        USE C:\Tiarasys\Temp\shipment_header EXCLUSIVE ALIAS shipment_header

Answer 2

Here your real problem, causing the error is the literal strings are limited to 255 characters in length. Your SQL command string literal is over 255.

• Either divide that into pieces where each literal is 255 characters at most
• Or write it within TEXT ... ENDTEXT block as Stefan suggested.

To expand on it:

clear
x = "12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890"
    
? m.x, LEN(m.x)

Would fail with the error you get. However, if you divide it into smaller pieces then you can concatenate to build up a string variable (up to 16 Mb according to documentation):

x = "12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890"+;
"12345678901234567890123456789012345678901234567890;
12345678901234567890123456789012345678901234567890"
    
? m.x, LEN(m.x)

Of course especially for SQL commands it is preferable to use TEXT...ENDTEXT. It has a big advantage of that you can copy paste the text content into SSMS and test your code there.

Then, the hidden error is INTO TABLE ... as Tamar pointed out. You can't create an on disk table like that using SQLExec(). Instead you would use the 3rd parameter to get the result into a cursor and then you can write to a table as you did from that cursor. ie:

SQLExec(m.myHandle, "select * from myTable", "crsResult")

Select * from crsResult into table ('c:\MyFolder\myTable')

Tamar also mentioned about referring text objects on form, for them you would use parameters in your code. ie:

myValue = thisfrom.txtSomething.Value
SQLExec(m.handle, "select * from myTable where myField = ?m.myValue", "crsResult")

And then to last probable error, your code sounds to be querying Excel tables? If so then that might be the wrong SQL for Excel (MSAccess engine doesn't really understand good ANSI SQL). If that happens to be the case, try using a pair of parentheses around each of your joins.

Answer 3

Stefan is right that putting the whole query right into SQLExec() isn't a good idea.

That said, one problem here is the INTO TABLE clause at the end. That's not going to work in SQL Server and it isn't helpful. You want SQLExec() to give you back a cursor, which is what it does by default. If you really want to create a table from that cursor, you can with COPY TO, but usually you just work with the cursor.

Another problem is the reference to a textbox inside the quotes where you're building the query. That's going to just put that exact string in your query, not the textbox value.

Anyway, take Stefan's advice and then you can look at the query you've built and if the problem hasn't gone away, you'll be able to see what might be causing trouble.

Answer 4

If you want to create a multi-line string in Vfp, you can for example use Text/Endtext, e.g.

LOCAL lcSql
TEXT TO lcSql 
    SELECT col1,
        col2,
        col4
    From ...
    Left Join....
    Where ...;
EndText
SQLEXEC(lnConn4,m.lcSql,...)

But if you want to send that string via ODBC, you would need to talk the Server slang, e.g. T-SQL for MS Sql Server, i.e. remove the FoxPro line-continuation ";" characters, and the Into Table part, everything the server would not understand.

And if the server does not understand, the SqlExec() function would return -1, and you can use Vfp's AError() function to get the ODBC error message.