SQLite and Parameters

General questions that have to do with plugins go here
Post Reply
GMKai
Posts: 62
Joined: Mon Feb 15, 2010 11:58 am

SQLite and Parameters

Post by GMKai » Wed May 02, 2018 11:00 pm

Hello,
I try to pass parameters, to a dynamic sql, here the failing short example.
What am I doing wrong?

Code: Select all

@VERSION 7,0
@REQUIRE "sqlite3"

db = sqlite3:open_memory()
Local res = db:exec([[
  CREATE TABLE db_stadionblock
  (clubid INTEGER Not NULL, blockid STRING(1) Not NULL, size INTEGER,cond INTEGER,state VARCHAR(40));
   ]])
DebugPrint(res)
Local str$="INSERT INTO db_stadionblock VALUES (22,'A',12000,100,'-');"
DebugPrint(str$)
res = db:exec(str$)
DebugPrint(res)
;should work, now the defective statement:
DebugPrint("ERROR coming:")
Local str$ = "UPDATE db_stadionblock SET cond = cond - ? where clubid = ? and state = '-';"
stmt = db:prepare(str$)
Local res = stmt:bind(3,22)
DebugPrint(res)
stmt:dostep()
stmt:finalize()            

Gives the following output:

Code: Select all

0
INSERT INTO db_stadionblock VALUES (22,'A',12000,100,'-');
0
ERROR coming:
Work:Cubic IDE/Unbenannt.hws:18: Bind index out of range [1..%ld]!

User avatar
airsoftsoftwair
Posts: 2557
Joined: Fri Feb 12, 2010 3:33 pm
Location: Germany
Contact:

Re: SQLite and Parameters

Post by airsoftsoftwair » Sun May 06, 2018 12:09 am

Lazi? :)

User avatar
lazi
Posts: 295
Joined: Fri Feb 25, 2011 12:08 am

Re: SQLite and Parameters

Post by lazi » Tue Jul 24, 2018 1:26 pm

Sorry for the late reply!

As far as I can see, the syntax of bind only allows one parameter at a time.
The documentation says: stmt:bind(n[,value])

So you have to change the bind usage in your example to something like this:

stmt:bind(1,3)
stmt:bind(2,22)

The first value always the index of the parameter and the second the parameter itself.

GMKai
Posts: 62
Joined: Mon Feb 15, 2010 11:58 am

Re: SQLite and Parameters

Post by GMKai » Thu Aug 16, 2018 2:07 pm

or maybe use
bind_values()
instead!
Thanks for your effort

Post Reply