SQLITE over ODBC -- works but get weird error messages

[wws]

Hi everyone,

I would like to dial in import and export to SQLITE from SAS, so I am doing some experiments. 

Here is my libname:

libname sqlite odbc complete="dsn=SQLite3 Datasource; Driver={SQLite3 ODBC Driver}; Database=C:\Users\me\file.sqlite;  OEMCP=1; NoTXN=1; NoWCHAR=1;ShortNames=1;"; 

Now I want to do the following

data sqlite.shoes;
	set sashelp.shoes;
run;

But I get the following in the log:

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
ERROR: Error attempting to CREATE a DBMS table. NOTE: No data found/modified..
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set SQLITE.shoes may be incomplete.  When this step was stopped there were 0
         observations and 7 variables.
ERROR: ROLLBACK issued due to errors for data set SQLITE.shoes.DATA.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds

However, I can find the new table in the explorer, though it has empty columns that are correctly named and typed.  Now I can run the following and it works perfectly:

proc sql;
	insert into sqlite.shoes select * from sashelp.shoes;
quit;

So... my question is really "what gives?!"

I have found some viable workarounds, but I would like the data steps to work seamlessly without any errors or weird log messages.  Does anybody have any ideas?  Modifications to the libname connection string?

Tx!

[SASKiwi]

If you are just trying to insert or append rows try this:

proc datasets library = SQLLITE;
  append base = shoes data = sashelp.shoes;
run;
quit;

If the table doesn't exist it should be created.

[wws]

No, I am trying to get the "data sqlite.shoes; ..." statement to work as normal.  I have other work arounds where I create and drop the table using pass through SQL, then insert with proc sql.  Doing workarounds is what I am trying to avoid.

(I tried to quote but it didn't work...)

Thanks!

[Patrick]

The data statement...

data sqlite.shoes;

...will try to create a table. If this table already exists then the DB will throw an error. I assume that's what happens in your case.

Use below options to get more information in the SAS log what actually gets sent to the DB for execution.

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

[wws]

---

@Patrick wrote:

If this table already exists then the DB will throw an error. I assume that's what happens in your case.

Well, you assumed wrong...  I confirmed that the table does NOT exist before running the code "data sqlite.shoes;  ..."  I looked via HeidiDB as well as a refresh of the explorer window.

Use below options to get more information in the SAS log what actually gets sent to the DB for execution.

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

---

I will try that and report back.

[Edit]  For reference, when I run the datastep and the table exists, I get the following error in the log, compare to the original one I posted:

ERROR: The ODBC table shoes has been opened for OUTPUT. This table already exists, or there is
       a name conflict with an existing object. This table will not be replaced. This engine
       does not support the REPLACE option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

[Edit #2]

Here is the SQL output if I do the data step above (knowing that sqlite.shoes does NOT exist):

18   data sqlite.shoes;
19       set sashelp.shoes;
20   run;


ODBC_4: Prepared: on connection 1
SELECT * FROM shoes WHERE 0=1

ODBC: AUTOCOMMIT is NO for connection 2
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

ODBC_5: Executed: on connection 2
CREATE TABLE shoes (Region varchar(25),Product varchar(14),Subsidiary varchar(12),Stores
double,Sales double,Inventory double,Returns double)

ODBC: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
ODBC: COMMIT performed on connection 2.
ERROR: Error attempting to CREATE a DBMS table. NOTE: No data found/modified..
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set SQLITE.shoes may be incomplete.  When this step was stopped there were 0
         observations and 7 variables.
ODBC: ROLLBACK performed on connection 2.
ODBC: COMMIT performed on connection 2.
ERROR: ROLLBACK issued due to errors for data set SQLITE.shoes.DATA.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

I think it's very interesting but somewhat surprising.  Also, I know by looking and inserting later that the table was definitely created on the SQLITE db.

Thanks!

[Patrick]

The logs you share are pretty clear: You try to create a table that already exists. Not sure what makes you think it doesn't.

May-be list the libref in the log and verify that you're loading into/looking at the same database schema.

libname sqlite list;

When working with SAS tables you are used to the data step just creating and re-creating tables. When interfacing with databases that's a bit different. You normally create a database table only once.

Reasons for not always dropping/re-creating database tables are

- keep statistics and optimizations a database maintains in the background.

- database specific table definitions like column types that SAS won't create "automatically" for you

If you want to stick using a SAS data step and re-create the table every single time then you need to ensure it doesn't exist.

Code like below should do the job.

/* delete target table if it exists */
proc datasets lib=sqlite nolist nowarn;
  delete shoes;
  run;
quit;

/* create target table and load data */
data sqlite.shoes;
  set sashelp.shoes;
run;

For a more "serious" ETL/ELT full refresh load one would use code more along the line of below.

/* truncate target table if it exists */
%if %sysfunc(exist(sqlite.shoes)) %then
  %do;
    proc sql;
      delete from sqlite.shoes;
    quit;
  %end;

/* load data into target table (also creates the table if it doesn't exist) */
proc append base=sqlite.shoes data=sashelp.shoes;
run;quit;

And last but not least: If running with the SAS options shared earlier you want to see a commit message in the SAS log after the load (after execution of the Proc Append statement).

ODBC: COMMIT performed on connection <n>.

[wws]

---

@Patrick wrote:

The logs you share are pretty clear: You try to create a table that already exists. Not sure what makes you think it doesn't.

Um, you are wrong : I delete the table, verify it doesn't exist via 2 methods (a refresh of the explorer window and Heidi SQL), then I run only the data step I mentioned in the original post.  You get a different error (posted) when you try to run the data step and the table already exists in the database.  Really.  I definitely appreciate your in depth response, but you should trust me when I say there is not table.

[Quentin]

I would suggest sending this in to tech support, with a log showing both your libname statement and your attempt to create sqlite.shoes.  This looks bug-ish to me. From what you I can see from your logs, the DATA step is able to create the table, but it's not able to to insert records into it.  That's weird.  Just for fun, you might try a simpler test case, e.g.:

data sqlite.foo;
  x=1;
run;

You might also show them in the log that PROC APPEND to create a new SQL table works (if it does), but the DATA step is not working.

[wws]

---

@Quentin wrote:

I would suggest sending this in to tech support, with a log showing both your libname statement and your attempt to create sqlite.shoes.  This looks bug-ish to me. From what you I can see from your logs, the DATA step is able to create the table, but it's not able to to insert records into it.  That's weird.  Just for fun, you might try a simpler test case, e.g.:

data sqlite.foo;
  x=1;
run;

You might also show them in the log that PROC APPEND to create a new SQL table works (if it does), but the DATA step is not working.

---

Is there a standard form for tech support? I have never done that before.

Just FYI, here is the simpler test case:

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;
%let APPLF = C:\Users\webb.sprague@cfc.wa.gov\applications.sqlite;
libname sqlite odbc complete="dsn=SQLite3 Datasource; Driver={SQLite3 ODBC Driver}; Database=&APPLF.;  OEMCP=1;"; 
proc datasets lib=sqlite nolist;
	delete blah;
run;quit;
data sqlite.blah;
	x=1;
run;

Here is the log output.  Note the weird "SELECT * FROM blah WHERE 0=1" ... :

36   %let APPLF = C:\Users\webb.sprague@cfc.wa.gov\applications.sqlite;
ODBC: COMMIT performed on connection 0.
ODBC: AUTOCOMMIT is NO for connection 0
37   libname sqlite odbc complete="dsn=SQLite3 Datasource; Driver={SQLite3 ODBC Driver};
37 ! Database=&APPLF.;  OEMCP=1;";
NOTE: Libref SQLITE was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: SQLite3 Datasource
ODBC: AUTOCOMMIT is NO for connection 1
ODBC: AUTOCOMMIT turned ON for connection id 1
ODBC: Called SQLTables with schema of NULL
38   proc datasets lib=sqlite nolist;
39       delete blah;
40   run;

NOTE: Deleting SQLITE.blah (memtype=DATA).

ODBC_1: Prepared: on connection 1
SELECT * FROM blah WHERE 0=1


ODBC_2: Executed: on connection 1
DROP  TABLE blah

ODBC: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
NOTE: No data found/modified.
40 !     quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds


41   data sqlite.blah;
42       x=1;
43   run;


ODBC_3: Prepared: on connection 1
SELECT * FROM blah WHERE 0=1

ODBC: AUTOCOMMIT is NO for connection 2

ODBC_4: Executed: on connection 2
CREATE TABLE blah (x double)

ODBC: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
ODBC: COMMIT performed on connection 2.
ERROR: Error attempting to CREATE a DBMS table. NOTE: No data found/modified..
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set SQLITE.blah may be incomplete.  When this step was stopped there were 0
         observations and 1 variables.
ODBC: ROLLBACK performed on connection 2.
ODBC: COMMIT performed on connection 2.
ERROR: ROLLBACK issued due to errors for data set SQLITE.blah.DATA.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds


ODBC: Called SQLTables with schema of NULL

[ErikLund_Jensen]

Hi @wws 

You write "I have found some viable workarounds, but I would like the data steps to work seamlessly without any errors or weird log messages".

In the beginning there was the Data Step, and the Data Step was there to read data into a SAS data set as input to analysis and reporting procedures. Most data was stored in flat files, Relational data bases didn't even exist, and neither did XML, Json or Web Services. But over the years these and other technologies emerged, and SAS was expanded with a comprehensive set of tools for coping with them.

Yoy can do almost anything with SAS, and do it in a lot of different ways too, and thanks to the available libname engines, the Data Step can read most data sources and with certain limitations also write them. But it is intended to write SAS Data Steps, and the SQL procedure is a better interface to RDBMS systems.

So I wonder why you insist on using the Data Step and see other tools as workarounds, even if they are better suited to the job at hand. You would probably not use an Allen key to tighten a Phillips screw.