append dataset to mysql server

[leonzheng]

I am trying to upload data into an existing table in mysql database, using append. Here is my code for data pulling with database information:

libname DB mysql user = userid password = mypassword schema=dbschema server="thisistheserver.com" port = 3311;

proc append base = DB.existingdata data = have force;
run;

It return error as below:

ERROR: Prepare error: No database selected SQL statement: SELECT * FROM `existingdata`

Please help to check what is the problem with my "append" code, or if anyone can show me how to use insert into to do the job, it is also OK.

Thanks!
.

[SuryaKiran]

Hello,

Does the base table exists in the database and you have permissions on it.

[leonzheng]

My userid and password should give me permission. I can upload data in JSL. But right now I need to combine that part of code into my SAS code

[jklaverstijn · Accepted solution]

As this is MySQL I would suggest:

libname DB mysql user = userid password = mypassword DATABASE=dbschema server="thisistheserver.com" port = 3311;

Hope this helps,

-- Jan.

[leonzheng]

Yes, that is what I used

[jklaverstijn]

If you rerun your code with the following options you get some insight in the dialog between SAS and MySQL. This may help with troubleshooting:

options sastrace=',,,d' sastraceloc=saslog;

libname ...

[leonzheng]

Here is the returned info

29
0 1546900232 no_name 0 APPEND
MYSQL_1: Executed: on connection 1 1 1546900232 no_name 0 APPEND
SELECT * FROM `existingdata` WHERE 0=1 2 1546900232 no_name 0 APPEND
3 1546900232 no_name 0 APPEND
4 1546900233 no_name 0 APPEND
MYSQL_2: Executed: on connection 2 5 1546900233 no_name 0 APPEND
SELECT * FROM `existingdata` where (0) 6 1546900233 no_name 0 APPEND
7 1546900233 no_name 0 APPEND
MYSQL: COMMIT performed on connection 2. 8 1546900233 no_name 0 APPEND
9 1546900233 no_name 0 APPEND
30 proc proc append base = DB.existingdata data = have force;
ERROR: Prepare error: No database selected SQL statement: SELECT * FROM `existingdata`.
31 run;

[Reeza]

Can you access or see that table in the library?

[leonzheng]

I cannot directly. But I can pull data from the table in mysql with the same information

[Reeza]

I'm not sure what "i can pull data form the table in mysql" means. Does this mean you can see the table in MySQL, but not in SAS?

[leonzheng]

Sorry I couldn't explain myself clearly. I used to run my SAS code to get result. Then upload new dataset or pull the whole table from MySQL use JSL. Now I have to combine everything into SAS code. I tried pull whole table in a separate program and it worked, but uploading right now is not working. I think the access to the table in MySQL is OK, not sure if that is what you mean "see"

[Reeza]

---

@leonzheng wrote:
Sorry I couldn't explain myself clearly. I used to run my SAS code to get result. Then upload new dataset or pull the whole table from MySQL use JSL. Now I have to combine everything into SAS code. I tried pull whole table in a separate program and it worked, but uploading right now is not working. I think the access to the table in MySQL is OK, not sure if that is what you mean "see"

---

Can you pull information from those tables in SAS? That's one way to verify your assumption that your access is ok. 

[leonzheng]

Yes, I can pull information from the table in SAS code

[Reeza]

Here's how a SQL insert may work. The first data step is just to create a table to insert the data into.

data class;
set sashelp.class;
run;


proc sql;
insert into class
select * from sashelp.class;
quit;

[leonzheng]

Follow your suggestion, I run code:

libname DB mysql user = userid password = mypassword schema=dbschema server="thisistheserver.com" port = 3311;

proc sql;
insert into DB.existingdata
select * from have    //also tried select * from existingdata here
quit;

libname DB clear;

It still not working, show the same error: Prepare error: No database selected SQL statement: SELECT * FROM `existingdata`