User Tools

Site Tools


sql

SQL

This is an overview about the possibility to use SQL databases.

The Qt documentation in C++ on SQL can be read here: http://doc.trolltech.com/4.3/qtsql.html

You do not need to have a running database server: If you have selected “Embedded (SQLite)” in the database connection dialog, KBasic will use a built-in SQLite database, no setup is needed for this database.

The following databases are (theoretically) supported:

Recommended:

  • QPSQL = PostgreSQL Driver
  • QSQLITE = SQLite Driver
  • QMYSQL = MySQL Driver (supported on Mac as well, contact us)

Available on all platforms:

  • QODBC = ODBC Driver (includes Microsoft SQL Server)

Supported at least through ODBC: IBM DB2, Borland InterBase Driver, Oracle Call Interface Driver, Sybase Adaptive Server

If you need native access to the databases IBM DB2, Borland InterBase Driver, Oracle Call Interface Driver, Sybase Adaptive Server, please contact us. We are happy to provide you with the requested database driver.

If the database driver is available, KBasic will show it in the database connection dialog. Which database driver is present, depends on the platform you are using, e.g. QSQLITE and QPSQL are currently available on all platforms.

Note: You can also use other databases, like MS Access or MS SQL Server, by using the ODBC interface driver.

Currently, if you would like to use other databases not listed here, you'll have to use the QODBC driver. The use of PostgreSQL is recommended, because it is free and available on all platforms. It is used as the test database of the KBasic developer, just like SQLite.

Important information about Qt support for databases can be read here: http://doc.trolltech.com/4.5/sql-driver.html

Tested databases

Although Qt supports nearly every database, I would like to test each database.

So far SQLite, MySQL and PostgreSQL have been tested and work as expected.

How much SQL do I need to know to use it?

Not much knowledge about SQL is needed, because KBasic automatically does all the hard parts for you. For example, it can manage the records of a database table and can provide common functionality for your applications – like editing, deleting, and adding new data. Nevertheless, it is still possible to use SQL commands (just like in many other programming languages) to gain low-level access to your database.

The process of creating and using your own custom database is described by the following steps:

  • First you decide, which tables you would like to use in your database. Therefore, you use the database administration tool, which comes with your database and create a new database, user password and other configurations you might need. After that you add the needed tables to this database and set up the configuration in KBasic's database connection dialog, either in KBasic (using Import CSV or creating plain tables) or in the administration tool. Now, you may be able to see the database tables inside KBasic by selecting 'Query By Example'.
  • You may change the database structure like adding new fields to your tables, droping tables by using the adminstration tool, which comes with your database software, but this is possible in KBasic as well.
  • You may telling the relationship of your tables and their fields to KBasic in the 'Relations' dialog. This is only needed, if you would like to use the 'Query By Example' dialog or KBasic's table editor. If relations is set up correctly, the query by example or table editor tool will consider the information when building the sql statements for you.
  • It is not needed either to use 'Relations' nor 'Query By Example' most database work will be fine without them.
If you use the administration tool of your database to create your tables you need to know how to create a primary key or so. In every table you must define a primary key, which is named 'kbrecordid' all lowercase! and set as 'int' datatype. KBasic assumes that you declare such a field, if it is not declared KBasic's SQL feature will NOT function.

The basic concept of SQL support in KBasic

You may change the database structure like adding new fields to your tables, droping tables by using the adminstration tool, which comes with your database software, though it is possible to manage the database tables from within KBasic (recommended).

Every Form is able to represent a database table, which can be changed and accessed in many ways. Normally, you define the table you would like to use in your form only by setting the right table name of SQLName property of your form. After that you decide, which of the fields of that table you would like to have in you form visible and accessable to your user. You may do this, by adding a new Control for each field of that database table and connect it to the database table field by setting the SQLName to the name of the field you have declared in your database administration tool. By using the Form Wizard, KBasic does this job for you.

Table Designer

This is only needed, if you would like to manage your database tables from within KBasic.

Name of fields must be written as it would be a variable in KBasic. It is recommended to write all fields lowercase for SQL compatibility. Spaces are not allowed.

Table Editor

This is only needed, if you would like to change the data of database tables from within KBasic.

Relations

This is only needed, if you would like to use the ‘Query By Example’ dialog. If relations is set up correctly, the query by example or table edit tool will consider the information when building the sql statements for you.

Query By Example

This tool is provided that you can visually design your sql queries without knowing sql syntax. It is possible to save queries in your project.

Form

The forms do all the hard sql support work for you. You create a Form, adds information about the sql table to use, and add some controls. Every control need to know to which database table field it is pointing to. You decide, which actions the user of your application might do like allowing changing existing records or deleting. That's it. Your database application is ready to run!

Report

The reports are useful, when you would like to print your database records.

Using SQL in forms

  • SQLName - contains the SQL statement to retrieve the data for the form or simple the table name. Currently, only single table selection is supported, which is sufficient for most circumstances. Write the name of the table you would like to use or use a select statement, e.g. “SELECT * FROM myTable”

Example

This is for a form: Selecting all fields from table sql in the order by the field values of textbox.

SELECT * FROM sql ORDER BY textbox
  • SQLInsert - If this value is set to false. SQL inserts are not allowed.
  • SQLUpdate - If this value is set to false. SQL updates are not allowed.
  • SQLDelete - If this value is set to false. SQL deletes are not allowed.

Controls and SQL

SQLName

  • SQLName - contains simple the field name. Currently, only the selection of one table is supported.

Example

This is for a textbox control. The sql table, to which the form of textbox controls points to, contains a database field 'name':

name

See property SQLName of Control for more information.

SQLRelation

It is possible to use 1:n or m:n relations.

Form

m:n relation

It is possible to embed a form into another form using the control ChildControl. By so, you may connect the two sql statements of that form by setting SQLRelation of the child form. You may access the current record of the parent form by using the $ and the field name like $kbrecordid. The SQLRelation contains a valid SQL select statement, which returns a list of id values used to read the records for the child form.

Examples:

Parent form: SQLName

SELECT * FROM movie

Child form: SQLName

SELECT * FROM actor

Child form: SQLRelation

SELECT actorid FROM actor_movie WHERE movieid=$kbrecordid

After each change of the current record of the parent control, the SQLRelation sql statement of the child form is executed. This returns the id values used as kbrecordid values within the SQLName select statement of the child form. Actually, a WHERE clause is added to the end with those id values and the query is refreshed and a new set of records is returned.

Other Control

1:n relation

Take the value from SQLName and search it in SQLRelation in the field kbrecordid of the table city and return name and code sorted by name.

SELECT kbrecordid, name, code FROM city ORDER BY name

First you write kbrecordid (which is the foreign key) and database field you would like to show instead. The information is displayed using SQLName and SQLRelation combined.

This is useful, if you would like to show a more humanreadably text instead of a foreign key.

If you do not write kbrecordid as first selected field, KBasic will automatically do it for you.

See property SQLRelation of Control for more information.

Primary Key

Before V1.89

One TextBox control must be set to “kbrecordid”, otherwise the auto-sql handling of KBasic will fail. If SQLName of one control is “kbrecordid”, the value will be auto-incremented by KBasic and used as primary key. It is recommended to set this control as hidden by 'Visible = False'.

All of your tables must have kbrecordid as of type int and set as primary key, otherwise the auto sql handling of KBasic will fail.

V1.89 and later

Either you define your own primary key field using a database tool and create a table and then go back to KBasic to see it in the table list or you KBasic to create a new table (which will automatically use the field “kbrecordid” as primary key)

Using SQL for filling data without binding

  • SQL - contains the SQL statement to retrieve the data for the control.

See property SQL of Control for more information.

Hidden SQL Operating

  • If you need to change sql records without notice for the user, one way would be to create a form with controls and set the SQLName’s of the controls. Open this form using OpenHidden and use it like you would use a visible form with sql controls and bindings.
  • Another way would be directly creating the needed sql statements.
  • Third possibility is the use of the class Query.

Import/Export

It is possible to import or export database tables provided as comma separated values (CSV files). The separator might be ',' or ';' and the first line may contain the names of the fields.

SQLite (Embedded)

KBasic supports SQLite, so that you do not need to setup a database server. It uses SQLite as embedded database engine and you may create database files locally on your machine.

Each database is locally stored in one file on your machine. Where it is stored, depence on the database name property in the database connection dialog. Actually, you may enter a fixed path there. Relative paths are also supported. The current path is the current of the executable in the sub directory “sqlite” (your program or KBasic's IDE).

“*./sqlite/sqlite.s3db*” is used relative to the application path when deployed or relative to the project path during development in the IDE. Naming the file somewhat else with ./ is expected to work, e.g. ”./mypath/myname.s3db”

For more information, see http://www.sqlite.org/

SQLite does not support LONG (64 bit).

SQLite Administrator for Windows is a powerful tool if you easily want to create, design or administrate SQLite database files. http://sqliteadmin.orbmu2k.de/

Additional information

  • Always name your fields lowercase

QPSQL

  • Do not use PostgreSQL above 8.1.4, because Qt starts to complain about newer versions than 8.1.4.
  • Select encoding “UTF8” for databases.

CONNECT OPTIONS

The format of the options string is a semicolon separated list of option names or option=value pairs. The options depend on the database client used:

ODBC

  • SQL_ATTR_ACCESS_MODE
  • SQL_ATTR_LOGIN_TIMEOUT
  • SQL_ATTR_CONNECTION_TIMEOUT
  • SQL_ATTR_CURRENT_CATALOG
  • SQL_ATTR_METADATA_ID
  • SQL_ATTR_PACKET_SIZE
  • SQL_ATTR_TRACEFILE
  • SQL_ATTR_TRACE
  • SQL_ATTR_CONNECTION_POOLING
  • SQL_ATTR_ODBC_VERSION

MySQL

  • CLIENT_COMPRESS
  • CLIENT_FOUND_ROWS
  • CLIENT_IGNORE_SPACE
  • CLIENT_SSL
  • CLIENT_ODBC
  • CLIENT_NO_SCHEMA
  • CLIENT_INTERACTIVE
  • UNIX_SOCKET

PostgreSQL

  • connect_timeout
  • options
  • tty
  • requiressl
  • service

DB2

  • SQL_ATTR_ACCESS_MODE
  • SQL_ATTR_LOGIN_TIMEOUT

OCI

  • OCI_ATTR_PREFETCH_ROWS
  • OCI_ATTR_PREFETCH_MEMORY

SQLite

  • QSQLITE_BUSY_TIMEOUT

Interbase

  • ISC_DPB_LC_CTYPE
  • ISC_DPB_SQL_ROLE_NAME

Example how to write the string.

CLIENT_SSL=1;CLIENT_IGNORE_SPACE=1

MS Access on Windows

I checked the Qt documentation again and it is possible to use .mdb files without the need of ODBC (but I guess it runs only on Windows) and I haven't tested it yet.

Set the database name in the connection dialog to the following string, replacing “myaccessfile.mdb” with the real file name.

“DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=myaccessfile.mdb”

sql.txt · Last modified: 2013/04/09 22:58 (external edit)