I have an odd problem. It involves Linux & Windows, and it involves
the ruby gem sqlite3. I've already contacted that author/maintainer of
the gem, but I have not heard back in several weeks.
I have a sql file that contains a ctas statement that employs cte with
statements. On Windows, the sql executes fine using the sqlite3
command line tool "sqlite3 aan.db -init cte_with.sql". And it runs
fine calling sqlite3 via the sqlite3 gem:
db = SQLite3::Database.new File.join(FOLDER,'aan.db')
db.execute 'drop table if exists fullfilled;'
sql = File.readlines( File.join(FOLDER,'cte_with.sql') ).collect {|ln| ln.chomp}
db.execute sql.join(' ')
Now with linux, the exact same sql code and ruby files are used. The
sql exec completes successfully using the command line tool. However,
it fails using the sqlite3 gem. The specific exception is "in
'initialize': near "with": syntax error (Sqlite3::SQLException)". It
is failing in the preparation of the statement. From all appearances,
sqlite3 is throwing the exception.
The gem compiles sqlite, creating sqlite_native.so, by downloading the
latest source file, as
"http://sqlite.org#{URL_PATH}/sqlite-autoconf-#{URL_VERSION}.tar.gz";
and compiling that. I have been comparing the gem code between the o/s
environments, and the C code (to this point) is identical.
I have dug into this deeper than my brain can figure out. Is it
possible that there is some kind of #ifdef or #ifndef being triggered,
based on o/s environment, that could effect the parsing or execution
of a cte?
I have a package that contains the sql, the ruby code, and a test db
(1.4k zipped) if anyone would like to give this a shot in their linux
environment and see if you get the same result. Just email me back and
I will send it to you.
* If I change the sql to create temporary tables instead of using the
cte with statements, then everything runs fine regardless of the o/s
environment.
I'm scratching my head on this, one. As always, thanks for your time
and consideration.
dvn
final note: if you take this one, a successful run will create an
empty table. it is good that the table is created. the problem is the
exception, not an empty table.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
