Skip Menu |

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 120682
Status: rejected
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: mendoza [...] pvv.ntnu.no
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: 1.54
Fixed in: (no value)



Subject: Not able to sqlite_create_function for DATE_ADD
I want to create a function to mimic MySQL's DATE_ADD, but it seem a function with esoteric parameters with spaces in them does not work as expected: ✔ 01:01 ~ $ perl -wle 'use Data::Dumper; use DBI; my $dbh = DBI->connect("dbi:SQLite:/tmp/test"); $dbh->sqlite_create_function("date_add", -1, sub { return 2; }); warn Dumper($dbh->selectrow_arrayref("select date_add(1);"));' $VAR1 = [ 2 ]; ✔ 01:01 ~ $ perl -wle 'use Data::Dumper; use DBI; my $dbh = DBI->connect("dbi:SQLite:/tmp/test"); $dbh->sqlite_create_function("date_add", -1, sub { return 2; }); warn Dumper($dbh->selectrow_arrayref("select date_add(1, interval 1 hour);"));' DBD::SQLite::db selectrow_arrayref failed: near "1": syntax error at -e line 1. $VAR1 = undef; Is there something I am doing wrong, or should function parsing work differently? is that an sqlite issue, or can the DBI driver do something about it?
On Wed Mar 22 09:05:12 2017, NICOMEN wrote: Show quoted text
> I want to create a function to mimic MySQL's DATE_ADD, but it seem a > function with esoteric parameters with spaces in them does not work as > expected: > > ✔ 01:01 ~ $ perl -wle 'use Data::Dumper; use DBI; my $dbh = DBI-
> >connect("dbi:SQLite:/tmp/test"); $dbh- > >sqlite_create_function("date_add", -1, sub { return 2; }); warn
> Dumper($dbh->selectrow_arrayref("select date_add(1);"));' > $VAR1 = [ > 2 > ]; > > ✔ 01:01 ~ $ perl -wle 'use Data::Dumper; use DBI; my $dbh = DBI-
> >connect("dbi:SQLite:/tmp/test"); $dbh- > >sqlite_create_function("date_add", -1, sub { return 2; }); warn
> Dumper($dbh->selectrow_arrayref("select date_add(1, interval 1 > hour);"));' > DBD::SQLite::db selectrow_arrayref failed: near "1": syntax error at > -e line 1. > $VAR1 = undef; > > Is there something I am doing wrong, or should function parsing work > differently? is that an sqlite issue, or can the DBI driver do > something about it?
I suppose you just need to add quotes before and after "interval 1 hour" and parse it as you like in the callback subroutine you give to sqlite_create_function(), but if it is not what you want, please explain what you actually want to do. The error message (near "1": syntax error) is directly from SQLite, saying it can't parse the SQL statement.
on. 22. mars 2017 04.00.56 skrev ISHIGAKI: Show quoted text
> On Wed Mar 22 09:05:12 2017, NICOMEN wrote:
> > I want to create a function to mimic MySQL's DATE_ADD, but it seem a > > function with esoteric parameters with spaces in them does not work > > as > > expected: > > > > ✔ 01:01 ~ $ perl -wle 'use Data::Dumper; use DBI; my $dbh = DBI-
> > > connect("dbi:SQLite:/tmp/test"); $dbh- > > > sqlite_create_function("date_add", -1, sub { return 2; }); warn
> > Dumper($dbh->selectrow_arrayref("select date_add(1);"));' > > $VAR1 = [ > > 2 > > ]; > > > > ✔ 01:01 ~ $ perl -wle 'use Data::Dumper; use DBI; my $dbh = DBI-
> > > connect("dbi:SQLite:/tmp/test"); $dbh- > > > sqlite_create_function("date_add", -1, sub { return 2; }); warn
> > Dumper($dbh->selectrow_arrayref("select date_add(1, interval 1 > > hour);"));' > > DBD::SQLite::db selectrow_arrayref failed: near "1": syntax error at > > -e line 1. > > $VAR1 = undef; > > > > Is there something I am doing wrong, or should function parsing work > > differently? is that an sqlite issue, or can the DBI driver do > > something about it?
> > I suppose you just need to add quotes before and after "interval 1 > hour" and parse it as you like in the callback subroutine you give to > sqlite_create_function(), but if it is not what you want, please > explain what you actually want to do. The error message (near "1": > syntax error) is directly from SQLite, saying it can't parse the SQL > statement.
The syntax with unquoted params is from MySQL code, so I guess SQlite simply does not allow unquoted space separate params, and there is no way to mimic DATE_ADD?
On Wed Mar 22 17:16:04 2017, NICOMEN wrote: Show quoted text
> on. 22. mars 2017 04.00.56 skrev ISHIGAKI:
> > On Wed Mar 22 09:05:12 2017, NICOMEN wrote:
> > > I want to create a function to mimic MySQL's DATE_ADD, but it seem > > > a > > > function with esoteric parameters with spaces in them does not work > > > as > > > expected: > > > > > > ✔ 01:01 ~ $ perl -wle 'use Data::Dumper; use DBI; my $dbh = DBI-
> > > > connect("dbi:SQLite:/tmp/test"); $dbh- > > > > sqlite_create_function("date_add", -1, sub { return 2; }); warn
> > > Dumper($dbh->selectrow_arrayref("select date_add(1);"));' > > > $VAR1 = [ > > > 2 > > > ]; > > > > > > ✔ 01:01 ~ $ perl -wle 'use Data::Dumper; use DBI; my $dbh = DBI-
> > > > connect("dbi:SQLite:/tmp/test"); $dbh- > > > > sqlite_create_function("date_add", -1, sub { return 2; }); warn
> > > Dumper($dbh->selectrow_arrayref("select date_add(1, interval 1 > > > hour);"));' > > > DBD::SQLite::db selectrow_arrayref failed: near "1": syntax error > > > at > > > -e line 1. > > > $VAR1 = undef; > > > > > > Is there something I am doing wrong, or should function parsing > > > work > > > differently? is that an sqlite issue, or can the DBI driver do > > > something about it?
> > > > I suppose you just need to add quotes before and after "interval 1 > > hour" and parse it as you like in the callback subroutine you give to > > sqlite_create_function(), but if it is not what you want, please > > explain what you actually want to do. The error message (near "1": > > syntax error) is directly from SQLite, saying it can't parse the SQL > > statement.
> > The syntax with unquoted params is from MySQL code, so I guess SQlite > simply does not allow unquoted space separate params, and there is no > way to mimic DATE_ADD?
I'm not sure if "unquoted space separate params" is the right phrase (because INTERVAL is a keyword for MySQL), but, anyway, SQLite usually doesn't allow unquoted space separate params, AFAIK, except CREATE ... USING fts4(<columns>, tokenize=perl '<perl_function>') and such. As I said above, if you add quotes to the interval phrase, you can parse the string in the callback.
Marked as rejected. Thanks.