SQLite on Mac?
lazarus / 16-Databases

----------------------------------------------------------------------
qdos · 2011-02-20 17:16

I cant get the sqlite packages to work. I have the latest lazarus &
fpc (downloaded from lazarus website) installed.

Is there a class version instead? I dont really need components.
What is the best sqlite fpc unit(s) that works well?

----------------------------------------------------------------------
Phil · 2011-02-20 17:32

You can use the SQLite3db unit to programmatically create and work
with Sqlite databases.

Thanks.

-Phil

----------------------------------------------------------------------
Leledumbo · 2011-02-20 19:16

QuoteI cant get the sqlite packages to work.Please always state what's
the real problem. Can't compile? Access violation at runtime?

As Phil said, you could also work with the component programmatically
instead of using the form designer.

----------------------------------------------------------------------
qdos · 2011-02-20 23:50

Well the problem is this:

1. I add the sqlite package, and try to recompile lazarus.
2. This fails with a lot of missing symbols (which i suspect have to
do with sqLite version)

Whenever i include the "sqlite" unit, the app wont compile. I get this
error, also with the sqlite3db unit

Undefined symbols:
  "_sqlite3_interrupt", referenced from:
_SQLITE3DB_TSQLITE_$__CANCEL$$BOOLEAN in sqlite3db.o
  "_sqlite3_complete", referenced from:
_SQLITE3DB_TSQLITE_$__ISCOMPLETE$ANSISTRING$$BOOLEAN in sqlite3db.o
  "_sqlite3_busy_handler", referenced from:
_SQLITE3DB_TSQLITE_$__SETBUSYTIMEOUT$LONGINT in sqlite3db.o
_SQLITE3DB_TSQLITE_$__SETBUSYTIMEOUT$LONGINT in sqlite3db.o
  "_sqlite3_busy_timeout", referenced from:
_SQLITE3DB_TSQLITE_$__SETBUSYTIMEOUT$LONGINT in sqlite3db.o
  "_sqlite3_changes", referenced from:
_SQLITE3DB_TSQLITE_$__QUERY$ANSISTRING$TSTRINGS$$BOOLEAN in
sqlite3db.o
  "_sqlite3_last_insert_rowid", referenced from:
_SQLITE3DB_TSQLITE_$__LASTINSERTROW$$LONGINT in sqlite3db.o
  "_sqlite3_free", referenced from:
_SQLITE3DB_TSQLITE_$__CREATE$ANSISTRING$$TSQLITE in sqlite3db.o
_SQLITE3DB_TSQLITE_$__QUERY$ANSISTRING$TSTRINGS$$BOOLEAN in
sqlite3db.o
  "_sqlite3_exec", referenced from:
_SQLITE3DB_TSQLITE_$__QUERY$ANSISTRING$TSTRINGS$$BOOLEAN in
sqlite3db.o
  "_sqlite3_close", referenced from: _SQLITE3DB_TSQLITE_$__DESTROY in
sqlite3db.o
  "_sqlite3_open", referenced from:
_SQLITE3DB_TSQLITE_$__CREATE$ANSISTRING$$TSQLITE in sqlite3db.o
  "_sqlite3_errmsg", referenced from:
_SQLITE3DB_TSQLITE_$__CREATE$ANSISTRING$$TSQLITE in sqlite3db.o
_SQLITE3DB_TSQLITE_$__ERRORMESSAGE$LONGINT$$ANSISTRING in sqlite3db.o
ld: symbol(s) not found
Error: Error while linking

----------------------------------------------------------------------
Phil · 2011-02-21 00:26

Which sqlite package? There are two packages of components included
with Lazarus. I would recommend sqldblaz.lpk. Both install fine here
both in 0.9.28.2 and 0.9.29.

However, you indicated that you don't need components, hence my
suggestion of sqlite3. You don't need to install anything to work with
it programmatically.

Thanks.

-Phil

----------------------------------------------------------------------
qdos · 2011-02-21 00:41

I have the sqldblaz selected, and it compiles file.
But what units can i use to access it? How to i use this package? Is
it without components (just classes?)?
I did not see any difference on the component palette.

----------------------------------------------------------------------
Phil · 2011-02-21 00:48

http://wiki.lazarus.freepascal.org/SQLdb_Package

Once you've rebuilt Lazarus, the components will be on the SQLdb tab.

Thanks.

-Phil

----------------------------------------------------------------------
qdos · 2011-02-21 00:50

The error still persists:

Undefined symbols:
  "_sqlite3_interrupt", referenced from:
_SQLITE3DB_TSQLITE_$__CANCEL$$BOOLEAN in sqlite3db.o
  "_sqlite3_complete", referenced from:
_SQLITE3DB_TSQLITE_$__ISCOMPLETE$ANSISTRING$$BOOLEAN in sqlite3db.o
  "_sqlite3_busy_handler", referenced from:
_SQLITE3DB_TSQLITE_$__SETBUSYTIMEOUT$LONGINT in sqlite3db.o
_SQLITE3DB_TSQLITE_$__SETBUSYTIMEOUT$LONGINT in sqlite3db.o
  "_sqlite3_busy_timeout", referenced from:
_SQLITE3DB_TSQLITE_$__SETBUSYTIMEOUT$LONGINT in sqlite3db.o
  "_sqlite3_changes", referenced from:
_SQLITE3DB_TSQLITE_$__QUERY$ANSISTRING$TSTRINGS$$BOOLEAN in
sqlite3db.o
  "_sqlite3_last_insert_rowid", referenced from:
_SQLITE3DB_TSQLITE_$__LASTINSERTROW$$LONGINT in sqlite3db.o
  "_sqlite3_free", referenced from:
_SQLITE3DB_TSQLITE_$__CREATE$ANSISTRING$$TSQLITE in sqlite3db.o
_SQLITE3DB_TSQLITE_$__QUERY$ANSISTRING$TSTRINGS$$BOOLEAN in
sqlite3db.o
  "_sqlite3_exec", referenced from:
_SQLITE3DB_TSQLITE_$__QUERY$ANSISTRING$TSTRINGS$$BOOLEAN in
sqlite3db.o
  "_sqlite3_close", referenced from: _SQLITE3DB_TSQLITE_$__DESTROY in
sqlite3db.o
  "_sqlite3_open", referenced from:
_SQLITE3DB_TSQLITE_$__CREATE$ANSISTRING$$TSQLITE in sqlite3db.o
  "_sqlite3_errmsg", referenced from:
_SQLITE3DB_TSQLITE_$__CREATE$ANSISTRING$$TSQLITE in sqlite3db.o
_SQLITE3DB_TSQLITE_$__ERRORMESSAGE$LONGINT$$ANSISTRING in sqlite3db.o
ld: symbol(s) not found
Error: Error while linking

Im fairly sure this is a version error.... (?)

From what i see, my Mac has sqLite 3.7.5 installed

----------------------------------------------------------------------
Phil · 2011-02-21 01:09

How did you determine 3.7.5?

What version of FPC and Lazarus do you have installed? Give numbers,
not "latest".

Thanks.

-Phil

----------------------------------------------------------------------
Leledumbo · 2011-02-21 02:37

Usually the lines before these undefined symbols would say something
like: "import library for xxx not found, linking may fail"
Many (well, I haven't seen one that doesn't) FPC packages link to
generic version of a library (i.e. libxxx.so, not libxxx.so.a.b.c), so
you maybe need to make a symlink to your lib for that.

----------------------------------------------------------------------
Phil · 2011-02-21 03:34

No, this is OS X, not Linux. On 10.6 both are already there and it's
the more specific one that's the symlink:

lrwxr-xr-x  1 root  wheel       16 Jun  9  2010 libsqlite3.0.dylib ->
libsqlite3.dylib
-rwxr-xr-x  1 root  wheel  2438496 Nov  6 21:55 libsqlite3.dylib

With both FPC 2.4.2 and 2.5.1, it links against the library like this
(see sqlite3.inc):

{$ifdef darwin}
{$linklib sqlite3}
{$endif}

And sure enough, after compiling an app that uses SQLite3db, otool -L
reveals that it's linked as expected:

   /usr/lib/libsqlite3.dylib (compatibility version 9.0.0, current
version 9.6.0)

Hence my question about version. I would guess that he's using FPC
2.2.4 - I don't think it knew how to link against sqlite3 correctly on
OS X.

Thanks.

-Phil

----------------------------------------------------------------------
qdos · 2011-02-21 07:58

Lazarus v: 0.9.28.2 beta (downloaded from the lazarus website under a
week ago)
FPC 2.2.4
Brand new imac, 7 days old

Downloaded from:
http://sourceforge.net/projects/lazarus/files/Lazarus%20Mac%20OS%20X%2
0i386/Lazarus%200.9.28.2/

----------------------------------------------------------------------
qdos · 2011-02-21 10:14

Does anyone have a set of units that targets a  DLL (dylib) directly?
Perhaps you could zip it down together with a dylib that is
compatible.

I presume the errors i'm getting is either:
1. The dylib is newer than the wrapper unit
2. the wrapper unit is newer than the version of sqlite i have
installed

I really need to get this working, im supposed to present fpc as an
alternative to C# on the mac.

----------------------------------------------------------------------
qdos · 2011-02-21 10:53

Well I'll be damned....

Suddenly, out of the blue -- it works.
Hm. While I love it when things work --- im a bit puzzled as to why it
now works.
I didnt do anything...

----------------------------------------------------------------------
Phil · 2011-02-21 14:33

Quote from: qdos on February 21, 2011, 08:58:46 amLazarus v: 0.9.28.2
beta (downloaded from the lazarus website under a week ago)
FPC 2.2.4
Brand new imac, 7 days old

Downloaded from:
http://sourceforge.net/projects/lazarus/files/Lazarus%20Mac%20OS%20X%2
0i386/Lazarus%200.9.28.2/

Those are ancient. FPC 2.4.2 is the current stable release. The
previous was 2.4.0 and before that was 2.2.4 - so you're two versions
behind.

Try FPC 2.4.2 and recent 0.9.29 snapshot of Laz.

Thanks.

-Phil

----------------------------------------------------------------------
Phil · 2011-02-21 14:35

Quote from: qdos on February 21, 2011, 11:14:23 amDoes anyone have a
set of units that targets a  DLL (dylib) directly?
Perhaps you could zip it down together with a dylib that is
compatible.

I presume the errors i'm getting is either:
1. The dylib is newer than the wrapper unit
2. the wrapper unit is newer than the version of sqlite i have
installed

I really need to get this working, im supposed to present fpc as an
alternative to C# on the mac.


I believe the problem is as I outlined above. OS X 10.4 through 10.6
have the same version of libsqlite3.dylib, I believe.

Thanks.

-Phil

----------------------------------------------------------------------
qdos · 2011-02-21 16:32

How do I uninstall those I have?
And why do these older versions show up as "the latest" when you click
on download from the lazarus pages?

Also one more question about databases:

I got it working, but somehow i couldnt find any table files (although
i created them through code).
I accidentally looked inside the application.bundle for lazarus ...
and there they were (!)

How do i set the output directory?

----------------------------------------------------------------------
Phil · 2011-02-21 17:14

Quote from: qdos on February 21, 2011, 05:32:17 pmHow do I uninstall
those I have?
And why do these older versions show up as "the latest" when you click
on download from the lazarus pages?


You're asking the wrong person.

The 0.9.29 snapshots on the Daily Snapshot's page are the release
candidates for 0.9.30.

Quote from: qdos on February 21, 2011, 05:32:17 pmAlso one more
question about databases:

I got it working, but somehow i couldnt find any table files (although
i created them through code).
I accidentally looked inside the application.bundle for lazarus ...
and there they were (!)

How do i set the output directory?

Not sure what you're asking here, but this also sounds like a bug
that's probably been fixed in 0.9.29.

If you're serious about using Lazarus on Mac for an app that will have
a UI, you'll want to look at this:

http://web.me.com/macpgmr/ObjP/MacXPlatform_Part8.html

Thanks.

-Phil

----------------------------------------------------------------------
qdos · 2011-02-21 18:25

I got it working!
Took some tinkering since i havent used this before, but the below
code works.

Components used on mainform:

    Button1: TButton;
    Datasource1: TDatasource;
    dbcon: TSQLite3Connection;
    DBGrid1: TDBGrid;
    SQLQuery1: TSQLQuery;
    sqtran: TSQLTransaction;

procedure TForm1.Button1Click(Sender: TObject);
var
 x:Integer;
 mTemp:String;
 mRows:Integer;
begin
  (* Setup connection object *)
  try
     dbcon.Directory:=ExtractFilePath(application.ExeName);
     dbcon.DatabaseName:='testing.db3';
     //dbcon.Options:=[];
     dbcon.LoginPrompt:=False;
     dbcon.HostName:='';
     dbcon.KeepConnection:=true;
     dbcon.Connected:=True;
  except
    on e: exception do
    Showmessage('SETUP:' + e.message);
  end;

  (* Establish database. Drop table if already exists *)
  try
    dbcon.ExecuteDirect('DROP TABLE IF EXISTS test;');
    dbcon.ExecuteDirect('CREATE TABLE test (id integer primary key
AUTOINCREMENT, name varchar(128), token integer);');
    dbcon.ExecuteDirect('CREATE INDEX IDX_NAME on test(name,id);');
  except
    on e: exception do
    Showmessage('CREATE:' + e.message);
  end;

  (* Insert some records into the table *)
  try
    try
      mRows:=0;
      for x:=1 to 1000 do
      Begin
        mTemp:='INSERT INTO test (name,token) values("%s",%d);';
        mTemp:=Format(mTemp,['hello #'+IntToStr(x),x]);
        sqlquery1.SQL.Text:=mTemp;
        sqlquery1.ExecSQL;
        mRows:=mRows + sqlquery1.RowsAffected;
      end;
    except
      on e: exception do
      Showmessage('POPULATE:' + e.message);
    end;
  finally
    sqtran.Commit;
  end;

  sqlquery1.Active:=False;
  sqtran.Active:=False;

  (* now connect to our datasource & display data in a grid *)
  sqlquery1.SQL.Clear;
  sqlquery1.SQL.Add('select * from test order by id;');
  sqlquery1.Active:=true;
  datasource1.DataSet:=sqlquery1;
end;