Page 1 of 1

Basic SQL / SQLite3 questions

Posted: Wed Nov 01, 2023 4:42 pm
by 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

Re: Basic SQL / SQLite3 questions

Posted: Wed Nov 01, 2023 6:36 pm
by 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. :-)

Re: Basic SQL / SQLite3 questions

Posted: Wed Nov 01, 2023 6:47 pm
by 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)

Re: Basic SQL / SQLite3 questions

Posted: Wed Nov 01, 2023 9:42 pm
by 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?

Re: Basic SQL / SQLite3 questions

Posted: Thu Nov 02, 2023 8:28 pm
by 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.