Speaking of Ruby & Sqlite...a weekend oddity for someone

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Speaking of Ruby & Sqlite...a weekend oddity for someone

Don V Nielsen
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Speaking of Ruby & Sqlite...a weekend oddity for someone

Roger Binns
On 09/12/16 14:09, Don V Nielsen wrote:
> However,
> it fails using the sqlite3 gem. The specific exception is "in
> 'initialize': near "with": syntax error

That will be a SQLite version issue - older SQLite's didn't support
with.  While you made some effort around versions, whatever is happening
there is an older library is actually used.

You can use this to find out exactly what library version is used:

  SELECT sqlite_version(), sqlite_source_id();

Also this will give a list of compilation options, to verify:

  PRAGMA compile_options;

Roger



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

signature.asc (169 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Speaking of Ruby & Sqlite...a weekend oddity for someone

Don V Nielsen
You are correct, sir. I discovered in the rake tasks the following
little bit of code. It directs which version of sqlite to download and
compile.

# BINARY_VERSION = "3.8.11.1"
# URL_VERSION    = "3081101"
# URL_PATH       = "/2015"

Now I just need to figure out how to reinitiate the rake compile task
after making the following update, then test.

BINARY_VERSION = "3.15.2.0"
URL_VERSION    = "3150200"
URL_PATH       = "/2016"

On Fri, Dec 9, 2016 at 5:27 PM, Roger Binns <[hidden email]> wrote:

> On 09/12/16 14:09, Don V Nielsen wrote:
>> However,
>> it fails using the sqlite3 gem. The specific exception is "in
>> 'initialize': near "with": syntax error
>
> That will be a SQLite version issue - older SQLite's didn't support
> with.  While you made some effort around versions, whatever is happening
> there is an older library is actually used.
>
> You can use this to find out exactly what library version is used:
>
>   SELECT sqlite_version(), sqlite_source_id();
>
> Also this will give a list of compilation options, to verify:
>
>   PRAGMA compile_options;
>
> Roger
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users