topic Re: insert from sas into amazon rds mysql not working even though log shows insert in SAS Programming

insert from sas into amazon rds mysql not working even though log shows insert

NVK — Mon, 29 Aug 2016 11:07:51 GMT

I am trying to insert records from SAS table into Amazon RDS using ODBC libname statement. My log shows that records have been inserted , however the select query returns null. I tried commit after insert, but it is still not working.

When i try inserting from sql prompt it works.

I am trying a normal proc sql statement .

Code :-

libname test1 odbc user=XX password="XX" datasrc="Amazon RDS ODBC DSN"    autocommit=yes dbcommit =1000;
proc sql;
insert into test1.tmp_list (cust_iden, process_id) values (1,10);
select * from test1.tmp_list;
quit;

Log

*NOTE: Libref TEST1 was successfully assigned as follows:  
      Engine:        ODBC  
      Physical Name: Amazon RDS ODBC DSN  
3          proc sql;  
4          insert into test1.tmp_list (cust_iden, process_id) values (1,10);  
NOTE: 1 row was inserted into TEST1.tmp_list.  
5          select * from test1.tmp_list;  
NOTE: No rows were selected.  
6  
7          quit;  
NOTE: PROCEDURE SQL used (Total process time):  
      real time           0.09 seconds  
      cpu time            0.05 seconds*

There is no error displayed in the log which indicates a rollback. Where is my record disappearing? I want to be able load a list of cust_iden's from a sas dataset into this temp tables.

I am getting the same result even when the select query is in a different proc. I also logged into the database server, but none of the records have been added to the table

Any pointers would be helpful. Thanks.

Re: insert from sas into amazon rds mysql not working even though log shows insert

LinusH — Mon, 29 Aug 2016 11:19:51 GMT

Try the folowing optins, the databse might return something more to you...

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

Re: insert from sas into amazon rds mysql not working even though log shows insert

Ksharp — Mon, 29 Aug 2016 11:42:04 GMT

Maybe you need reconnect to libname .



libname test1 odbc user=XX password="XX" datasrc="Amazon RDS ODBC DSN"    autocommit=yes dbcommit =1000;
proc sql;
insert into test1.tmp_list (cust_iden, process_id) values (1,10);
quit;
libname test1 clear;

libname test1 ......
proc sql;
select * from test1.tmp_list;
quit;

Re: insert from sas into amazon rds mysql not working even though log shows insert

NVK — Tue, 30 Aug 2016 12:07:56 GMT

The libname clear command is not working.

I am checking with sastrace option to see what is happening at database end. Will put in the findings soon.