Sqlite Database Functions

BRexx API for accessing Sqlite databases. MySQL can be compiled as static with the brexx program or as a shared library that has to be imported to the interpreter before use with the command CALL import "sqlite.r".

Example

call import "sqlite.r"
call sqlite
call sqlopen "test.db"
call sql "create table square (id integer primary key, id2 integer, b blob)"
call sql "insert into square values (?,?,?)"	/* prepare insert statement */
/* insert records */
do i=1 to 10
	call sqlreset
	call sqlbind 1,"i",i
	call sqlbind 2,"i",i**2
	call sqlbind 3,"b",copies("sql",i)
	call sqlstep	/* perform the insertion */
end

/* read records */
say "Record=" sql("select * from square")
do until sqlstep()=="DONE"
	do i=1 to sqlget()
		call write ,sqlget(i)"|"
	end
	say
end
call sqlclose

Executes a SQL query specified as a sqlcmd string. Returns the number of affected rows.

sql('select * from test',2)

sql('insert into test set (name="Vasilis")')

sql('select last_insert_id()')

SQLBIND(col, <"B"|"I"|"F"|"D"|"S"|"T"|"N"|Z">, value)

Binds a parameter indexed by a ? at column position col by a value of type:
 B for blob
 I for integer
 D | F for double or float
 S | T for string or text
 N for null
 Z for zero blob

call sqlbind(1,"N")

call sqlbind(1,"D",10.0)

SQLCLOSE()

ends the connection with the Sqlite file. Doesn't return anything

call sqlclose

SQLGET(col)

return the value of the col column number of the current record

sqlget(2)	/* 'hello' */
sqlget(1)	/* 'test' */

SQLOPEN(database)

Opens file database as an sqlite3 database. connects to a MySQL server. Returns "0" if ok, otherwise the error number. Use the dberror to find the error.

dbconnect('localhost','root','rootpsw','mysql')	/* connect to the mysql db */
dbconnect('localhost',,,'test')	/* open the test db */

SQLITE()

Initializes the global variables of SQLITE. Look the sqlite.r library for the description

call sqlite

SQLRESET()

Resets the current statement

call sqlreset

SQLSTEP()

Moves to the next record and returns the word "ROW" if there is next row or "DONE" then arrives to the end of the query

call sqlstep