PureBasic Forums - English

Posted: **Sun Feb 25, 2007 12:03 pm**

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.

Posted: **Sun Feb 25, 2007 12:47 pm**

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

Posted: **Sat Mar 03, 2007 5:24 pm**

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?

Posted: **Sat Mar 03, 2007 5:25 pm**

netmaestro wrote: 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?

Posted: **Sat Mar 03, 2007 6:29 pm**

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

Posted: **Sat Mar 03, 2007 6:33 pm**

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?

Posted: **Sat Mar 03, 2007 7:22 pm**

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

Thanks netmaestro.

Posted: **Sat Mar 03, 2007 7:30 pm**

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..

Posted: **Sat Mar 03, 2007 7:36 pm**

But the internal PB database functions also use ODBC !

Posted: **Sat Mar 03, 2007 7:44 pm**

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

Posted: **Sat Mar 03, 2007 8:14 pm**

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..

Posted: **Sat Mar 03, 2007 10:06 pm**

Karbon wrote: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

PureBasic Forums - English

Use SQLite the easy way

Use SQLite the easy way

Re: Use SQLite the easy way