Use SQLite the easy way

[netmaestro]

This probably doesn't qualify as a trick, so let's call it a tip: You can make the freely-distributable ODBC driver for SQLite found at http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe part of the install program for your app. Then, you can simply do this and your SQLite3 database is fully accessible with PureBasic's native database commands:

Code: Select all

Declare AddConnection(Driver.s,ConnectString.s) 
Declare RemoveConnection(Driver.s,DSN.s) 

#ODBC_ADD_DSN    = 1    
#ODBC_CONFIG_DSN = 2 
#ODBC_REMOVE_DSN = 3 

;Replace File.s with a database that exists on your system! 
File.s = "i:\_movies\sample.db"
Driver.s = "SQLite3 ODBC Driver" 
Connectstring.s = "DSN=SessionDSN;Database="+File 
Result=AddConnection(Driver,Connectstring) 

If Result 
  InitDatabase() 
  db = OpenDatabase(0, "SessionDSN", "", "") 
  If db 
    Debug "Opened Successfully"
    CloseDatabase(0) 
    If RemoveConnection(Driver,"SessionDSN") 
      Debug "Closed Successfully" 
    EndIf 
  EndIf 
EndIf 

Procedure AddConnection(Driver.s,ConnectString.s) 
    Result=SQLConfigDataSource_(0,#ODBC_ADD_DSN,Driver,ConnectString ) 
    If Result 
      ProcedureReturn 1 
    EndIf 
EndProcedure 

Procedure RemoveConnection(Driver.s,DSN.s) 
    Result=SQLConfigDataSource_(0,#ODBC_REMOVE_DSN,Driver,"DSN="+DSN) 
    If Result 
      ProcedureReturn 1 
    EndIf 
EndProcedure 

That's all there is to it, no dlls or wrappers to mess with at all.

[Num3]

I wonder if with this ODBC driver you can have concurrent access to the database without any extra programming. (several connections with read/write...)

[Karbon]

Yes, but SQLite 3 still uses a file-level locking system. When one user writes to the DB the entire thing is locked.

Has anyone used PB's Database stuff on Windows Vista yet?

[Karbon]

Result=SQLConfigDataSource_(0,#ODBC_ADD_DSN,Driver,ConnectString [/code]

Where does the SQLConfigDataSource_() come from? Did you import the SQLite DLL using PB's DLLImporter tool?

[netmaestro]

It's a Windows API call. With the ODBC driver you don't need the dll at all.

[Karbon]

Oops

I read that as SQLITEConfigDataSource - thought it was SQLite specific!

How much testing have you done using this SQLite ODBC driver? And I don't know anything about ODBC, do you have to have admin privs on XP (or Vista) to set one up?

[srod]

Interesting. This might come in handy, even though it would involve abandoning my own SQLite include file!

Thanks netmaestro.

[Karbon]

Yea, ODBC scares me a little as that's another layer where something can go wrong. I really need to find out about admin permissions and creating DSNs too.

I'm trying to avoid getting into another situation like I'm in now -- using someone else's userlib that got abandoned... Moving to the internal PB database functions might be the ticket..

[srod]

But the internal PB database functions also use ODBC !

[Karbon]

Right, I know.. I'm saying that using this ODBC driver *might* be a good idea, depending on how robust it is.

[Karbon]

While we all have to have "use at your own risk" disclaimers, this one seems to go to an extreme (from the ODBC driver's README) :

"The driver is usable but may contain lots of memory leaks and all other kinds of bugs. Use it on your own risk."

Eek. Decisions decisions..

[KarLKoX]

Yes, but SQLite 3 still uses a file-level locking system. When one user writes to the DB the entire thing is locked.

Has anyone used PB's Database stuff on Windows Vista yet?

Right but the shared cache can help