PowerBuilder app with embedded database?

Question

Is it possible to use e.g. SQLite with PowerBuilder? I need an embedded open source database (no additional costs).

Answer 1

I don't believe that PowerBuilder contains a driver for native support to SQLite. But it definitely has a driver for ODBC, so that is always an option even if it isn't the most efficient one.

Answer 2

Like Bernard said, you'll need an ODBC driver, so as long as you're willing to go third party (if I understand the SQLite situation correctly), that should be no problem.

That said, if you have PowerBuilder, you have license to distribute the single-user SQL Anywhere run time engine. If no-cost is your only criteria, and you're only connecting locally, SQL Anywhere may be an option to evaluate. Not only is it an incredibly solid database, but there's a much larger base of documentation and experience connecting PowerBuilder to SQL Anywhere, so if you run into problems, you're more likely to get some help.

Good luck.

Answer 3

As noted, SQL Anywhere is available and solid. But it has a disadvantage--you can't change the schema using the run time engine. This makes it hard to, say, add a column to a db that you have distributed.

Answer 4

I used to use SQL Anywhere, but eventually ditched it for the reasons Joe Landau gave - can't change the schema using the distributable runtime engine.

I switched to Firebird, which has an embedded version, and that seems solid. The only issue is that the ODBC driver I'm using (Gemini), which seems to be the best one available, seems to have gone out of business. (I just checked - it seems to be available on other sites.) And you have to add the following to your PBODB*.INI file:

[Firebird]
PBSyntax='Firebird_SYNTAX'
PBNoCatalog='YES'

[Firebird_SYNTAX]
CreateTable='CREATE TABLE &TableName (::ColumnElement[::ColumnElement]...)'
ColumnElement='&ColumnName &DataType'
DropTable='DROP TABLE &TableName'
GetIdentity='Select gen_id(GEN_&TableName,0) from RDB$DATABASE'

I've been very happy with it. Using it for almost 2 years, with over 1,000 users, and no problems whatsoever. You can also easily switch to the Firebird server version if some users need that.