topic Re: ODBC MySQL table update - Optional feature not implemented error in SAS Programming

ODBC MySQL table update - Optional feature not implemented error

Onur_Goekcen — Thu, 29 Oct 2015 13:09:16 GMT

Dear all,

I'm trying to update a table in a MySQL database via ODBC. The connection is set up correctly and select queries work well.

When I try to update some fields, I'm receiving the Error: Error updating table entry: [MySQL][ODBC 5.3(a) Driver][mysqld-5.6.19]Optional feature not implemented.

Below is the statement I use:

libname mydblib odbc datasrc=QDPM_TEST;

proc sql;

update mydblib.kpi_dq_scores set score= 116, status = 'Check completed' where deliveryID='DLV_7_117_Y2015M10D29T0850';

quit;

I've added to odbc.ini the option EnableNcharSupport=1, did not help.

Would appreciate any help.

Br,

Onur

Re: ODBC MySQL table update - Optional feature not implemented error

RW9 — Thu, 29 Oct 2015 13:32:21 GMT

It sounds like there is an error with your SQL statement. Have you tried putting double quotes around text:

update mydblib.kpi_dq_scores

set score= 116,

status = "Check completed"

where deliveryID="DLV_7_117_Y2015M10D29T0850";

Some checklist:

1 - check running the SQL directly in the database.

2 - If that works, check write permissions been given for the access

3 - what does select * from mydblib.kpi_dq_scores return, are those variables present, and the right format?

4 - If both above are ok, then it is something in the connection part.

Re: ODBC MySQL table update - Optional feature not implemented error

Onur_Goekcen — Thu, 29 Oct 2015 13:54:43 GMT

Hi,

thx for quick response.

1. Double quotes are not working either. The statement works properly on MySQL

2. The user used for ODBC configuration has admin rights on MySQL

3. The table content is returned correctly

4. I've tried in EG: can access and open all tables via the mapped ODBC library, but when trying to update any value I receive the same error

My guess was that the issue is either related to ODBC config (I couldn't see any meaningful option in the detailed options list when creating the ODBC connection) or data type mismatch?

Thank you,

Onur

Re: ODBC MySQL table update - Optional feature not implemented error

RW9 — Thu, 29 Oct 2015 14:49:24 GMT

What does the table returned from select * from ... look like, i.e. a proc contents. Your not trying to put character in number or vice versa?

Re: ODBC MySQL table update - Optional feature not implemented error

Onur_Goekcen — Thu, 29 Oct 2015 15:12:21 GMT

Select returns:

Proc contents:

I'm not converting any data type. A create table with data step, insert and drop table with PROC SQL are just working fine, so the user has sufficient rights for DML operations. The only statement that fails is e.g. a simple update on a cell (tested also without a where condition, also fails)

proc sql;
update  mydblib.kpi_dq_scores set status = "Check completed" where deliveryID="DLV_7_117_Y2015M10D29T0850";
quit;

Re: ODBC MySQL table update - Optional feature not implemented error

Onur_Goekcen — Thu, 29 Oct 2015 15:55:53 GMT

After several attempts, I've decided to use the pass-through facility, it works now:

PROC SQL; 
  CONNECT TO ODBC (DSN="QDPM_T");    
  EXEC (update  kpi_dq_scores set status = "Check completed2" where deliveryID="DLV_7_117_Y2015M10D29T0850";) BY ODBC;            
QUIT;

Thank you for your support,

Br,

Onur

Re: ODBC MySQL table update - Optional feature not implemented error

RW9 — Fri, 30 Oct 2015 10:10:55 GMT

Yes, its an odd one. You can put a ticket into the support desk about it. Personally I have only ever used pass through, and in general I would do any database work on the database itself, I don't like the idea of driving one app with another. Glad you got it fixed though.