08-29-2016 07:07 AM
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 .
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;
*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.
08-29-2016 07:19 AM
Try the folowing optins, the databse might return something more to you...
options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
08-29-2016 07:42 AM
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;
08-30-2016 08:06 AM - edited 08-30-2016 08:07 AM
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.