Updating MySQL tables through NAV using ADO

jelittlejohn

Hi and thanks in advance for your help,

I have used ADO for years to pull data from MySQL tables into NAV using ADO automation objects. Using posts found here as a guide I never had any real trouble. However, now, I am tasked with updating these MySQL tables with some shipping info that is to be entered in NAV. I though I had it but I am getting a syntaxerror back from the MySQL odbc driver. When I take my SQL UPDATE statement and run it directly against the MySQL tables it works without problem. The code is as follows:

ConnStr := 'Driver={MySQL ODBC 5.1 Driver}; SERVER=www.testserver.com; PORT=3306; ' +
  'Database=testdb; User=testuser; Password=testpwd; Option=3;';

sil.SETRANGE(Type,sil.Type::Item);
sil.SETRANGE("Document No.",Rec."No.");
IF sil.FIND('-') THEN BEGIN
 REPEAT
  IF (STRLEN(sil."Tracking No.") > 0) AND (STRLEN(sil."Carrier Code") > 0) THEN BEGIN
   CLEAR(ADOConn) ;
   CREATE(ADOConn) ;
   CLEAR(ADOCommand) ;
   CREATE(ADOCommand) ;
   ADOConn.ConnectionString := ConnStr;
   ADOConn.ConnectionTimeout := 0;
   ADOConn.CommandTimeout := 0;
   ADOConn.Open();
   Connvar := ADOConn;
   ADOCommand.ActiveConnection := Connvar;
   ADOCommand.CommandType := 4;

   ConnQ := 'UPDATE s01_SS_STATUS_OrderItems SET status = ''SHIPPED'' WHERE Order_id = ' + Rec."Order No.";
   //ConnQ := 'UPDATE s01_SS_STATUS_OrderItems SET status = ''SHIPPED'',tracknum = ''' + sil."Tracking No." + '''';
   //ConnQ := ConnQ + ',tracktype = '''+ sil."Carrier Code" + ''' Where ';
   //ConnQ := ConnQ + '(Order_id = ' + Rec."Order No." + ') AND (code = ''' + sil."No." + ''')';
   ADOCommand.CommandText := ConnQ;
   ADOCommand.Execute;
  END ELSE BEGIN
   shipped := FALSE;
  END;
 UNTIL sil.NEXT = 0;
END;

Name DataType Subtype Length
sil Record Sales Invoice Line
ConnStr Text 250
ConnQ Text 250
ADOConn Automation 'Microsoft ActiveX Data Objects 6.0 Library'.Connection
ADOCommand Automation 'Microsoft ActiveX Data Objects 6.0 Library'.Command
Connvar Variant

I commented out the longer update statement in an attempt to get to the bottom of the issue with a simpler version. Depending on the valus of ConnQ, I always get some variation of the following error:

This message is for C/AL programmers:

The call to member Execute failed. Microsoft OLE DB Provider for ODBC Drivers returned the following message: [MySQL][ODBC 5.1 Driver][mysqld-4.1.22]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call UPDATE s01_SS_STATUS_OrderItems SET status = 'SHIPPED' WHERE Order_id = 176' at line 1

The sql statement is always slightly truncated in the error message. Also, in the table in question status is a varchar field and Order_id is an integer field.

I feel like I must be making some small but obvious mistake. Thanks again for any help.

Thanks,
Jack

MarkHamblin

It's likely the "call" command in the SQL. And (I'm just guessing here) that's being added by calling "Execute" on the ADO connection. The ODBC driver is likely restricting "Execute" to executing stored procs, hence the "Call" syntax it adds.

Try using OpenRecordset on ADOConn instead and see if it helps.

- Mark

SigGunn

I would try changing the CommandType

//ADOCommand.CommandType := 4;  // adCmdStoredProc
ADOCommand.CommandType := 1; // adCmdText

jelittlejohn

Changing the Commandtype value from 4 to 1 solved the problem. I suspected it was an issue like this but wasn't sure where to find the possible values. Thanks so much for your help.

Jack