Basic SQL / SQLite3 questions

[phipslk]

Hi all,
I am working on a database for magazines as a start with Hollywood and stumbled upon SQLite3 which seems to be a good plugin to use for that purpose.
I am very new to SQL. I tried my first steps with inserting data into a sql database. I oriented myself on the demo code which came with the plugin download. but my version doesn't work. any hints appreciated and I will come to more questions probably.

Code: Select all

@REQUIRE "sqlite3"

db = sqlite3.open_memory()

db:exec([[ CREATE TABLE test (id INTEGER PRIMARY KEY, name, publisher, year, issue,language,systems,isbn, condition) ]])

;stmt = db:prepare([[ INSERT INTO test VALUES (:key, :value) ]])

Function p_Bind(k,nam, publishe, yea, issu,languag,system,isb, conditio)	; key , value
stmt = db:prepare([[ INSERT INTO test VALUES (:key, :name, :publisher, :year, :issue, :language, :systems, :isbn, :condition ) ]])
stmt:bind_names({  key = k,  name=nam, publisher=publishe, year=yea, issue=issu,language=languag,systems=system,isbn=isb, condition=conditio})
stmt:dostep()
stmt:reset()
EndFunction

Local m1 = 	{name = "64er",
	publisher = "M&T",
	year = 1986,
	issue = 1,
	language = "german",
	systems = {"C64","VC20"},
	isbn = "123-456789-123-0",
	condition = 1
	}
	
Local m2=	{name = "Amiga Future",
	publisher = "APC&TCP",
	year = 2023,
	issue = 126,
	language = "german",
	systems = {"Amiga"},
	isbn = "123-765443-123-0",
	condition = 1
	}

p_Bind(NULL,name = "64er", publisher = "M&T", year = "1986", issue = "1", language = "german",	systems = "C64", isbn = "123-456789-123-0", condition = "1")
;p_Bind(2,Unpack(m2))

stmt:finalize()

For row In db:nrows("SELECT * FROM test")
  DebugPrint(row.id, row.publisher)
Next

[lazi]

Try this for calling your p_Bind():

Code: Select all

p_Bind(NULL,"64er", "M&T", "1986", "1", "german",  "C64", "123-456789-123-0", "1") 

Tell me if you want deeper explanation.

[phipslk]

ah, got it. stupid thing
of course I'd like a deeper explanation why doesn't it work with variables? in the included demo, it works by passing one value to the function?

Code: Select all

p_Bind(NULL,m1.name,m1.publisher,m1.year,m1.issue,m1.language,m1.systems,m1.isbn,m1.condition)

[lazi]

Let's see:

p_whatever (m1.name) - The argument is a value. The value of the "name" tag of the table "m1".
p_whatever ({name="C64 Rulez"}) - The argument is a table, which has a tag called "name".
p_whatever (name="C64 Kicks Ass") - The argument is the result of the equal operator comparing the "name" variable's value and the given string. The result is 0, means not equal. It makes no sense.

Is it clear now?

[phipslk]

Not quite yet, sorry

Perhaps it's easier if I explain what I want to achieve. I have a RapaGUI window and user input should be stored in the sql database being read from the GUI items values, and vice versa.