Connecting to SQLite DB Question

[Former Member]

Hello,

  Got a quick question.  We want to use the SQLite database for a simple desktop application.  Is there a way to connect to and use this DB in PB without having to set up an ODBC or OLEdb connection?  I know how to do this on other languages, but not PB, and not sure if it can be done in PB.

  Any help will be greatly appreciated.

  Thanks,

Gary

[Former Member]

This was originally posted by another resource site I found when interested in doing the same thing, Not yet sure if this worked ; will be finding out tonight

edit: So after review looks like you need a third party ODBC Driver after all which someone has created as an open source

I used the ODBC driver : SQLite ODBC Driver

Source Site:

Powerbuilder Connect to SQLite version 3 database (ODBC): Anvil of Time

Modified the source example from the site to the following:

	string ls_dbparm, ls_dbfile, ls_exepath, ls_file[]
	ls_dbfile = 'c:\temp\sqlite.db3'
	IF Fileexists(ls_dbfile) THEN
	// do nothing
	ELSE
	IF GetFileOpenName('Select Data File',ls_dbfile, ls_file,'db3','SQLite files (*.db3),*.db3') < 1 THEN
	Messagebox('No Data File Chosen','Application will close')
	HALT CLOSE
	END IF
	END IF
	// SQLite connection
	ls_dbparm = "ConnectString='"
	// Driver installed with SQLite ODBC Driver
	ls_dbparm = ls_dbparm + "DRIVER=SQLite3 ODBC Driver;"
	ls_dbparm = ls_dbparm + "Database=" + ls_dbfile + "'"
	ls_dbparm = ls_dbparm + "UID=" + "admin" + ";PWD="
	ls_dbparm = ls_dbparm +  "'"
	sqlca.DbParm=ls_dbparm
	sqlca.DBMS = "ODBC"
	CONNECT USING SQLCA;
	IF (sqlca.sqlcode) <> 0 THEN
	MessageBox("Database Log On Error","Failed to Connect to Database" +string(sqlca.sqlcode)+" "+sqlca.DBparm  + sqlca.sqlerrtext)
	HALT CLOSE
	END IF

[former_member190719]

PowerBuilder is intended for use with databases that support a communications layer such as a native driver, ODBC, ADO.Net, OLEDB, etc.

It would certainly be technically possible to call it from PowerBuilder, particularly from a PBNI extension (a PowerBuilder Native Interface extension written in C++ that can use SQLite's C++ libraries directly).  It wouldn't make a lot of sense though.  You'd basically be doing everything in embedded SQL through external function calls and would lose a lot of the capabilities that PowerBuilder DataWindow gives you.

There are a number of other open source, royalty free databases out there you might want to consider that do provide a communications layer that PowerBuilder can work with.

[Former Member]

Bruce, Thanks.

You mentioned "There are a number of other open source, royalty free databases out there you might want to consider that do provide a communications layer that PowerBuilder can work with."  Can you list a couple of these?

I've been doing PB for many many years and have research several DB engines, but have not found any that can work with PB without ODBC, OLEdb, or some other connection setup.  Again, thanks.

Gary

[former_member190719]

>>but have not found any that can work with PB without ODBC, OLEdb

You might have misunderstood what I was saying.  You need to use ODBC or some other communications layer to make full use of PowerBuilder.

Probably the most popular open source database out there is MySQL.  The community edition is free to use.

Some people are a bit nervous about that product since Oracle bought them, so there is a fork of the product called MariaDB that remains fully open source.

Another popular open source database is PostgreSQL.