SQLite help

[Jonás Perusquía]

Hello my friends , i have a question for you...

¿Is there any tutorial for using the SQLite port for GLBasic?

the available information is a bit little and im using DDgui and SQLite for a project.

And making some tests, givea me a very complex code that its hard to understand... (even for me and i wrote it xD)

[backspace]

It also took me a while to figure the sqlite thing out, But once you get the hang of it, it's a great tool. There was a need for another level of wrapping in order to make it more GLBasic friendly. I've done that to make it easier for myself to use. I've included an example that has my wrapper functions. I hope you find it useful.

Code (glbasic) Select Expand

// --------------------------------- //
// Project:      sqlite-test00
// Description:  a test of sqlite
// --------------------------------- //

GLOBAL SQL AS SQLITE     //sqllite wrapper       
GLOBAL fields$[]
DIM fields$[5]           //number of fields in database
LOCAL recordcount


DBInit("myfriends.db")   //set database name
DBSetDebug(FALSE)        //log file = GLBSqLite.debug

//Set database fields
fields$[0] = "NAME"
fields$[1] = "COUNTRY"
fields$[2] = "EMAIL"
fields$[3] = "PHONE"
fields$[4] = "BIRTHDAY"

//Drop table if it exists
DBExecute("drop table info;")

//Create table named 'info' from fields$[] array
DBCreateTable("info")

//Add friends to the database
DBExecute("insert into info (NAME, COUNTRY, EMAIL, PHONE, BIRTHDAY) values ('Abraham','Israel','abe@is.is','12345678','6 February');")
DBExecute("insert into info (NAME, COUNTRY, EMAIL, PHONE, BIRTHDAY) values ('Ivan','Ukraine','ivan@ukuks','12345678','7 February');")
DBExecute("insert into info (NAME, COUNTRY, EMAIL, PHONE, BIRTHDAY) values ('Scotty','Scotland','scotabe@sc.sc','12345678','8 February');")
DBExecute("insert into info (NAME, COUNTRY, EMAIL, PHONE, BIRTHDAY) values ('Spock','Vulcan','spock@vu.vu','12345678','9 February');")
DBExecute("insert into info (NAME, COUNTRY, EMAIL, PHONE, BIRTHDAY) values ('Jim','Starbase','jum@sb.sb','12345678','10 February');")

//Do a database query on all friends
recordcount = DBQuery("select * from info;")

//read all the retieved records - reuse the fields$ array for the data
FOR i% = 0 TO recordcount-1       //for each record retrieved
DBRead(i,fields$[])           //read the record into the fields array
FOR j% = 0 TO LEN(fields$)-1  //for each field in the record
DEBUG fields$[j]+"   "    //display the data
NEXT
DEBUG "\n"
NEXT

END

//---------------------------------------------------------------------
//Reads a retrieved database record
//---------------------------------------------------------------------
FUNCTION DBRead: record%, array$[]
LOCAL s$,_data$[]
s$ = SQL.DBgetRecord$(record)
SPLITSTR(s$,_data$[],";")
FOR i% = 0 TO LEN(_data$)-1
array$[i] = _data$[i]
NEXT
ENDFUNCTION


//---------------------------------------------------------------------
//Query the database - returns number of records found
//---------------------------------------------------------------------
FUNCTION DBQuery: query$
SQL.DBdoSql(query$)
RETURN SQL.DBgetRecordcount()
ENDFUNCTION


//---------------------------------------------------------------------
//Execute a sql command
//---------------------------------------------------------------------
FUNCTION DBExecute: command$
SQL.DBdoSql(command$)
ENDFUNCTION


//---------------------------------------------------------------------
//Create databse table from field$ array
//---------------------------------------------------------------------
FUNCTION DBCreateTable: tableName$
LOCAL s$ = "create table " + tableName$ + "("
FOR i% = 0 TO LEN(fields$)-1
s$ = s$ + fields$[i]+" text"
IF i < LEN(fields$)-1 THEN s$ = s$ + ", "
NEXT
s$ = s$ + ");"
SQL.DBdoSql(s$)
ENDFUNCTION


//---------------------------------------------------------------------
//Initialize the database - open in with a database name
//---------------------------------------------------------------------
FUNCTION DBInit: databaseName$
SQL.DBSetName(databaseName$)
ENDFUNCTION


//---------------------------------------------------------------------
//Set the dabase debugging on or off
//---------------------------------------------------------------------
FUNCTION DBSetDebug: state
IF state
SQL.DBdebugOn()
ELSE
SQL.DBdebugOff()
ENDIF
ENDFUNCTION


[Jonás Perusquía]

thank you! you really helped me a lot!!!

[Jonás Perusquía]

hey, could you please help me to add the values into a DDgui list? thank you so much

[Kitty Hello]

Foreach item$ in items$[]
Inc Text$, item$+"|"
Next
Text$=rtrim$(Text$)
DDGui_Set("list", "TEXT", Text$)

[Jonás Perusquía]

thank you so much Kitty Hello!!!!!
EDIT:
been working with backspace sample but the list keeps showing nothing :/

is this right?

Code (glbasic) Select Expand

FOR i% = 0 TO (recordcount)-1
sql.DBRead(i%,fields$[])
INC Text$, fields$[0]+" "+fields$[1]+" "+fields$[2]+"|"
NEXT
Text$=RTRIM$(Text$)
DDgui_set("list", "TEXT", Text$)

also, for recordcount im using

Code (glbasic) Select Expand

recordcount = sql.DBdoSql("select * from contacts;")

[Jonás Perusquía]

Ok, so i finally got it working with this:

Code (glbasic) Select Expand

recordcount = sql.DBgetRecordcount()

FOR i% = 0 TO (recordcount)-1
sql.DBRead(i%,fields$[])
INC Text$, fields$[1]+" "+fields$[2]+" "+fields$[0]+"|"
NEXT
Text$=RTRIM$(Text$)

And now i have a question but this time is about DDgui, since i want to get the current selected object on a list...

i found these three useful functions:

Code (glbasic) Select Expand

//DDgui_getitemtext$
//With this FUNCTION you can get the nth item text of a list box OR a radio group.
//DDgui_insertitem
//You can easily insert items IN between a list box OR a radio group with this FUNCTION.
//DDgui_deleteitem
//When you want TO DELETE an item from a list box, please use this FUNCTION.

However, DDgui_getitemtext$ is asking for an index, like this:

Code (glbasic) Select Expand

DDgui_getitemtext$(id$,index%)

Can you please tell me what should i place on index%? really thank you, and sorry for all my newbie questions

[Jonás Perusquía]

so figured out how to make it work!

i got the list working with this:

Code (glbasic) Select Expand

indexer$ = DDgui_get$("list","SELECT")
sql.DBRead(indexer$,fields$[])
DDgui_set("nametext", "TEXT",fields$[0])
DDgui_set("lastname1text", "TEXT",fields$[1])
DDgui_set("lastname2text", "TEXT",fields$[2])
DDgui_set("phonetext", "TEXT",fields$[3])

[Jonás Perusquía]

also added a "delete" button to delete records from database by looking at the list... code!

Code (glbasic) Select Expand

// DELETE RECORDS START
IF DDgui_get$("delete", "CLICKED") = TRUE
sql.DBdoSql("delete from contacts where NAME = '"+DDgui_get$("nametext","TEXT")+"' AND PHONE = '"+DDgui_get$("phonetext","TEXT")+"'")
DDgui_set("list", "TEXT", "")
    Text$=""
    sql.DBdoSql("select * from contacts;")
    recordcount = sql.DBgetRecordcount()
    FOR i% = 0 TO (recordcount)-1
sql.DBRead(i%,fields$[])
INC Text$, fields$[1]+" "+fields$[2]+" "+fields$[0]+"|"
NEXT
DDgui_set("list", "TEXT", Text$)
ENDIF
// DELETE RECORDS END

IM IN LOVE WITH THIS PROJECT

Thank you for all your help! backspace and Kitty Hello!!!
i will upload later a sample of this project :3 however :/ it is in spanish... but i will work on traslating it!