Fog Creek Software
Discussion Board




Embedded SQL server for VB?

I'm currently looking for the small SQL servers that don't require any installation besides copying a DLL or something.

At this point, I know of:
- embedded MySQL : works great from PowerBasic, but GPFs from VB
- SQLite : terrible performance through the AGS version
- embedded FireBird Interbase : no info at this point, as it's pretty recent and no documentation

Anybody knows of other solutions?

Thx
Fred.

Frederic Faure.
Friday, June 06, 2003

MSDE.

Good.  Fast.  Free.

What more could you want.  (Plus it is fully compatable with SQL Server 2000)

Eric Budd
Friday, June 06, 2003

Thx but... not open-source, and how does it install? Our customers have no computer training, no one to help them, and even running MySQL's wizard is too much asking. But then, if someone knows of a way to install eg. MySQL or Firebird silently... :-)

Frederic Faure.
Friday, June 06, 2003

Doesn't the package and deployment wizard create an install routine? And if it's a database app, why not develop it directly in Acess and save time?

Stephen Jones
Friday, June 06, 2003

When u do VB programs u should get a decent installer software. My personal favorite ist WISE install system (very likely because I worked with it longest). With this the installation can be fully automated and the MS-SQL engine installs in a snap....

If you are daring check out this SQL Database:
http://www.sqlite.org/

Hope that helps

Stephan H. Wissel
Friday, June 06, 2003

Thx guys.

As for MSDE : too big ("MSDE 1.0 System Requirements:
65-180 MB of Server; approximately 170 MB for typical installation"). Most of our customers are connected through a dial-up...

Access : What do we need to get this on a bare Windows host? The whole point is to have no or very few dependencies, so ADO, ODBC are out

SQLite : as explained, the AGS version that makes it possible to use SQLite from VB offers terrible performance (1,000 records in embedded MySQL = less than 1s; with AGS = 1mn30)

As for creating an install program, we might do this provided it can run silently with a response file. Even asking them to click on a few Next buttons should be avoided if possible. Otherwise, might's well go for MySQL :-)

Thx much

Frederic Faure
Friday, June 06, 2003

You don't need *anything* special for Access. It is just a database in a single file so you just copy the file and be done with it.

And get an installation package (Wise is my personal favorite) right away. It will solve many of your issues with end-user illiteracy.

marc
Friday, June 06, 2003

Er... to build Access DB's, each computer must have the MDAC, right?

Too big, must be installed... and we'd like to avoid having to actually visit each site and perform this on every computer.

Thx for the tip :-)

Frederic Faure
Friday, June 06, 2003

Marc,

How is copying an Access .MDB file going to act as a SQL server (lower case "s" in server)?

John Topley (www.johntopley.com
Friday, June 06, 2003

I think you need to give some more details here. It sound to me like you are going to hamstring yourself over fear of installation.

Making you technologies decisions based on fear is always the wrong choice.

marc
Friday, June 06, 2003

Not fear. Experience :-)

Simple:
- SQL server
- light-weight : no 30-Meg downloads
- silent install, or _very_ simple
- good performance, even on older computers (P2 running 95)

Too bad, because embedded MySQL works fine... if only I could understand why it runs from PowerBasic but GPFs from VisualBasic...

Frederic Faure
Friday, June 06, 2003

Advantage Local Server is one good option:  http://www.advantagedatabase.com/ADS/Product+Detail/Advantage+Local+Server/default.htm

Advantage comes from the Delphi world originally, but they've branched out and there's an ADO/OLEDB provider for their products.  The Local Server (fileserver) version is free and deploys just by placing the .dll in your executable's directory (I think).  The client/server version you do have to pay for but doesn't sound like your app would ever need to use it.

The Firebird embedded server is part of the Firebird 1.5 Release Candidate version (1.5 Gold to be out soon).  Firebird, of course, is open source, so it's free that way.  And Firebird is a client/server database that has most of the features of MS SQL Server (i.e., views, stored procedures, triggers, referential integrity, etc).

You're right that there isn't much information on the new embedded version, but I believe the current download of the Release Candidate version includes the embedded .dll that -- like Advantage -- you could just deploy by placing the .dll in your executable's directory.  If I recall the README file also has a bit more information on using the embedded version.  There are ADO providers available from third parties for little or no cost.
http://firebird.sourceforge.net/index.php

Herbert Sitz
Friday, June 06, 2003

Sorry, I think the new embedded version of Firebird may be usable from Delphi only.  Not sure, though.

Herbert Sitz
Friday, June 06, 2003

Thx Herbert. Indeed, embedded Firebird is included in the RC3 (fbembed.dll), but there's only a tiny readme, and no information on Firebird's and related sites to list the APIs, but it looks promising. I assume it's the same APIs as the cs version?

"The embedded server is a fully functional server linked as a dynamic library (fbembed.dll). It has exactly the same features as the usual server and exports the standard Firebird API entrypoints."

Seems like Yaffil, the Russian commercial company is coming up with stuff like this, but the site is only in Russian... the stuff is commercial, and considering the power struggle, I'm not too sure we should go this way now.

Thx again.

Frederic Faure
Friday, June 06, 2003

Take a look at Valentina.  It's been around for a while, it's cross-platform, and has a pretty good reputation based on what I have read (I have not used it myself).  There are lots of wrappers for different development tools, including an ActiveX component for VB.

http://www.paradigmasoft.com/

Christopher Morrison
Friday, June 06, 2003

Thx Christopher :-)

For those interested, Valentina is a commercial product, and the ActiveX version is available at $199.95.

Frederic Faure
Friday, June 06, 2003

Frederic,

In your SQLite performance comparisions, did you use SQL transactions?  If not, why not?

I wrote a quick PERL script that uses the ODBC wrapper around SQLite to insert 1000 records (in fact, the SQL code is taken from SQLite's own statistics page).

When I don't use transactions, the insert process takes roughly 90 seconds.  When I do use transactions, SQLite requires less than a second.  Also, note that SQLite supports a 'dump' format which can be used to do a bulk insert.

Norbert Burger
Friday, June 06, 2003

Thx Norbert. Since accessing SQLite from VB is only possible from the AGS version of the DLL, I don't know if I can tweak the performance. SQLite seems pretty hard to use natively from VB, hence AGS...

FWIW, here's the code:

DB_Handle = sqlite_open("test.db", 0, errs)
TableArray = ags_sqlite_get_table(DB_Handle, "create table tbl1(one varchar(10), two smallint);", strErrMsg)

'Slooooooooow
For iCounter = 0 To 1000
DoEvents
TableArray = ags_sqlite_get_table(DB_Handle, "insert into tbl1 values('hello!',10);;", strErrMsg)
Next iCounter

sqlite_close DB_Handle

Thx

Frederic Faure
Friday, June 06, 2003

Frederic,

See "Test 1" at http://www.sqlite.org/speed.html.  It explains exactly why you're getting poor performance with your code.  In your example, after each SQLExecute, SQLite is forced to fsync() the database file.

You can eliminate the performance problem by using transactions.

AGS' website is currently down, so I can't find out if the AGS code supports transactions.  But instead of the AGS wrapper, you could use the ODBC wrapper for SQLite (see http://www.ch-werner.de/sqliteodbc/).  There are numerous examples on the web for using ODBC from within VB.

As I wrote earlier, the following PERL code takes less than 1 second to complete.  It can be quickly ported to VB.

use Win32::ODBC;

$data = new Win32::ODBC("SQLite Datasource");
    
$sql = "BEGIN;"; $data->Sql($sql);

$sql = "CREATE TABLE sample(a INTEGER, b INTEGER, c VARCHAR(100));";
$data->Sql($sql);
for ($i=0; $i<1000; $i++) {
  $sql = "INSERT INTO sample VALUES ($i,1000-$i,'beta');";
  $data->Sql($sql);
}

$sql = "COMMIT;"; $data->Sql($sql);

$data->Close();

Norbert Burger
Friday, June 06, 2003

Ditch the VB and go with MS Visual FoxPro.

Easy to program and has native data engine.

LesC
Monday, June 09, 2003

Thx Norbert, that did it :-) Amazingly fast... Now, just have to figure out how to play with sqlite_get_table() in VB...

LesC : sorry, but we don't have the luxury of rewriting the whole damn thing in FoxPro.

Frederic Faure
Tuesday, June 10, 2003

CodeBase is the way to go.

Pablo
Saturday, June 14, 2003

Hi guys,

I have meet this topic where you discuss a "terrible performance" of SQL Lite :-), and mention Valentina.
I am developer of Valentina btw.
So what I want to say, that Valentina is able insert 100,000 records in 4 seconds into table with 13 fields. i.e. 25,000 per second. As I see your tests of SQL Lite give 1000 records per second into table with 3 fields.
It sounds like Valentina is 100 times faster of SQL Lite.
Interesting how you will name performance of Valentina? :-)

Rulan Zasukhin
Friday, July 11, 2003

*  Recent Topics

*  Fog Creek Home