Confused about SQLite...

[jmauver]

Hi guys,

I have PB 4.2 and I am aware that I can use SQLite database without any other library. I have been creating my application ok but I noticed that the functions available in PB for SQLite are not complete. For example, I want to know how many rows my Select has returned. At moment I have to use NextDatabaseRow() to count the rows one by one. Is there another way to get that ? Also I defined a INTEGER AUTO INCREMENT field but I am lost in the syntax of the INSERT statement in this case. The first field is the ID field which has the AUTO INCREMENT. If I ignore that field, I get a error message saying that is missing a value for a field. If I put a number, it does not increase automatically. What I am doing wrong ? Is it better download the SLQlite library from SQLlite site and use that library ? Is there a conflict with the SQLite already in PB ? Thanks for any light on this.

[ts-soft]

You can use the static lib from pb directly with API, but in the moment, you
can't mix this. See here:
http://www.purebasic.fr/english/viewtop ... 853#240853

Greetings
Thomas

[pdwyer]

... but I noticed that the functions available in PB for SQLite are not complete.

You answered your own question

There's quite a bit missing. I'm not sure if this is something they intend to build on in future version and expand the command set in the PB DB or not though ( I hope they do). I would say if you have more than basic database needs, use the dll with an include file.

[jmauver]

Thanks guys. I am really a beginner here so would you tell me which library and include files I should download and where to place them ? Once placed, is it just a matter of calling the functions as I would do normally in PB ? Sorry for my ignorance on this. I just do not want to mess up with my PB installation.

Thanks again!

[pdwyer]

Follow TS_Softs link for an include file, the latest sqlite dll can be downloaded at www.sqlite.org or I think it can use the lib file that PB bundle, not sure though.

[Fangbeast]

TS-Soft's include uses the statically linked SqLite library already in PB4.20, so no other dll is needed.

[jmauver]

So, I copied and pasted the TS-Soft's static lib to notepad. Do I need compile it first or just save the file somewhere in my PB folder ? Sorry for this.

Thanks !

[pdwyer]

the lib is part of PB 4.2 you shouldn't need anything

[jmauver]

Hi pdwyer,

Thanks for the explanation. Would it be possible you show me a very simple code as kick start for me ? I get a error message when using "SQLiteInit()". Thanks !

[pdwyer]

Actually I don't use that include file, I have my own (simpler one), but it uses the DLL.

Perhaps if you posted an error?

[jmauver]

Actually I don't use that include file, I have my own (simpler one), but it uses the DLL.

Perhaps if you posted an error?

This code here does not work...

Code: Select all

If SQLiteInit() 
  hDB = SQLiteOpen("c:\MyDB.db", #True) 
  ; reading 
  SQLiteClose(hDB.l) 
  hDB = SQLiteOpen("c:\MyDB.db", #False) 
  ;writing 
  SQLiteClose(hDB.l) 

EndIf

[Fangbeast]

Save TS-Soft's include file as a .PB or .PBI file (in keeping with pb naming conventions) and then XINCLUDEFILE that file at state start of any program using those functions.

The below is an excerp of my document manager

Code: Select all

; Any specific modules that have to load first

XIncludeFile "\Development\Resources\Modules\_SqLite3Base.pbi"                          ; TS-Soft's SqLite functions for PB4.2

; Try to open the system database and create missing tables

Global dbHandle.l = SQLiteOpen("MyDatabase.db")                                          ; Make handle global for other functions to use
If Program\dbHandle
  Query.s = "CREATE TABLE IF NOT EXISTS documents("
  Query.s + "added TEXT, lastrun TEXT, docnum TEXT, title TEXT, owner TEXT, active TEXT, note TEXT, filename TEXT, "
  Query.s + "directory TEXT, filesize TEXT, md5 TEXT CONSTRAINT md5 UNIQUE, created TEXT, accessed TEXT, modified TEXT, "
  Query.s + "readonly TEXT, archive TEXT, system TEXT, hidden TEXT, normal TEXT, backedup TEXT, compressed TEXT, "
  Query.s + "lastbackedup TEXT, category TEXT, subcategory TEXT, deleted TEXT, "
  Query.s + "record INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)"
  If SQliteExecute(dbHandle, Query.s) = #False
    Debug SQLiteErrorMsg(dbHandle)
  Else
    Debug " Opened and ready to work"
  EndIf
Else
  Debug SQLiteErrorMsg(dbHandle)
EndIf 

; This creates and/or opens the database and tables. Now do your reading, writing and close at program end

[harff182]

I had the same problem a while ago, when switching from DLL to static.
The macro SQLiteInit never returned "TRUE", AFAIR.
(And I must confess, I don't understand, why )
After replacing

Code: Select all

Macro SQLiteInit(a="")
EndMacro

by

Code: Select all

Procedure SQLiteInit()
  ProcedureReturn #True
EndProcedure;

everything worked again:

Code: Select all

XIncludeFile "sqlite_static_420.pbi"

If SQLiteInit()
  hDB = SQLiteOpen("MyDB.db")
  ; reading
  SQLiteClose(hDB.l)
  hDB = SQLiteOpen("MyDB.db")
  ;writing
  SQLiteClose(hDB.l)
EndIf

hth...

[ts-soft]

SQLiteInit is useless in this version and removed.
This Code make this automatic for you:

Code: Select all

UseSQLiteDatabase()

ImportC ""
  sqlite3_close (hDB.l)
  sqlite3_errcode (hDB.l)
  sqlite3_errmsg (hDB.l) ; ... 

[jmauver]

Save TS-Soft's include file as a .PB or .PBI file (in keeping with pb naming conventions) and then XINCLUDEFILE that file at state start of any program using those functions.

The below is an excerp of my document manager

Code: Select all

; Any specific modules that have to load first

XIncludeFile "\Development\Resources\Modules\_SqLite3Base.pbi"                          ; TS-Soft's SqLite functions for PB4.2

; Try to open the system database and create missing tables

Global dbHandle.l = SQLiteOpen("MyDatabase.db")                                          ; Make handle global for other functions to use
If Program\dbHandle
  Query.s = "CREATE TABLE IF NOT EXISTS documents("
  Query.s + "added TEXT, lastrun TEXT, docnum TEXT, title TEXT, owner TEXT, active TEXT, note TEXT, filename TEXT, "
  Query.s + "directory TEXT, filesize TEXT, md5 TEXT CONSTRAINT md5 UNIQUE, created TEXT, accessed TEXT, modified TEXT, "
  Query.s + "readonly TEXT, archive TEXT, system TEXT, hidden TEXT, normal TEXT, backedup TEXT, compressed TEXT, "
  Query.s + "lastbackedup TEXT, category TEXT, subcategory TEXT, deleted TEXT, "
  Query.s + "record INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)"
  If SQliteExecute(dbHandle, Query.s) = #False
    Debug SQLiteErrorMsg(dbHandle)
  Else
    Debug " Opened and ready to work"
  EndIf
Else
  Debug SQLiteErrorMsg(dbHandle)
EndIf 

; This creates and/or opens the database and tables. Now do your reading, writing and close at program end

Thanks Fangbeast !
I got it working ! I saved TS-Soft static lib. I saved it in the PureLibraries/UserLibries, not sure if it is the right place. But it worked. I changed your code a little bit as it was giving an error for the structure. This the code that worked:

Code: Select all

XIncludeFile "C:\Program Files\PureBasic\PureLibraries\UserLibraries\sqlite3.pbi" 

Global dbHandle.l = SQLiteOpen("c:\MyDatabase.db")                                          ; Make handle global for other functions to use 

  Query.s = "CREATE TABLE IF NOT EXISTS documents(" 
  Query.s + "added TEXT, lastrun TEXT, docnum TEXT, title TEXT, owner TEXT, active TEXT, note TEXT, filename TEXT, " 
  Query.s + "directory TEXT, filesize TEXT, md5 TEXT CONSTRAINT md5 UNIQUE, created TEXT, accessed TEXT, modified TEXT, " 
  Query.s + "readonly TEXT, archive TEXT, system TEXT, hidden TEXT, normal TEXT, backedup TEXT, compressed TEXT, " 
  Query.s + "lastbackedup TEXT, category TEXT, subcategory TEXT, deleted TEXT, " 
  Query.s + "record INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)" 
  If SQliteExecute(dbHandle, Query.s) = #False 
    Debug SQLiteErrorMsg(dbHandle) 
  Else 
    Debug " Opened and ready to work" 
  EndIf 

So I presume I can use all the functions available on TS-Soft's static library. I am going to replace all my code in my application as I was getting stuck with the lack of flexibility of PB built-in SQLlite commands.

Again, thanks all for all replies.