ODBC MySQL table update - Optional feature not implemented error

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

ODBC MySQL table update - Optional feature not implemented error

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


Accepted Solutions
Solution
‎10-29-2015 11:56 AM
Occasional Contributor
Posts: 7

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

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

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,229

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

[ Edited ]

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.

Occasional Contributor
Posts: 7

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

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,229

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

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?

Occasional Contributor
Posts: 7

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

Select returns:

 

1.jpg

 

Proc contents:

2.jpg

 

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;
Solution
‎10-29-2015 11:56 AM
Occasional Contributor
Posts: 7

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

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,229

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

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 854 views
  • 1 like
  • 2 in conversation