Basic SQL / SQLite3 questions

Discuss about plugins that don't have a dedicated forum
Post Reply
phipslk
Posts: 12
Joined: Sun Oct 29, 2023 7:21 pm

Basic SQL / SQLite3 questions

Post 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
User avatar
lazi
Posts: 625
Joined: Thu Feb 24, 2011 11:08 pm

Re: Basic SQL / SQLite3 questions

Post 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. :-)
phipslk
Posts: 12
Joined: Sun Oct 29, 2023 7:21 pm

Re: Basic SQL / SQLite3 questions

Post 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)
User avatar
lazi
Posts: 625
Joined: Thu Feb 24, 2011 11:08 pm

Re: Basic SQL / SQLite3 questions

Post 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?
phipslk
Posts: 12
Joined: Sun Oct 29, 2023 7:21 pm

Re: Basic SQL / SQLite3 questions

Post 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.
Post Reply