Author Topic: SQLite help  (Read 3971 times)

Offline Jonás Perusquía

  • Mr. Polyvector
  • ***
  • Posts: 170
    • View Profile
    • Ditrabox
SQLite help
« on: 2013-Feb-02 »
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)
<HTML><BASIC><EC>
Be free and do good things

Offline backspace

  • Mr. Drawsprite
  • **
  • Posts: 53
    • View Profile
Re: SQLite help
« Reply #1 on: 2013-Feb-02 »
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]
// --------------------------------- //
// 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
I came, I saw, I coded.

Offline Jonás Perusquía

  • Mr. Polyvector
  • ***
  • Posts: 170
    • View Profile
    • Ditrabox
Re: SQLite help
« Reply #2 on: 2013-Feb-02 »
thank you! you really helped me a lot!!!
<HTML><BASIC><EC>
Be free and do good things

Offline Jonás Perusquía

  • Mr. Polyvector
  • ***
  • Posts: 170
    • View Profile
    • Ditrabox
Re: SQLite help
« Reply #3 on: 2013-Feb-09 »
hey, could you please help me to add the values into a DDgui list? thank you so much :)
<HTML><BASIC><EC>
Be free and do good things

Offline Kitty Hello

  • code monkey
  • Administrator
  • Prof. Inline
  • *******
  • Posts: 10839
  • here on my island the sea says 'hello'
    • View Profile
    • http://www.glbasic.com
SQLite help
« Reply #4 on: 2013-Feb-09 »
Foreach item$ in items$[]
Inc Text$, item$+"|"
Next
Text$=rtrim$(Text$)
DDGui_Set("list", "TEXT", Text$)

Offline Jonás Perusquía

  • Mr. Polyvector
  • ***
  • Posts: 170
    • View Profile
    • Ditrabox
Re: SQLite help
« Reply #5 on: 2013-Feb-12 »
thank you so much Kitty Hello!!!!!
EDIT:
been working with backspace sample but the list keeps showing nothing :/

is this right?

Code: (glbasic) [Select]
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]
recordcount = sql.DBdoSql("select * from contacts;")
« Last Edit: 2013-Feb-12 by jonaspm »
<HTML><BASIC><EC>
Be free and do good things

Offline Jonás Perusquía

  • Mr. Polyvector
  • ***
  • Posts: 170
    • View Profile
    • Ditrabox
Re: SQLite help
« Reply #6 on: 2013-Feb-18 »
Ok, so i finally got it working with this:
Code: (glbasic) [Select]
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]
//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]
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
« Last Edit: 2013-Feb-18 by jonaspm »
<HTML><BASIC><EC>
Be free and do good things

Offline Jonás Perusquía

  • Mr. Polyvector
  • ***
  • Posts: 170
    • View Profile
    • Ditrabox
Re: SQLite help
« Reply #7 on: 2013-Feb-19 »
so figured out how to make it work!

i got the list working with this:
Code: (glbasic) [Select]
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])
<HTML><BASIC><EC>
Be free and do good things

Offline Jonás Perusquía

  • Mr. Polyvector
  • ***
  • Posts: 170
    • View Profile
    • Ditrabox
Re: SQLite help
« Reply #8 on: 2013-Feb-19 »
also added a "delete" button to delete records from database by looking at the list... code!
Code: (glbasic) [Select]
// 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 <3

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!
« Last Edit: 2013-Feb-19 by jonaspm »
<HTML><BASIC><EC>
Be free and do good things