Confused about SQLite...

[Fangbeast]

I saved it in the PureLibraries/UserLibries

Those are PureBasic binary libraries and should not be touched.

Create a directory of your own and call it something like Modules or RModules (reusable modules) and stick your re-useable source code modules in there.

Easier to remember and no danger of accidentally damaging things in the pb userlibs directory.

[ts-soft]

> I saved TS-Soft static lib. I saved it in the PureLibraries/UserLibries
The required static lib comes with PB!
You have only to include my source as source, for example as
Sqlite3_include.pbi

greetings
Thomas

PS: the sun is shinning in berlin

[jmauver]

> I saved TS-Soft static lib. I saved it in the PureLibraries/UserLibries
The required static lib comes with PB!
You have only to include my source as source, for example as
Sqlite3_include.pbi

greetings
Thomas

PS: the sun is shinning in berlin

Hi TS-Soft,

Thanks for that. I get confused about libraries, statics, includes, modules etc... I am still improving my knowledge !

Regards.

[npath]

Autoincrement works fine for me using the native PB interface with SQLite. See below for a generic example.

Code: Select all

Procedure main()
  Define.l i
  
  If CreateFile(0, "test.db")
    CloseFile(0)
  Else
    MessageRequester("Error", "Can't create file.")
    ProcedureReturn
  EndIf
  
  UseSQLiteDatabase()
  
  If OpenDatabase(0, "test.db", "", "")
    If DatabaseUpdate(0, "create table table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, names TEXT)") = 0
      MessageRequester("Error", DatabaseError())
      CloseDatabase(0)
      ProcedureReturn
    EndIf
    
    For i = 1 To 5
      If DatabaseUpdate(0, "insert into table1 (names) values ('John Doe #" + Str(i) + "')") = 0
        MessageRequester("Error", DatabaseError())
        CloseDatabase(0)
        ProcedureReturn
      EndIf
    Next

    If DatabaseQuery(0, "select id, names from table1")
      While NextDatabaseRow(0)
        MessageRequester("ID: " + GetDatabaseString(0, 0), GetDatabaseString(0, 1))
      Wend
    EndIf
    
    CloseDatabase(0)
  Else
    MessageRequester("Error", "Can't open database.")
    ProcedureReturn
  EndIf
EndProcedure

main()

[jmauver]

Autoincrement works fine for me using the native PB interface with SQLite. See below for a generic example.

Code: Select all

Procedure main()
  Define.l i
  
  If CreateFile(0, "test.db")
    CloseFile(0)
  Else
    MessageRequester("Error", "Can't create file.")
    ProcedureReturn
  EndIf
  
  UseSQLiteDatabase()
  
  If OpenDatabase(0, "test.db", "", "")
    If DatabaseUpdate(0, "create table table1 (id INTEGER PRIMARY KEY AUTOINCREMENT, names TEXT)") = 0
      MessageRequester("Error", DatabaseError())
      CloseDatabase(0)
      ProcedureReturn
    EndIf
    
    For i = 1 To 5
      If DatabaseUpdate(0, "insert into table1 (names) values ('John Doe #" + Str(i) + "')") = 0
        MessageRequester("Error", DatabaseError())
        CloseDatabase(0)
        ProcedureReturn
      EndIf
    Next

    If DatabaseQuery(0, "select id, names from table1")
      While NextDatabaseRow(0)
        MessageRequester("ID: " + GetDatabaseString(0, 0), GetDatabaseString(0, 1))
      Wend
    EndIf
    
    CloseDatabase(0)
  Else
    MessageRequester("Error", "Can't open database.")
    ProcedureReturn
  EndIf
EndProcedure

main()

Thanks Npath !

I managed to create the AUTO INCREMENT field. The problem was to INSERT a new row as I was not aware what to put as a value to the field holding the AUTO INCREMENT. Then I found that a should place the world NULL as a paramenter to that field. Now it increments everytime a new record is inserted. I think in your code you specify in the INSERT command all the fields except the one holding the AUTO INC. I was Inserting a new record like "INSERT INTO mytable VALUES ('1','2',......)
So I was getting a error becase I did not specify a value for AUTO INC field.
Thanks

[npath]

jmauver,

You're welcome. You are right. You do not have to specify a value for the auto-incrementing primary key. SQLite will take care of this for you. By the way, I find that the PB interface works great for most of what I do with SQLite. In fact, I think that Fred has done an excellent job of keeping the syntax simple and consistent. The only problem you might run into with PB 4.2 is database locking, which has something to do with closing transactions. Fred has fixed the problem for the next version. If you have any troubles, just download the updated library at:

http://www.purebasic.com/beta/windows/

The library you need is DatabaseSQLite. Save this to the PureLibraries directory, for example (or wherever you keep your PureBasic install):

C:\Program Files\PureBasic\PureLibraries


Good luck coding.

[jmauver]

jmauver,

You're welcome. You are right. You do not have to specify a value for the auto-incrementing primary key. SQLite will take care of this for you. By the way, I find that the PB interface works great for most of what I do with SQLite. In fact, I think that Fred has done an excellent job of keeping the syntax simple and consistent. The only problem you might run into with PB 4.2 is database locking, which has something to do with closing transactions. Fred has fixed the problem for the next version. If you have any troubles, just download the updated library at:

http://www.purebasic.com/beta/windows/

The library you need is DatabaseSQLite. Save this to the PureLibraries directory, for example (or wherever you keep your PureBasic install):

C:\Program Files\PureBasic\PureLibraries


Good luck coding.

Hi npath,

Thanks again for your time on this.

I downloaded the DatabaseSQLite and placed it in the PureLibraries folder. So, I assume that PB will take care of that file without a need of me doing anything, is it correct ?

You said something about lock problems. I am having a problem to update a existing record. PB takes about 20 seconds trying to update a record. After 20 secods where my PB app freezes, my app comes to normal state but the record is not updated or SQLite gives any error messages. Have you had similar situation ? This the code that I use to update the record. The recID is global and holds a valid record number.

Code: Select all

Procedure UpdateRecord()

Debug "Recid is "+Str(recID) ; recID is global and hold the record number I want to update.

sqldef.s
result.l

If OpenDatabase(0, #DBPATH+#DBNAME, "", "") = 0
  MessageRequester("Erro", "Unable to open database!")
  End
EndIf

                
        
             
              sqldef = "UPDATE ex SET "
              sqldef = sqldef + "name="+"'" +GetGadgetText(#String_1)+"',"
              sqldef = sqldef + "birth="+"'" +GetGadgetText(#String_2)+"'," 
              sqldef = sqldef + "father="+"'" +GetGadgetText(#String_4)+"',"               
              sqldef = sqldef + "mother="+"'" +GetGadgetText(#String_5)+"'," 
              sqldef = sqldef + "hometown="+"'" +GetGadgetText(#String_6)+"'," 
              sqldef = sqldef + "address1="+"'" +GetGadgetText(#String_7)+"'," 
              sqldef = sqldef + "address2="+"'" +GetGadgetText(#String_8)+"'," 
              sqldef = sqldef + "address3="+"'" +GetGadgetText(#String_9)+"'," 
              sqldef = sqldef + "town="+"'" +GetGadgetText(#String_10)+"'," 
              sqldef = sqldef + "postcode="+"'" +GetGadgetText(#String_11)+"'," 
              sqldef = sqldef + "country="+"'" +GetGadgetText(#String_12)+"'," 
              sqldef = sqldef + "phonehome="+"'" +GetGadgetText(#String_13)+"'," 
              sqldef = sqldef + "phonework="+"'" +GetGadgetText(#String_14)+"'," 
              sqldef = sqldef + "phonemobile="+"'" +GetGadgetText(#String_15)+"'," 
              sqldef = sqldef + "phonefax="+"'" +GetGadgetText(#String_16)+"'," 
              sqldef = sqldef + "mainemail="+"'" +GetGadgetText(#String_17)+"'," 
              sqldef = sqldef + "otheremail2="+"'" +GetGadgetText(#String_18)+"'," 
              sqldef = sqldef + "workemail="+"'" +GetGadgetText(#String_19)+"'," 
              sqldef = sqldef + "workurl="+"'" +GetGadgetText(#String_20)+"'," 
              sqldef = sqldef + "personalurl="+"'" +GetGadgetText(#String_21)+"'," 
              sqldef = sqldef + "employername="+"'" +GetGadgetText(#String_22)+"'," 
              sqldef = sqldef + "employeraddress="+"'" +GetGadgetText(#String_23)+"'," 
              sqldef = sqldef + "wifename="+"'" +GetGadgetText(#String_47)+"'," 
              sqldef = sqldef + "numberofkids="+"'" +GetGadgetText(#String_48)+"'," 
              sqldef = sqldef + "socialneturl="+"'" +GetGadgetText(#String_49)+"'," 
              sqldef = sqldef + "activefrom="+"'" +GetGadgetText(#String_50)+"',"
              sqldef = sqldef + "activeto="+"'" +GetGadgetText(#String_51)+"',"  
              sqldef = sqldef + "linkedto="+"'" +GetGadgetText(#String_52)+"',"
              sqldef = sqldef + "txtfile="+"'" +GetGadgetText(#String_53)+"'"
              sqldef = sqldef + " WHERE id="+Str(recID)+";"  
              

              result = DatabaseUpdate(0, sqldef) 
              
              If result =0 :Debug "Error Updating Records":Debug sqldef
              
                     Debug DatabaseError()
              EndIf
               
             CloseDatabase(0)
             

EndProcedure

Thanks for any comments !

[Fangbeast]

Before you go any further, just clear up your syntax a little to make it easier to read.

You don't need "sqldef = sqldef +" on subsequent lines

Code: Select all

              sqldef + "name="+"'" +GetGadgetText(#String_1)+"',"
              sqldef + "birth="+"'" +GetGadgetText(#String_2)+"',"
              sqldef + "father="+"'" +GetGadgetText(#String_4)+"',"               
              sqldef + "mother="+"'" +GetGadgetText(#String_5)+"',"
              sqldef + "hometown="+"'" +GetGadgetText(#String_6)+"',"
              sqldef + "address1="+"'" +GetGadgetText(#String_7)+"',"
              sqldef + "address2="+"'" +GetGadgetText(#String_8)+"',"
              sqldef + "address3="+"'" +GetGadgetText(#String_9)+"',"
              sqldef + "town="+"'" +GetGadgetText(#String_10)+"',"
              sqldef + "postcode="+"'" +GetGadgetText(#String_11)+"',"
              sqldef + "country="+"'" +GetGadgetText(#String_12)+"',"
              sqldef + "phonehome="+"'" +GetGadgetText(#String_13)+"',"
              sqldef + "phonework="+"'" +GetGadgetText(#String_14)+"',"
              sqldef + "phonemobile="+"'" +GetGadgetText(#String_15)+"',"
              sqldef + "phonefax="+"'" +GetGadgetText(#String_16)+"',"
              sqldef + "mainemail="+"'" +GetGadgetText(#String_17)+"',"
              sqldef + "otheremail2="+"'" +GetGadgetText(#String_18)+"',"
              sqldef + "workemail="+"'" +GetGadgetText(#String_19)+"',"
              sqldef + "workurl="+"'" +GetGadgetText(#String_20)+"',"
              sqldef + "personalurl="+"'" +GetGadgetText(#String_21)+"',"
              sqldef + "employername="+"'" +GetGadgetText(#String_22)+"',"
              sqldef + "employeraddress="+"'" +GetGadgetText(#String_23)+"',"
              sqldef + "wifename="+"'" +GetGadgetText(#String_47)+"',"
              sqldef + "numberofkids="+"'" +GetGadgetText(#String_48)+"',"
              sqldef + "socialneturl="+"'" +GetGadgetText(#String_49)+"',"
              sqldef + "activefrom="+"'" +GetGadgetText(#String_50)+"',"
              sqldef + "activeto="+"'" +GetGadgetText(#String_51)+"'," 
              sqldef + "linkedto="+"'" +GetGadgetText(#String_52)+"',"
              sqldef + "txtfile="+"'" +GetGadgetText(#String_53)+"'"
              sqldef + " WHERE id="+Str(recID)+";" 

[pdwyer]

You will needs some cleaning checks on that. For example, if there is a ' inserted by the user then your insert will crash.

sqltext = replacestring(sqltext, "'", "''")

or something is needed.

If security is likely a problem then you need to be sure that people can't put SQL into an address field. Like how people attack web forms putting javascript in them you can do the same with SQL. you can catch keywords like INSERT, DELETE, TRUNCATE etc. If security is not an issue then you can forget that.

[npath]

jmauver,

You are correct. Once you copy the DatabaseSQLite library into the PureLibraries folder, PB will then be able to call the functions in the library. The only thing you have to do is restart the compiler so that it will see the new library. After you do this, you will be set.

The problem you are describing is exactly the same problem that I had, which is locking of the database. Apparently, I did not give you all of the necessary information. Do the following to solve the problem:

1. You have already copied DatabaseSQLite into the PureLibraries folder. I forgot that you also need the "Database" library from http://www.purebasic.com/beta/windows/. Just to be sure, you might also add "DatabaseODBC".


2. Restart the compiler (or just restart the PB IDE).


3. Add the line of code, FinishDatabaseQuery(0), just before closing the database, so that is reads:

Code: Select all

FinishDatabaseQuery(0)
CloseDatabase(0)

The FinishDatabaseQuery procedure will release the lock on the database. You need to do this after every database update. Please refer to the following for the details of the fix.

http://www.purebasic.fr/english/viewtop ... abasequery


This should solve your problem. All of this will be included in the next PB release, so that these steps will no longer be necessary if you install PB on another computer.


Let me know if you have trouble.

[jmauver]

Thank you pdwyer !

Thank you npath !

Yes, npath, it works fine now after installing the update you pointed me out. It is fast ! Working very well. Now it is time to make my app a bit more sofisticated.

Best regards to all,

[drahneir]

Hello,

after updating to PB 4.20 I also changed my SQLite related code to the PB database commands. And after a few weeks since then, I have not mentioned any disadvantages. My first thought was as well, that I needed a function to get the number of rows of a result set, but I didn't.
Before the change I used Kiffi's SQLite3 include file, and it worked well. To browse through a result set I used For...Next loops, and with that you really need the row count. But now I changed all these loops to
While NextDatabaseRow(#DB)...Wend or
Repeat...Until NextDatabaseRow(#DB) = #False,
which I think is the better code. And as a bonus, you don't have to move to the next row, which I liked to forget in the For...Next loops, and then wondered, why it didn't finish.
When you really need the number of rows, a three line command sequence will do the job.

Code: Select all

DatabaseQuery(#DB, "SELECT COUNT(*) FROM Table")
FirstDatabaseRow(#DB)
lNumRows = GetDatabaseLong(#DB, 0)

What I really missed, was the column access by name.
Tp overcome this, I wrote a little procedure, which converts the column name to the related index.

Code: Select all

Procedure.l GetDBColIdx(sColNam.s)
  Define lSI.l
  For lSI = 0 To DatabaseColumns(#DB) - 1
    If DatabaseColumnName(#DB, lSI) = sColNam
      ProcedureReturn lSI
    EndIf
  Next
EndProcedure

Now a columns access looks like

Code: Select all

str1 = GetDatabaseString(#DB, GetDBColIdx("XXX"))

After all, I am very satisfied with the change, and perhaps the column access by name will be a function of a PB future release.