Re: [racket-users] db module + SQLite driver + 'ON CONFLICT' = syntax error

[Ryan Culpepper]

On 4/18/19 23:53, David Storrs wrote:



On Thu, Apr 18, 2019 at 5:42 PM Ryan Culpepper > wrote:


Use "INSERT OR IGNORE" instead. See
https://sqlite.org/lang_conflict.html, 3rd paragraph.


Yep.  Unfortunately, this was a simplified case of wanting to use OR DO 
UPDATE to make this actually an upsert.  When I started seeing this 
issue I backed it out to OR DO NOTHING in order to simplify the syntax.


Ah, okay. Try putting a recent version of the libsqlite3 shared library 
in one of the paths returned by


  (begin (require setup/dirs) (get-lib-search-dirs))

Ryan



On 4/18/19 23:28, David Storrs wrote:
 >
 >
 > On Thu, Apr 18, 2019 at 4:48 PM Jon Zeppieri mailto:zeppi...@gmail.com>
 > >> wrote:
 >
 >     It might well be the SQLlite version. This is a pretty new
feature.
 >     It's possible that the db library is using an older version than
 >     your CLI client.
 >
 >
 > Got it.  Is there any way for me to address the situation aside from
 > "Don't use that SQL"?
 >
 >
 >     - Jon
 >
 >
 >     On Thu, Apr 18, 2019 at 4:03 PM David Storrs
mailto:david.sto...@gmail.com>
 >     >> wrote:
 >
 >         I'm having trouble with using the the 'ON CONFLICT'
clause with
 >         a SQLite database.  Example:
 >
 >          > (query-exec db "drop table if exists peers")
 >          > (query-exec db "create table if not exists peers (name
text)")
 >          > (query-exec db "INSERT INTO peers ( name ) VALUES
($1)" "hqwt")
 >          > (query-rows db "SELECT * FROM peers")
 >         '(#("hqwt"))
 >          > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON
 >         CONFLICT DO NOTHING" "hqwt")
 >         ; query-exec: near "ON": syntax error
 >         ;   error code: 1
 >         ; [,bt for context]
 >
 >         I thought maybe there was some issue with the column not
being
 >         marked UNIQUE, so I did that.  No effect:
 >
 >          > (query-exec db "drop table if exists peers")
 >          > (query-exec db "create table if not exists peers (name
text
 >         UNIQUE)")
 >          > (query-exec db "INSERT INTO peers ( name ) VALUES
($1)" "hqwt")
 >          > (query-exec db "INSERT INTO peers ( name ) VALUES
($1)" "hqwt")
 >         ; query-exec: abort due to constraint violation
 >         ;   error code: 2067
 >         ;   SQL: "INSERT INTO peers ( name ) VALUES ($1)"
 >         ; [,bt for context]
 >          > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON
 >         CONFLICT DO NOTHING" "hqwt")
 >         ; query-exec: near "ON": syntax error
 >         ;   error code: 1
 >         ; [,bt for context]
 >
 >         I've been through the SQLite documentation on the INSERT
 >         statement multiple times, and that last statement sure looks
 >         syntactically valid to me.  Furthermore, it works fine if
I use
 >         it in the OSX 10.11.6 sqlite3 CLI client.
 >
 >         What am I missing?
 >
 >         --
 >         You received this message because you are subscribed to the
 >         Google Groups "Racket Users" group.
 >         To unsubscribe from this group and stop receiving emails from
 >         it, send an email to
racket-users+unsubscr...@googlegroups.com

 >         >.
 >         For more options, visit https://groups.google.com/d/optout.
 >
 > --
 > You received this message because you are subscribed to the Google
 > Groups "Racket Users" group.
 > To unsubscribe from this group and stop receiving emails from it,
send
 > an email to racket-users+unsubscr...@googlegroups.com

 > >.
 > For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google 
Groups "Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to racket-users+unsubscr...@googlegroups.com 
.

For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "Racket 
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit 

Re: [racket-users] db module + SQLite driver + 'ON CONFLICT' = syntax error

[Ryan Culpepper]

Use "INSERT OR IGNORE" instead. See 
https://sqlite.org/lang_conflict.html, 3rd paragraph.


Ryan


On 4/18/19 23:28, David Storrs wrote:



On Thu, Apr 18, 2019 at 4:48 PM Jon Zeppieri > wrote:


It might well be the SQLlite version. This is a pretty new feature.
It's possible that the db library is using an older version than
your CLI client.


Got it.  Is there any way for me to address the situation aside from 
"Don't use that SQL"?



- Jon


On Thu, Apr 18, 2019 at 4:03 PM David Storrs mailto:david.sto...@gmail.com>> wrote:

I'm having trouble with using the the 'ON CONFLICT' clause with
a SQLite database.  Example:

 > (query-exec db "drop table if exists peers")
 > (query-exec db "create table if not exists peers (name text)")
 > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
 > (query-rows db "SELECT * FROM peers")
'(#("hqwt"))
 > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON
CONFLICT DO NOTHING" "hqwt")
; query-exec: near "ON": syntax error
;   error code: 1
; [,bt for context]

I thought maybe there was some issue with the column not being
marked UNIQUE, so I did that.  No effect:

 > (query-exec db "drop table if exists peers")
 > (query-exec db "create table if not exists peers (name text
UNIQUE)")
 > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
 > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
; query-exec: abort due to constraint violation
;   error code: 2067
;   SQL: "INSERT INTO peers ( name ) VALUES ($1)"
; [,bt for context]
 > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON
CONFLICT DO NOTHING" "hqwt")
; query-exec: near "ON": syntax error
;   error code: 1
; [,bt for context]

I've been through the SQLite documentation on the INSERT
statement multiple times, and that last statement sure looks
syntactically valid to me.  Furthermore, it works fine if I use
it in the OSX 10.11.6 sqlite3 CLI client.

What am I missing?

-- 
You received this message because you are subscribed to the

Google Groups "Racket Users" group.
To unsubscribe from this group and stop receiving emails from
it, send an email to racket-users+unsubscr...@googlegroups.com
.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google 
Groups "Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send 
an email to racket-users+unsubscr...@googlegroups.com 
.

For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "Racket 
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [racket-users] db module + SQLite driver + 'ON CONFLICT' = syntax error

[David Storrs]

On Thu, Apr 18, 2019 at 5:42 PM Ryan Culpepper  wrote:

> Use "INSERT OR IGNORE" instead. See
> https://sqlite.org/lang_conflict.html, 3rd paragraph.
>

Yep.  Unfortunately, this was a simplified case of wanting to use OR DO
UPDATE to make this actually an upsert.  When I started seeing this issue I
backed it out to OR DO NOTHING in order to simplify the syntax.


> Ryan
>
>
> On 4/18/19 23:28, David Storrs wrote:
> >
> >
> > On Thu, Apr 18, 2019 at 4:48 PM Jon Zeppieri  > > wrote:
> >
> > It might well be the SQLlite version. This is a pretty new feature.
> > It's possible that the db library is using an older version than
> > your CLI client.
> >
> >
> > Got it.  Is there any way for me to address the situation aside from
> > "Don't use that SQL"?
> >
> >
> > - Jon
> >
> >
> > On Thu, Apr 18, 2019 at 4:03 PM David Storrs  > > wrote:
> >
> > I'm having trouble with using the the 'ON CONFLICT' clause with
> > a SQLite database.  Example:
> >
> >  > (query-exec db "drop table if exists peers")
> >  > (query-exec db "create table if not exists peers (name text)")
> >  > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)"
> "hqwt")
> >  > (query-rows db "SELECT * FROM peers")
> > '(#("hqwt"))
> >  > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON
> > CONFLICT DO NOTHING" "hqwt")
> > ; query-exec: near "ON": syntax error
> > ;   error code: 1
> > ; [,bt for context]
> >
> > I thought maybe there was some issue with the column not being
> > marked UNIQUE, so I did that.  No effect:
> >
> >  > (query-exec db "drop table if exists peers")
> >  > (query-exec db "create table if not exists peers (name text
> > UNIQUE)")
> >  > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)"
> "hqwt")
> >  > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)"
> "hqwt")
> > ; query-exec: abort due to constraint violation
> > ;   error code: 2067
> > ;   SQL: "INSERT INTO peers ( name ) VALUES ($1)"
> > ; [,bt for context]
> >  > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON
> > CONFLICT DO NOTHING" "hqwt")
> > ; query-exec: near "ON": syntax error
> > ;   error code: 1
> > ; [,bt for context]
> >
> > I've been through the SQLite documentation on the INSERT
> > statement multiple times, and that last statement sure looks
> > syntactically valid to me.  Furthermore, it works fine if I use
> > it in the OSX 10.11.6 sqlite3 CLI client.
> >
> > What am I missing?
> >
> > --
> > You received this message because you are subscribed to the
> > Google Groups "Racket Users" group.
> > To unsubscribe from this group and stop receiving emails from
> > it, send an email to racket-users+unsubscr...@googlegroups.com
> > .
> > For more options, visit https://groups.google.com/d/optout.
> >
> > --
> > You received this message because you are subscribed to the Google
> > Groups "Racket Users" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> > an email to racket-users+unsubscr...@googlegroups.com
> > .
> > For more options, visit https://groups.google.com/d/optout.
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [racket-users] db module + SQLite driver + 'ON CONFLICT' = syntax error

[David Storrs]

On Thu, Apr 18, 2019 at 4:48 PM Jon Zeppieri  wrote:

> It might well be the SQLlite version. This is a pretty new feature. It's
> possible that the db library is using an older version than your CLI client.
>

Got it.  Is there any way for me to address the situation aside from "Don't
use that SQL"?


> - Jon
>
>
> On Thu, Apr 18, 2019 at 4:03 PM David Storrs 
> wrote:
>
>> I'm having trouble with using the the 'ON CONFLICT' clause with a SQLite
>> database.  Example:
>>
>> > (query-exec db "drop table if exists peers")
>> > (query-exec db "create table if not exists peers (name text)")
>> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
>> > (query-rows db "SELECT * FROM peers")
>> '(#("hqwt"))
>> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON CONFLICT DO
>> NOTHING" "hqwt")
>> ; query-exec: near "ON": syntax error
>> ;   error code: 1
>> ; [,bt for context]
>>
>> I thought maybe there was some issue with the column not being marked
>> UNIQUE, so I did that.  No effect:
>>
>> > (query-exec db "drop table if exists peers")
>> > (query-exec db "create table if not exists peers (name text UNIQUE)")
>> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
>> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
>> ; query-exec: abort due to constraint violation
>> ;   error code: 2067
>> ;   SQL: "INSERT INTO peers ( name ) VALUES ($1)"
>> ; [,bt for context]
>> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON CONFLICT DO
>> NOTHING" "hqwt")
>> ; query-exec: near "ON": syntax error
>> ;   error code: 1
>> ; [,bt for context]
>>
>> I've been through the SQLite documentation on the INSERT statement
>> multiple times, and that last statement sure looks syntactically valid to
>> me.  Furthermore, it works fine if I use it in the OSX 10.11.6 sqlite3 CLI
>> client.
>>
>> What am I missing?
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Racket Users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to racket-users+unsubscr...@googlegroups.com.
>> For more options, visit https://groups.google.com/d/optout.
>>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [racket-users] db module + SQLite driver + 'ON CONFLICT' = syntax error

[Jon Zeppieri]

It might well be the SQLlite version. This is a pretty new feature. It's
possible that the db library is using an older version than your CLI client.

- Jon


On Thu, Apr 18, 2019 at 4:03 PM David Storrs  wrote:

> I'm having trouble with using the the 'ON CONFLICT' clause with a SQLite
> database.  Example:
>
> > (query-exec db "drop table if exists peers")
> > (query-exec db "create table if not exists peers (name text)")
> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
> > (query-rows db "SELECT * FROM peers")
> '(#("hqwt"))
> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON CONFLICT DO
> NOTHING" "hqwt")
> ; query-exec: near "ON": syntax error
> ;   error code: 1
> ; [,bt for context]
>
> I thought maybe there was some issue with the column not being marked
> UNIQUE, so I did that.  No effect:
>
> > (query-exec db "drop table if exists peers")
> > (query-exec db "create table if not exists peers (name text UNIQUE)")
> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
> ; query-exec: abort due to constraint violation
> ;   error code: 2067
> ;   SQL: "INSERT INTO peers ( name ) VALUES ($1)"
> ; [,bt for context]
> > (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON CONFLICT DO
> NOTHING" "hqwt")
> ; query-exec: near "ON": syntax error
> ;   error code: 1
> ; [,bt for context]
>
> I've been through the SQLite documentation on the INSERT statement
> multiple times, and that last statement sure looks syntactically valid to
> me.  Furthermore, it works fine if I use it in the OSX 10.11.6 sqlite3 CLI
> client.
>
> What am I missing?
>
> --
> You received this message because you are subscribed to the Google Groups
> "Racket Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to racket-users+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[racket-users] db module + SQLite driver + 'ON CONFLICT' = syntax error

[David Storrs]

I'm having trouble with using the the 'ON CONFLICT' clause with a SQLite
database.  Example:

> (query-exec db "drop table if exists peers")
> (query-exec db "create table if not exists peers (name text)")
> (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
> (query-rows db "SELECT * FROM peers")
'(#("hqwt"))
> (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON CONFLICT DO
NOTHING" "hqwt")
; query-exec: near "ON": syntax error
;   error code: 1
; [,bt for context]

I thought maybe there was some issue with the column not being marked
UNIQUE, so I did that.  No effect:

> (query-exec db "drop table if exists peers")
> (query-exec db "create table if not exists peers (name text UNIQUE)")
> (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
> (query-exec db "INSERT INTO peers ( name ) VALUES ($1)" "hqwt")
; query-exec: abort due to constraint violation
;   error code: 2067
;   SQL: "INSERT INTO peers ( name ) VALUES ($1)"
; [,bt for context]
> (query-exec db "INSERT INTO peers ( name ) VALUES ($1) ON CONFLICT DO
NOTHING" "hqwt")
; query-exec: near "ON": syntax error
;   error code: 1
; [,bt for context]

I've been through the SQLite documentation on the INSERT statement multiple
times, and that last statement sure looks syntactically valid to me.
Furthermore, it works fine if I use it in the OSX 10.11.6 sqlite3 CLI
client.

What am I missing?

-- 
You received this message because you are subscribed to the Google Groups 
"Racket Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.