[Gambas-user] Database Results

[Caveat]

Hi Tobias

Fabien is correct in saying it's a kind of library.

For Gambas2 in my Ubuntu installation, the following libraries are
automatically installed along with Gambas2:

gambas2-gb-db
gambas2-gb-db-postgresql
gambas2-gb-db-firebird
gambas2-gb-db-mysql
gambas2-gb-db-form
gambas2-gb-db-odbc
gambas2-gb-db-sqlite

gb-db is the common library for database access
gb-db-postgresql, ...firebird, ...mysql, ...odbc, ...sqlite libraries
are the specific 'drivers'
gb-db-form handles Gambas database-bound controls

The driver normally handles the specifics of talking to the particular
database so that your code all looks the same, regardless of which
database you're talking to.  So whether it's for mysql, sqlite,
postgre...you just code:

Dim myConnection as NEW Connection
...
myConnection.Open
...
myConnection.Exec("select * from candidates")

The properties you set on the Connection, and specifically the Type
determine which database in particular you will be talking to.

My connect function looks something like:

PUBLIC FUNCTION connect2db() AS Boolean
  WITH myConnection
    .Type = "mysql"
    .Host = "192.168.2.188"
    .Port = 3306
    .Login = "dbuser"
    .Password = "db25$4klln"
    .Name = "recruiting"
  END WITH
  TRY myConnection.Open
  IF ERROR THEN
    Message("Cannot Open recruiting database:" & Error.Text)
    RETURN FALSE
  END IF
  RETURN TRUE
END

Under the covers, the specific driver for mysql may convert the
myConnection.Open into (completely made-up fictitious nonsense code
follows):

MySQLConnectionObject mysqlConn = CALL MySQL.openDatabase(Login:
{name:password}, Host: {host}, Port: {port})

and for postgresql it may look nothing at all like:

postgresql.connection = CALL postgresql.openDB(ConnectURL:
{name:password at ...2480...:port})

The only thing you need to be aware of (and only if you plan on making
your code portable from one db to another...actually not a bad idea) is
the small differences in SQL syntax that the different databases support
and the different ways in which different databases may interpret your
SQL.

For example for HSQLDB (this is a beautiful thing, written in java,
6.5Mb download for a complete db with graphical client!) you might say:

select concat(count(distinct name), '->B5') as ForCell from candidates
where upper(canReadAndWrite) = 'YES'

but for SQL Server (sorry, I'm forced to use 'doze for my work!) you
have to say:

select str(count (distinct name)) + '->B5' as ForCell from candidates
where upper(canReadAndWrite) = 'YES'

I've also been surprised by the same query returning different results
in different databases.  ISTR something like SQL Server being
case-insensitive by default, and HSQLDB not... so running the same
"select count(distinct name) from candidates" query resulted in
different counts!

Hope this helps, and that you get this this time around (I put you on cc
so maybe you get 2 copies for the price of one) :-)

Regards,
Caveat


On Sun, 2010-09-12 at 21:33 +0200, tobias wrote:
> hi,
> 
> > It's a kind of lbrary ... you select gb.db in thecomponents.
> > 
> > 
> > then
> > 
> > private hcon as new connection
> > 
> > 
> > Publc sub Main()
> > 
> >   hCon.type = "sqlite3"
> >   hCon.Host=user.home &/ "dbpath"
> >   hcon.Name = "mydatabase"
> >   hcon.open
> > 
> > end
> > 
> 
> yeah, so this 'kind of library' it loaded by the gambas component (?) 
> loader and there are the several sqlite3 functionalities for my program?
> 
> regards,
> tobi
> 
> ------------------------------------------------------------------------------
> Start uncovering the many advantages of virtual appliances
> and start using them to simplify application deployment and
> accelerate your shift to cloud computing
> http://p.sf.net/sfu/novell-sfdev2dev
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user