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

[NVK]

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.

[LinusH]

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

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

[Ksharp]

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;

[NVK]

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.