BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Onur_Goekcen
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Onur_Goekcen
Fluorite | Level 6

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Onur_Goekcen
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

Onur_Goekcen
Fluorite | Level 6

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;
Onur_Goekcen
Fluorite | Level 6

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3547 views
  • 2 likes
  • 2 in conversation