libgda"> GNOME-DB"> Igalia, S.L.'> API"> DBMS"> DSN"> ODBC"> GDA"> LDAP"> CORBA"> IDL"> ORB"> SQL"> RPM"> XML"> PostgreSQL"> MySQL"> Oracle"> Interbase"> Sybase"> MS Access"> Informix"> GdaServerProvider"> GdaDataModel"> GdaDataModelArray"> GdaDataModelHash"> ]> Michael Lausch

michael.lausch@1012surf.net

Rodrigo Moya

rodrigo@gnome-db.org

Vivien Malerba

malerba@gnome-db.org

Sean Allen

zeroone@worldonline.co.za

GDP compliance, FDL, added mark-up, English and syntax Xabier Rodriguez Calvar &igalia;

xrcalvar@igalia.com

How to begin and migration formulae Jose Dapena Paz &igalia;

jdapena@igalia.com

Some examples 1999 February 1999-2003 The GNOME Foundation GNOME Data Access (&GDA;) is an architecture whose purpose is to provide universal access to many different kinds and types of data sources. This goes from traditional relational database systems, to any imaginable kind of data source such as a mail server, a &LDAP; directory... This universality is obtained through the use of an easily extensible plug-in system as the mechanism for communication between the different components in the architecture. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.1 or any later version published by the Free Software Foundation with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license can be found in the appendix. Many of the names used by companies to distinguish their products and services are claimed as trademarks. Where those names appear in any GNOME documentation, and those trademarks are made aware to the members of the GNOME Documentation Project, the names have been printed in caps or initial caps. &ODBC; and &SQL; are established standards. The problem is, that &ODBC; doesn't specify the wire protocol and for some databases no &ODBC; driver exists. You might use RPC, TCP/IP, or shared memory and signals to pass the request from the client to the server. So you have to use the database specific &ODBC; library. This library might not be available for the CPU or operating system on which the client is running. &SQL; itself is also not standardised enough, so that source compatibility can not be assured for all database servers. And for some sort of servers, &SQL; is not even feasible (think about &LDAP;). &GDA; tries to tackle the &ODBC; problem and help you with the &SQL; problem. It's a sort of middleware (or can be blown up to be a middleware layer) to access different data sources. It offers a high level view of data sources and has some places where you can plug in low level access to the database for special tasks. GNOME Data Access (&GDA;) is defined as a set of plug-in interfaces. The level of abstraction provided by &GDA; makes it possible to access any kind of data source, provided that a plug-in implementing those interfaces and accessing this particular data source is written. It offers a wrapper around the database internals, thus making it easier for programmers to make use of all the power provided by many RDBMS without even knowing about it. It comes along with a library, for both clients and servers, as a C implementation of this architecture. This level of abstraction would make possible to, at a later time, change all the internals without having to modify applications using the libraries. Along with these libraries (and associated header files and language bindings for development), &LIBGDA; includes several tools and utilities to help you with the task of developing applications based on &LIBGDA;, as well as for automating some database-related tasks. &LIBGDA; is implemented for UNIX -like operating systems (including Linux), and does not depend on other libraries apart from libxml2 and Glib, which makes it a very lightweight system also ideal for applications to be run on hardware-limited systems. It was once part of the &GNOMEDB; project, and is still used as the basis for it, but it's been separated from it to remove all GNOME dependencies and thus allow non-GNOME applications to be developed based on it. &LIBGDA; is composed of three independent layers. The lower level is covered by the &GDA; providers, which are plug-ins whose task is to map the RDBMS-specific &API; to the &GDA; model. That is, they are objects implementing the &GDA; interfaces. Then, in a middle layer, are the client libraries: an easy-to-use and full featured library which offers access to all the architecture, also including several utility functions to help you on the development of applications based on &GDA;. This library, although targeted to client applications, also includes a set of helper classes and functions to help in the addition of new providers to the &LIBGDA; architecture. Finally, at the upper level sit all the client applications provided in the suite, as well as any application that may make use of the client libraries. On our web site, RedHat and Debian packages are available, so you shouldn't have any problem installing it. For a standard installation, there is are no further steps, but, you'd better know configuration options, just in case you come across a non-standard installation, or in case of problems during a standard installation. Installation depends on which format you choose to download. If you've got a package such as &RPM; or DEB, check your package manager documentation for how to install new packages. If you downloaded the source code (in a tarball), you must compile the software. To do so, once you have unpacked the source tree, you must: $ ./configure $ make $ make install This will generate the makefiles for your specific platform, compile all the source tree, and install the binaries and documentation in your system. If you don't find a file named configure, there should be one called autogen.sh. In this case, run autogen.sh, which will create and run the generated configure file. You can specify several arguments to configure (or autogen.sh). The most significant are (you can check all the available arguments by running configure --help): --prefix=<directory>: Prefix where package will be installed --with-mysql=<directory>: Specify directory where &MYSQL; libraries are installed --with-postgres=<directory>: Specify directory where &PGSQL; libraries are installed --with-sybase=<directory>: Specify directory where &SYBASE; libraries are installed --with-ldap=<directory>: Specify directory where &LDAP; libraries are installed --with-oracle=<directory>: Specify directory where &ORAC; libraries are installed --with-interbase=<directory>: Specify directory where &INTERB; libraries are installed --with-mdb=<directory>: Specify directory where the MDB libraries (for accessing &MSACC; files) are installed --with-sqlite=<directory>: Specify directory where the SQLite libraries are installed Libraries needed by providers are searched by default, so, if found, providers are compiled by default. Those libraries are searched in hard-coded directories, so if the installation of these libraries is in an uncommon place, the above arguments should be used to specify the directories where the libraries and header files are installed. If you find any problem during the configuration, compilation or installation process, do not hesitate in contacting the &GNOMEDB; mailing list (gnome-db-list@gnome.org, first send an email to gnome-db-list-request@gnome.org with the subject SUBSCRIBE, if you are not already subscribed). If you are using Debian you will need to install a few packages in the following way: $ apt-get install package-names These are the packages you will need for this: libgda2-1 libgda2-common libgda2-dev libgda2-doc libgda2-dbg libgda2-ruby (Ruby bindings) Alternatively, you can install the different providers that allow access to the different supported data sources. The packages you can choose from are: gda2-postgres for PostgreSQL databases gda2-mysql for MySQL databases gda2-odbc for ODBC data sources gda2-freetds for Sybase and MS SQL Server databases gda2-sqlite for SQLite databases You can obtain information about any packages with: $ apt-cache show package-names If you cannot find this packages, you must include in /etc/apt/sources.list a line like this: deb http://gluck.debian.org/~kov/debian woody gnome2 To compile you will need to set the C flags and to link the library, so we recommend to use the pkg-config command. $ gcc -c full_example.c `pkg-config --cflags libgda` $ gcc -o full_example `pkg-config --libs libgda` full_example.o Doing this will add to the C compiler command line all needed header files and library directories as well as all needed libraries for linking your GDA application. To include header files, you can just use libgda/libgda.h, which will include all GDA header files. Depending on the use you're going to get out of &LIBGDA;, you may have to dig deep into its internals, but don't be afraid, things have been implemented to be easy to use. If you want to develop applications using &LIBGDA;, you should install the libgda-dev[el] package if you do a &RPM; or Debian-based installation. If you compiled the source code, development files are installed in your system. The only step you need to do to make sure everything is well installed, is to check that &LIBGDA; libraries and binaries are seen by your system. That is, make sure that the &LIBGDA; bin/ directory is in your PATH environment variable, as well as the lib/ in your LD_LIBRARY_PATH (or /etc/ld.so.conf file). You have to include a headers file, and it is: #include <libgda/libgda.h> If you want to access a data source through a &GDA; provider, you must first of all have access to this provider, and most importantly, this provider should have access to its specific data source. So, first have your database up and running. For this, you'll have to check your specific data source documentation, or see the &LIBGDA; providers' specific documentation. Once you've got your &GDA; provider installed, whether on your machine or on another one on the network, you must configure your local system to have access to it. If you're on a local installation, once you have installed the &GDA; provider (by compiling it or by installing its &RPM; or Debian package), the provider is visible in your machine. This is because the provider installs itself in a well known location that makes &LIBGDA; itself know about the new provider. Then, the next step is to configure the data sources you want available on your system. For doing this, you should, as for now, use &GNOMEDB;, which is a front-end to &LIBGDA; for the GNOME project. It would be a good idea to add a command-line tool for managing the configuration, as now, using GConf, is not a matter of hacking on a config text file, as it was before with gnome_config. The &API; for doing so is already available in the libgda-commonlibrary, so it would be really easy. Volunteers? Command-line tools will be provided in &LIBGDA; for doing so in a not-too-distant future, so you may want to know what information you need to setup a data source. One of the problem &GDA; solves is the naming of data sources. Every database system has it's own way of defining names for it's databases. For example &MYSQL; uses the hostname, port number, and the name of the database. Other databases, like Solid use the hostname and port number only. There is no support for multiple databases per server. Because the client does not need all these details, the &LIBGDA; configuration defines all the properties of such a data source, so that the correct data base server can be contacted. This information is accessed by the client library and sent to the provider, which in turn will parse the string to decide which database must be connected to. The data stored for each data source is as follows: [sales] Provider=MySQL DSN=DATABASE=test;HOST=localhost;PORT=1111 Description=MySQL Test Database in native mode Username=username Password=password The provider for this database is the gda-mysql provider. The value of this entry is used as the object ID for the plug-in activation. This is the most important entry. The value of this entry is the string sent to the provider so that it knows which datasource to access. How this entry is interpreted by the providers is described in the provider section. There are, though, a set of default properties that can be used for the connection string for all providers. Those are: USERNAME: user name to be used for authentication. PASSWORD: password to be used for authentication. The value of this entry is a short description of the datasource. It is here for convenience only and it is not used for any purpose. The user name to be used when connecting to the database. The password to be used when connecting to the database. This is stored in plain text, so be sure you restrict access to the configuration file (~/.libgda/config) to any "dangerous" users. The &XML; configuration file (~/.libgda/config) is not recommended to be modified by hand and, for our example, it is something like this: <?xml version="1.0"?> <libgda-config> <section path="/apps/libgda/Datasources/sales"> <entry name="DSN" type="string" value="PORT=1111;DATABASE=test;HOST=localhost"/> <entry name="Description" type="string" value="MySQL Test Database in native mode"/> <entry name="Password" type="string" value="password"/> <entry name="Provider" type="string" value="MySQL"/> <entry name="Username" type="string" value="username"/> </section> </libgda-config> To create a data source you must use the function gda-config-save-data-source () Here you see how to create a data source named foo_ds. If you do not need to give an username or password to enter the database, you could put NULL. gda_config_save_data_source ("foo_ds", "PostgreSQL", "DATABASE=foo_db", "description of foo_ds", "foo_username", "foo_password"); gda_config_save_data_source ("other_foo_ds", "MySQL", "DATABASE=other_foo_db,HOST=db.foo.com", "description of other_foo_ds", "foo", NULL); For more details about provider specific information see in the section about providers specific information. There was a little bugIt can be fixed, but it is better not to run the risk of it., and it is that gda_config_save_data_source() does not create the configuration directory ~/.libgda, so you have to do it. There is no problem about calling several times to this function because if you save an existing data source, it is replaced. To remove a data source you must use the function gda-config-remove-data-source () Here you see how to remove a data source named foo_ds. gda_config_remove_data_source("foo_ds"); To list available data sources you must use the function gda_config_get_data_source_list () Here you see a function which lists the available data sources. void list_datasources (void) { GList *ds_list; GList *node; GdaDataSourceInfo *info; ds_list = gda_config_get_data_source_list (); g_print ("\n"); for (node = g_list_first (ds_list); node != NULL; node = g_list_next (node)) { info = (GdaDataSourceInfo *) node->data; g_print ("NAME: %s PROVIDER: %s CNC: %s DESC: %s USER: %s PASSWORD: %s\n", info->name, info->provider, info->cnc_string, info->description, info->username, info->password); } g_print ("\n"); gda_config_free_data_source_list (ds_list); } Our function. Note that you must free the list when you finish using it. To list the available providers you must use the function gda_config_get_provider_list () Here you see a function which lists available providers. void list_providers (void) { GList *prov_list; GList *node; GdaProviderInfo *info; prov_list = gda_config_get_provider_list (); for (node = g_list_first (prov_list); node != NULL; node = g_list_next (node)) { info = (GdaProviderInfo *) node->data; g_print ("ID: %s\n", info->id); } gda_config_free_provider_list (prov_list); } Our function. Note that you must free the list when you finish using it. This section provides information specific to each of the available &LIBGDA; providers. The &GDA; default provider is always installed with &LIBGDA;, which means that you've got always a default database system available for you. To connect to a default provider's database, you only need to specify, in the &DSN; string, a string of the form "URI=/path/to/the/database/file". When you first connect to the new data source, the &GDA; default provider will create the database, in the path you specified in the &DSN; string, if it does not exist. This default provider uses XML as the format for that file. The &ODBC; provider is a special case, since &ODBC; is itself a data access layer, the same as &LIBGDA;, So, in the case of the &GDA; &ODBC; provider, the &DSN; string is completely up to the &ODBC; driver manager. That is, the &GDA; &ODBC; provider does not parse it all, nor does it try to understand what it means; it simply passes it over to the &ODBC; library. So, if you want to use &LIBGDA; with &ODBC;, you should first know how to set up an &ODBC; data source, and then just specify the &DSN; string you would pass to the &ODBC; library in the &DSN; string of the &GDA; data sources. There is a project called unixODBC, which provides some graphical tools to help you in setting up &ODBC; data sources. You may find it interesting to give it a try. To use the &GDA; &PGSQL; provider, you'll need the gda-postgres package. The &PGSQL; provider accepts the following arguments in the &GDA; data source's &DSN; string: HOST: name of the host where the database server is running. If it begins with a slash then the protocol used is Unix-domain instead of TCP/IP and its value is the name of the directory where the file is stored. By default: /tmp. HOSTADDR: IP of the host where the database server is running (avoids DNS lookup). If this option has a value, TCP/IP communications is used. If neither a host name or host address is specified, the connection will be established using a local Unix domain socket. PORT: Port number or socket filename extension for Unix-domain connections. DATABASE: name of the database you want to access. This one is called 'dbname' in the &PGSQL; documentation. USER: user name to connect as. PASSWORD: password if the connection requires it. OPTIONS: trace/debug options to be sent to the server. TTY: a file or tty for optional debug output from the back-end. REQUIRESSL: set to '1' to force SSL connection to the back-end. Set to '0' to negotiate with server. The description of the parameters is almost copied from the &PGSQL; documentation. Refer to it for details on how some environment variables can also set these parameters. To configure a data source to access a &MYSQL; database, you'll need to install the &GDA; &MYSQL; provider (package gda-mysql). It accepts the following arguments in the &DSN; string: HOST: name of the host where the database server is running. PORT: port number for connecting to the database server. UNIX_SOCKET: socket file for connecting to the database server (should not be defined with HOST and/or PORT). DATABASE: name of the database you want to access. USER: employ the specified user name for logging in to the server. PASSWORD: employ the specified password when connecting to the database server. USE_SSL: will use SSL (encrypted protocol) if specified and set to 1. To use the &GDA; &SYBASE; provider, you'll need the gda-sybase package. The &SYBASE; provider accepts the following arguments in the &GDA; data source's &DSN; string: HOST: name of the host where the database server is running. USERNAME: user name to connect as. APPNAME: PASSWORD: password if the connection requires it. DATABASE: First of all you have to initialise the gda library, i.e. to call the gda_init () function. gda_init ("TestGDA", "0.1", argc, argv); After initialising you can work as usual or make a function with the whole stuff, calling gda_main_run(). Note that if you use this way you will need to call gda_main_quit() in order to finish the program. void do_stuff () { GdaClient *client; GdaConnection *connection; list_providers (); list_datasources (); client = gda_client_new (); g_print ("CONNECTING\n"); connection = gda_client_open_connection (client, "calvaris", NULL, NULL, GDA_CONNECTION_OPTIONS_READ_ONLY); g_print ("CONNECTED\n"); execute_some_queries (connection); g_print ("ERRORS PROVED!\n"); process_accounts(connection); gda_client_close_all_connections (client); g_object_unref(G_OBJECT(client)); play_with_parameters(); gda_main_quit(); } int main (int argc, char **argv) { g_print ("STARTING\n"); gda_init ("TestGDA", "0.1", argc, argv); save_ds (); gda_main_run ((GdaInitFunc) do_stuff, (gpointer) NULL); /* do_stuff(); */ g_print("ENDING\n"); } To connect you need to use two functions. We use gda_client_new () to create a connection pool and use gda_client_open_connection () to create the specific connections to the different data sources. void do_stuff () { GdaClient *client; GdaConnection *connection; list_providers (); list_datasources (); client = gda_client_new (); g_print ("CONNECTING\n"); connection = gda_client_open_connection (client, "calvaris", NULL, NULL, GDA_CONNECTION_OPTIONS_READ_ONLY); g_print ("CONNECTED\n"); execute_some_queries (connection); g_print ("ERRORS PROVED!\n"); process_accounts(connection); gda_client_close_all_connections (client); g_object_unref(G_OBJECT(client)); play_with_parameters(); gda_main_quit(); } Creates the connection pool. Creates the connection to calvaris data source with the default username and password you have specify when creating the data source. However, you can specify them if you want. It's a good practice to close connections when you finish using them. It doesn't mean that you must close them each time you use them. It's better to open the connections when program starts and use them during the whole execution. This function frees the GdaConnection objects attached to the GdaClient. When finishing with the client, I have to free it. Before invoking a query you have to build the structure containing the command and you can do this with gda_command_new (). The command type we most commonly use is GDA_COMMAND_TYPE_SQL because we will only focus on &SQL; queriesThere are other command types, as &XML; and so on. typedef enum { GDA_COMMAND_OPTION_IGNORE_ERRORS = 1, GDA_COMMAND_OPTION_STOP_ON_ERRORS = 1 << 1, GDA_COMMAND_OPTION_BAD_OPTION = 1 << 2 } GdaCommandOptions; Ignores all errors and executes all sentences returning data models. For failed sentences, it returns an empty data model. Stops when finding and error and doesn't return data models. Here you see an example of creating a command: gint execute_sql_non_query (GdaConnection *connection, const gchar * buffer) { GdaCommand *command; gint number; command = gda_command_new (buffer, GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS); number = gda_connection_execute_non_query (connection, command, NULL); gda_command_free (command); return (number); } Our function. You can give it several comma-separated sentences. We will see it later. It is a good practice to free the commands. Non queries are queries that does not return data, only the number of rows affected, as a DELETE or an UPDATE. We use gda_connection_execute_non_query() gint execute_sql_non_query (GdaConnection *connection, const gchar * buffer) { GdaCommand *command; gint number; command = gda_command_new (buffer, GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS); number = gda_connection_execute_non_query (connection, command, NULL); gda_command_free (command); return (number); } Normal queries are queries that return data (data models). You have two ways to do this: gda_data_model_execute_single_command() gda_data_model_execute_command() You can use the first way when you want to invoke only a single command. Second way is used to execute several comma-separated sentences. It is recommended to use gda_connection_execute_single_command (). Here you see an example: gboolean execute_sql_command (GdaConnection *connection, const gchar * buffer) { GdaCommand *command; GList *list; GList *node; gboolean errors=FALSE; GdaDataModel *dm; command = gda_command_new (buffer, GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS); list = gda_connection_execute_command (connection, command, NULL); if (list!=NULL) for (node=g_list_first(list); node != NULL; node=g_list_next(node)) { dm=(GdaDataModel *) node->data; if (dm == NULL) { errors=TRUE; } else { show_table (dm); g_object_unref(dm); } } else { errors=TRUE; } gda_command_free (command); return (errors); } Executes the query and obtains a list of data models Loop for moving through the list of data models. If you use gda_connection_execute_single_command(), you should not need to use a loop, because this function would return a data model. Each time we execute a normal query, we will obtain a GdaDataModel object, which is the way to see what the query returned. As GdaDataModel is an object, we can manage it with GdaDataModel class. Before continuing, we must say that it is possible to modify a data model, but as we are accessing using &SQL;, it is not recommended to modify it, so modifications on the database must be done using &SQL;. Let's see the functions we need: gda_data_model_get_n_rows() gda_data_model_get_n_columns() gda_data_model_describe_column() gda_data_model_get_column_title() gda_data_model_get_column_position() gda_data_model_get_row() gda_data_model_get_value_at() gda_row_get_value() This functions are very easy to use, so let's see some clear examples: This function accesses the data model by directly accessing cells (using gda_data_model_get_value_at () ) void show_table (GdaDataModel * dm) { gint row_id; gint column_id; GdaValue *value; for (column_id = 0; column_id < gda_data_model_get_n_columns (dm); column_id++) g_print("%s\t",gda_data_model_get_column_title (dm, column_id)); g_print("\n"); for (row_id = 0; row_id < gda_data_model_get_n_rows (dm); row_id++) { for (column_id = 0; column_id < gda_data_model_get_n_columns (dm); column_id++) { char *str; value = gda_data_model_get_value_at (dm, column_id, row_id); str = gda_value_stringify (value); g_print ("%s\t", str); } g_print("\n"); } } Loop for writing column names. Double loop accessing values using gda_data_model_get_value_at () Data returned is a GdaValue object. This function accesses the data model by accessing rows (using gda_data_model_get_row () and gda_row_get_value () ) void show_table2 (GdaDataModel * dm) { gint row_id; gint column_id; GdaValue *value; GdaRow *row; for (column_id = 0; column_id < gda_data_model_get_n_columns (dm); column_id++) g_print("%s\t",gda_data_model_get_column_title (dm, column_id)); g_print("\n"); for (row_id = 0; row_id < gda_data_model_get_n_rows (dm); row_id++) { row = (GdaRow *) gda_data_model_get_row (dm, row_id); for (column_id = 0; column_id < gda_data_model_get_n_columns (dm); column_id++) { value = gda_row_get_value (row, column_id); string=gda_value_stringify (value); g_print ("%s\t", string); g_free(string); } g_print ("\n"); } } Loop for writing column names. Outer loop obtaining rows using gda_data_model_get_row () Inner loop obtaining the value using gda_row_get_value () . Notice that gda_row_get_value () returns a const GdaValue, so we do not have to free it. When you finish using data models you must free it, but GdaDataModel class does not have a function to do it, so you have to use g_object_unref (). Values returned by functions of managing data are GdaValue objects. GdaValue class has many functions to access data, so we show the most important ones of them: gda_value_free() gda_value_is_null() gda_value_copy() gda_value_compare() gda_value_stringify() There are many functions to know what is the type of a value and to manage values, that can be seen in GdaValue class. We will return to the examples about last section to notice some important details: void show_table2 (GdaDataModel * dm) { gint row_id; gint column_id; GdaValue *value; GdaRow *row; gchar *string; for (column_id = 0; column_id < gda_data_model_get_n_columns (dm); column_id++) g_print("%s\t",gda_data_model_get_column_title (dm, column_id)); g_print("\n"); for (row_id = 0; row_id < gda_data_model_get_n_rows (dm); row_id++) { row = (GdaRow *) gda_data_model_get_row (dm, row_id); for (column_id = 0; column_id < gda_data_model_get_n_columns (dm); column_id++) { value = gda_row_get_value (row, column_id); string=gda_value_stringify (value); g_print ("%s\t", string); g_free(string); } g_print ("\n"); } } Loop for writing column names. Outer loop obtaining rows using gda_data_model_get_row () Inner loop obtaining the value using gda_row_get_value () . Notice that gda_row_get_value () doesn't return a const GdaValue, so we have to free it. We have the difference here. As you can see above, gda_value_stringify () does not return a const gchar *, so you have to free it. First way is quite attractive but it is not good. The special functions we need to do this are defined in the GdaTransaction, GdaConnection and GdaCommand classes, and they are: gda_transaction_new () gda_connection_begin_transaction () gda_connection_commit_transaction () gda_connection_rollback_transaction () gda_command_set_transaction () Things you have to do to manage transactions are: Create transaction Change, if needed, the isolation level Link transaction to a connection For each command you want to execute: Create command Link transaction to command Execute command Free command Commit or rollback transaction Free transaction Here you can see an example: void process_accounts(GdaConnection *connection) { GdaTransaction *transaction_one, *transaction_two; GdaCommand *command; transaction_one=gda_transaction_new("accounts1"); gda_transaction_set_isolation_level(transaction_one, GDA_TRANSACTION_ISOLATION_SERIALIZABLE); gda_connection_begin_transaction(connection,transaction_one); command=gda_command_new ( "UPDATE accounts SET balance=balance+50" "WHERE account_code=456", GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS); gda_command_set_transaction(command,transaction_one); gda_connection_execute_non_query(connection,command,NULL); gda_command_free(command); command=gda_command_new ( "UPDATE accounts SET balance=balance-50" "WHERE account_code=12", GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS); gda_command_set_transaction(command,transaction_one); gda_connection_execute_non_query(connection,command,NULL); gda_command_free(command); gda_connection_commit_transaction(connection,transaction_one); g_object_unref(transaction_one); transaction_two=gda_transaction_new("accounts2"); gda_transaction_set_isolation_level(transaction_two, GDA_TRANSACTION_ISOLATION_SERIALIZABLE); gda_connection_begin_transaction(connection,transaction_two); command=gda_command_new ( "UPDATE accounts SET balance=balance+400" "WHERE account_code=456", GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS); gda_command_set_transaction(command,transaction_two); gda_connection_execute_non_query(connection,command,NULL); gda_command_free(command); command=gda_command_new ( "UPDATE accounts SET balance=balance-400" "WHERE account_code=12", GDA_COMMAND_TYPE_SQL, GDA_COMMAND_OPTION_STOP_ON_ERRORS); gda_command_set_transaction(command,transaction_two); gda_connection_execute_non_query(connection,command,NULL); gda_command_free(command); gda_connection_rollback_transaction(connection,transaction_two); g_object_unref(transaction_one); execute_sql_command(connection,"SELECT * FROM accounts"); } Creates first transaction. Changes the isolation level. Links it to connection. Links command to transaction. Makes commit on transaction. Frees transaction. Makes rollback on second transaction. You can manage errors with GdaError class and obtain them with function gda_connection_get_errors() so let's see them and an example: Here you see the functions to manage errors: gda_error_get_description() gda_error_get_number() gda_error_get_source() gda_error_get_sqlstate() Here you can see an example of using this: gboolean get_errors (GdaConnection *connection) { GList *list; GList *node; GdaError *error; list = (GList *) gda_connection_get_errors (connection); for (node = g_list_first (list); node != NULL; node = g_list_next (node)) { error = (GdaError *) node->data; g_print ("Error no: %d\t", gda_error_get_number (error)); g_print ("desc: %s\t", gda_error_get_description (error)); g_print ("source: %s\t", gda_error_get_source (error)); g_print ("sqlstate: %s\n", gda_error_get_sqlstate (error)); } } Obtains errors list. Loop for getting error information. To compile you do not need to link with many libraries and configure directories of headers files, only capture the output of pkg-config as follows: $ gcc -o main `pkg-config --cflags --libs libgda` main.c Further more, you only need to include one headers file and it is: #include <libgda/libgda.h> As in the old version, you need to call gda_init() to initialise the library: gda_init ("TestGDA", "0.1", argc, argv); If you before created connections directly to data sources, you will now use a connections pool and will be necessary to create the data source because you create connection using the data source identifier. You have two ways to do this, one of them is creating them using some utility of &GNOMEDB; or using API functions. Remember the little bug. There is no problem about calling several times to this function because if you save an existing data source, it is replaced, so it could be advisableBut you must think of security if you distribute the source code because people would see the passwords of your databases. to save the data source each time you want to create the connection. You can see how to create a connection easily in a chapter above. It can be made, more or less, as in the old version, using gda_command_new(), but now, this function needs a few parameters and, in this version, you do not link a command to a connection, so you execute a command in a connection as we'll see later. You can see how to create commands and examples about this here. Non queries are queries that do not return data, as insertions, deletions, and so on. The function we use is gda_connection_execute_non_query () and returns the number of affected tuples or -1 in case of error. Here you see an example. It is better not to execute more than one &SQL; sentence for each command because the result can be unexpected. A normal query is a query that return data. This is made as a data model, analogous to GdaRecordset in the old versionNow you have a GdaRecordset class, but it is not recommended.. As you can see in the following example, the function we use to obtain data from a &SQL; sentence is gda_connection_execute_single_command () and needs the parameters of non queries. The difference is that now the function returns the data model or NULL in case of error. As in the case of non queries, you must not use several semicolon-separated sentences, because you have a special function to do thisgda_connection_execute_command (), but it is not recommended. As we have said before, data is obtained as data models. We can consider it as a representation of the table. We can access the table at row level or table level. We will focus on row level because it is the most similar to the old version. As you can see in the example, the access is made with the C style using a for to obtain data from rows and columns. We talk about table access only saying that the access is made in a very similar way. The only difference is that gda_data_model_get_value_at () returns a const and we have not to free it. As you can see viewing GdaDataModel class, it has not a free method, so we have to free it using g_object_unref. Theoretically, you could modify data models and dump changes over the database, but it is not recommended because you might make changes using &SQL;, so we consider data models not to be modifiable. There is not exist a function to access columns directly using the column name, but you can obtain its index using gda_data_model_get_column_position (), as you can see in this example: value=gda_row_get_value(row, gda_data_model_get_column_position(data_model,"id_product")); There is a function very similar to the last version and you can see a very clear example in the Managing errors section. In the old version, to manage transactions was more simple but less powerful. In the new version it is supposed that we can launch several transactions over the same connection but some database drivers, as &PGSQL;, do not implement it, so we do not recommend it. If you want to launch several transactions, you must open several connections, but you can do it over the same pool. You can see a clear example in the Transactions section. The following sections describe the API available for &GDA; applications. &libgda-init; &libgda-blob; &libgda-client; &libgda-command; &libgda-config; &libgda-connection; &libgda-data-model-array; &libgda-data-model-hash; &libgda-data-model-list; &libgda-data-model; &libgda-error; &libgda-export; &libgda-field; &libgda-log; &libgda-parameter; &libgda-quark-list; &libgda-row; &libgda-select; &libgda-table; &libgda-transaction; &libgda-util; &libgda-value; &libgda-xml-connection; &libgda-xml-database; &libgda-xml-document; For each different data source supported by &LIBGDA; there must be a GObject that implements the virtual methods defined in &GDASERVERPROVIDER; class. Providers must also create their own object to implement the virtual methods defined in &GDADATAMODEL; or one of its subclasses (currently &GDADATAMODELARRAY; and &GDADATAMODELHASH;) to support recordsets. Since &LIBGDA; itself is developed in the C language, and that most providers are also implemented in that language, the &LIBGDA; library itself contains a set of helper classes and functions to guide you in the addition of a new provider to the GDA framework. The &GDASERVERPROVIDER; is the class you should implement for adding a new provider. This class is just a typical GObject-based class, with a set of virtual methods, that are the ones that you must implement. These virtual methods are declared in the class structure, in the gda-server-provider.h file. They are explained in detail in the following subsections. Sets up the connection to the database back-end using the parameters received as arguments and returns a boolean TRUE if the connection is successfully established, otherwise FALSE. Frees the resources allocated for the connection and returns TRUE if everything is ok Returns the name of the currently open database for a given connection. Change the database being used in the active connection. Creates a new database whose name is received in a parameter. Drops an existing database whose name is received in a parameter. Executes a command and returns a GList of &GDADATAMODEL; with the results. Initiates a transaction if the DB back-end supports transactions. Commits a transaction if the DB back-end supports transactions. Rollback a transaction if the DB back-end supports transactions. Tests if a given feature is supported by the provider and the DB back-end. You can view the list of features in gda-connection.h, enumeration GdaConnectionFeature. Returns a &GDADATAMODEL; with the schema information requested. You can view the list of features in gda-connection.h, enumeration GdaConnectionSchema. This schema information is what describes the objects in the database, and is the way applications can manage the structure of several database systems without knowing anything about their specific way of retrieving this information. The get_schema virtual method gets a GdaConnectionSchema argument, which describes the kind of schema the client making the call is interested in, along with a GdaParameterList argument, which contains the list of arguments sent by the user to explicitly provide more information for the search. The information to be returned for each schema, along with the supported parameters, is described in the following table. Object type GDA identifier Supported Parameters Returned fields Databases GDA_CONNECTION_SCHEMA_DATABASES name Schemas (also namespaces), a feature supported by Postgres which allows to define multiple domains within a single database GDA_CONNECTION_SCHEMA_NAMESPACES name Tables GDA_CONNECTION_SCHEMA_TABLES "name" (name of table, optional) name, owner, comments, SQL definition Tables' parents GDA_CONNECTION_SCHEMA_PARENT_TABLES "name" (name of table, optional) name, order of inheritance Views GDA_CONNECTION_SCHEMA_VIEWS "name" (name of view, optional) name, owner, comments, SQL definition Table (or view) fields GDA_CONNECTION_SCHEMA_FIELDS "name" (name of table (or view), required) name, type, size, scale, not null?, primary key?, unique index?, references, default value Sequences GDA_CONNECTION_SCHEMA_SEQUENCES "name" (name of sequence, optional) name, owner, comments, SQL definition Procedures GDA_CONNECTION_SCHEMA_PROCEDURES "name" (name of procedure, optional) name, unique id, owner, comments, return type, number of arguments, arguments types (arguments types in the order, separated by spaces) and SQL definition. For the arguments where any type is accepted, '-' is returned as type. Aggregates GDA_CONNECTION_SCHEMA_AGGREGATES "name" (name of aggregate, optional) name, unique id, owner, comments, return type, argument type, SQL definition. If the argument can be of any type, then '-' is returned as type. Types GDA_CONNECTION_SCHEMA_TYPES "name" (name of type, optional) name, owner, comments, GDA type You have to extend one of &GDADATAMODEL;, &GDADATAMODELARRAY; or &GDADATAMODELHASH;. The easiest to extend are the last two. They both derive from &GDADATAMODEL;. Here is the list of methods that you have to override. If you use &GDADATAMODELHASH;, you don't need to override append_row. get_n_rows Returns the number of rows in the data model. If the provider can know in advance the number of rows the database server has returned, this function should just return that, and not retrieve any data. On the other hand, if it can't it should either retrieve all data, or move to the last record in the recordset and retrieve the row number, if the underlying data source allows it. get_n_columns Returns the number of columns in the data model. describe_column Returns information about a given column, in the form of a GdaFieldAttributes. get_row Retrieves a row from the data model. This function is very important for the implementation of editable data models. What this function returns is a GdaRow, which providers should uniquely identify (via gda_row_set_id). This is needed so that later on, client applications can use the same GdaRow returned by this method in the update_row and remove_row methods. get_value_at Returns the value stored in a given cell of the data model. is_editable Checks whether the data model can be modified or not. If the provider supports the edition of data models, it should return TRUE in this function. If it doesn't (for instance, because it can't uniquely identify rows in the data model), it should return FALSE. Before a data model can be edited, client applications must call the gda_data_model_begin_edit function, which emits the "begin_edit" signal on the GdaDataModel class. So, providers should connect to this signal to be informed when the data model starts being editing. In the callback connected to that signal, it should start a transaction, for instance. In a similar way, there are 2 other signals that provider's data model implementations should pay attention to. Those are "end_edit" and "cancel_edit". In "end_edit", if all went ok, providers should COMMIT the transaction started in "begin_edit". In "cancel_edit", a ROLLBACK should be made. append_row Appends a row to the data model. Usually, this means, in the provider, executing an INSERT SQL command on the table being read by the data model. update_row Updates an existing row in the data model. The row should have been uniquely identified in the provider code, as explained for the get_row method. remove_row Removes a row from the data model. Usually, this means, in the provider, executing a DELETE SQL command on the table being read by the data model. The row should have been uniquely identified in the provider code, as explained for the get_row method. &libgda-server-provider; &libgda-xql-atom; &libgda-xql-bin; &libgda-xql-column; &libgda-xql-const; &libgda-xql-delete; &libgda-xql-dml; &libgda-xql-dual; &libgda-xql-field; &libgda-xql-func; &libgda-xql-insert; &libgda-xql-item; &libgda-xql-join; &libgda-xql-list; &libgda-xql-query; &libgda-xql-select; &libgda-xql-stack; &libgda-xql-target; &libgda-xql-update; &libgda-xql-utils; &libgda-xql-value; &libgda-xql-valueref; libgda includes a built-in reporting engine which allows applications to easily create, manage and run custom reports. &libgda-report-types; &libgda-report-document; &libgda-report-item; &libgda-report-item-detail; &libgda-report-item-label; &libgda-report-item-pagefooter; &libgda-report-item-pageheader; &libgda-report-item-repfield; &libgda-report-item-report; &libgda-report-item-reportfooter; &libgda-report-item-reportheader; &libgda-report-item-sqlquery; &libgda-report-result; &libgda-report-valid; &fdl-appendix;