SQLdb Package: what changed
Revision 2 to 3. Removed lines have a minus sign; added lines have a plus sign.
| Old | New | Line | |
|---|---|---|---|
| 1 | - | ||
| 1 | + | The SQLdb package contains FPC units to access a number of SQL databases. It is "packaged" as sqldblaz.lpk in Lazarus, and the components can be found on the [[SQLdb tab]]. | |
| 2 | 2 | ||
| 3 | - | ||
| 3 | + | [](</File:sqldbcomponents-n.png>) | |
| 4 | 4 | ||
| 5 | 5 | ## Documentation | |
| 6 | 6 | ||
| 7 | - | ||
| 7 | + | See [SQLDB Documentation](<http://www.freepascal.org/docs-html/fcl/sqldb/index.html>) | |
| 8 | 8 | ||
| 9 | 9 | ## Components | |
| 10 | 10 | ||
| 11 | - | ||
| 11 | + | The SQLdb package provides the following components: | |
| 12 | 12 | ||
| 13 | 13 | #### TXXXConnection | |
| 14 | 14 | ||
| 15 | - | ||
| 15 | + | Documentation: see [TSQLConnection documentation](<http://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.html>) | |
| 16 | 16 | ||
| 17 | - | ||
| 17 | + | There are various connection components called TXXXConnection, where XXX is the flavour of the database you are connecting to. Each one of these components takes the requests of the SQLQuery and SQLTransaction components and translates them into requests specifically tailored for the database you are using. These connection objects descend from the generic TSQLConnection; you can use TSQLConnection to create database programs that can connect to multiple databases (see [[SQLdb Tutorial3]]. | |
| 18 | 18 | ||
| 19 | - | ||
| 19 | + | The actual components are: | |
| 20 | 20 | ||
| 21 | - | ||
| 22 | - | ||
| 23 | - | ||
| 24 | - | ||
| 25 | - | ||
| 26 | - | ||
| 27 | - | ||
| 28 | - | ||
| 29 | - | ||
| 30 | - | ||
| 31 | - | ||
| 32 | - | ||
| 33 | - | ||
| 21 | + | * TIBConnection ([[Firebird|Borland Interbase / Firebird]]) | |
| 22 | + | * TMSSQLConnection ([[mssqlconn|Microsoft SQL Server]], available since FPC version 2.6.1/Lazarus 1.0.8) | |
| 23 | + | * TMySQL40Connection ([[mysql|MySQL]], requires MySQL 4.0 client library) | |
| 24 | + | * TMySQL41Connection ([[mysql|MySQL]], requires MySQL 4.1 client library) | |
| 25 | + | * TMySQL50Connection ([[mysql|MySQL]], requires MySQL 5.0 client library) | |
| 26 | + | * TMySQL51Connection ([[mysql|MySQL]], requires MySQL 5.1 client library, available since FPC version 2.5.1) | |
| 27 | + | * TMySQL55Connection ([[mysql|MySQL]], requires MySQL 5.5 client library, available since FPC version 2.6.1/Lazarus 1.0.8) | |
| 28 | + | * TODBCConnection (An ODBC connection to a database that the PC has the driver for; see [[ODBCConn]]) | |
| 29 | + | * TOracleConnection ([[Lazarus Database Overview|Oracle]]) | |
| 30 | + | * TPQConnection ([[postgresql|PostgreSQL]]) | |
| 31 | + | * TSybaseConnection ([[mssqlconn|Sybase ASE]], available since FPC version 2.6.1/Lazarus 1.0.8) | |
| 32 | + | * TSQLite3Connection ([[SQLite]], available since FPC version 2.2.2) | |
| 33 | + | * TMSSQLConnection ([[mssqlconn|MSSQL]], requires FreeTDS library) | |
| 34 | 34 | ||
| 35 | 35 | #### TSQLTransaction | |
| 36 | 36 | ||
| 37 | - | ||
| 37 | + | Documentation: see [TSQLTransaction documentation](<http://www.freepascal.org/docs-html/fcl/sqldb/tsqltransaction.html>) | |
| 38 | 38 | ||
| 39 | - | ||
| 39 | + | This encapsulates the transaction on the database server. A TXXXConnection object always needs at least one TSQLTransaction associated with it, so that the transaction of its queries is managed. Queries/actions on the database need to be encapsulated in | |
| 40 | 40 | ||
| 41 | - | ||
| 42 | - | ||
| 41 | + | * a .StartTransaction/.Commit (or .Rollback) block | |
| 42 | + | * or a single .StartTransaction at the beginning, followed by .CommitRetaining or .RollBackretaiing. CommitRetaining and RollbackRetaining keep the transaction open, so performing another StartTransaction is not needed. | |
| 43 | 43 | ||
| 44 | - | ||
| 44 | + | Setting .Active to true if it was false is equivalent to .StartTransaction. | |
| 45 | 45 | ||
| 46 | - | ||
| 46 | + | Setting .Active to false if it was true is equivalent to .Rollback | |
| 47 | 47 | ||
| 48 | + | ||
| 49 | + | ||
| 48 | 50 | #### TSQLQuery | |
| 49 | 51 | ||
| 50 | - | ||
| 52 | + | Documentation: see [TSQLQuery ](<http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.html>) | |
| 51 | 53 | ||
| 52 | - | ||
| 54 | + | This is a descendant of TDataset, and provides the data as a table from the SQL query that you submit. However, it can also be used to execute SQL queries (e.g. stored procedures, INSERT INTO..) that don't return any data. See [[Working With TSQLQuery]] for more details. | |
| 53 | 55 | ||
| 54 | 56 | ## Using the SQLdb Package | |
| 55 | 57 | ||
| 56 | - | ||
| 58 | + | To use the SQLdb package, you need a TSQLQuery component, a TSQLTransaction component, and one of the connection components. | |
| 57 | 59 | ||
| 58 | - | ||
| 60 | + | Here is a quick tutorial: | |
| 59 | 61 | ||
| 60 | - | ||
| 61 | - | ||
| 62 | - | ||
| 63 | - | ||
| 64 | - | ||
| 65 | - | ||
| 66 | - | ||
| 62 | + | * Make sure you have the relevant database client/driver installed. | |
| 63 | + | * Go to the SQLdb tab in Component Palette. | |
| 64 | + | * Add a TXXXConnection component (suitable for the type of database you are using) to a form, and fill in the appropriate properties to connect to your database. These will vary depending on the actual database. Make sure you can set the "connected" property to true. | |
| 65 | + | * Add a TSQLTransaction component. Return to the Connection component, and set its Transaction Property to the new transaction. This should also set the transaction's database property. You should be able to set active to true. | |
| 66 | + | * Add an SQLQuery component and set its Database property to the connecton component you just added. | |
| 67 | + | * Set the SQLQuery "SQL" property. For a first test, just do a "select * from <tablename>". | |
| 68 | + | * Set active to true. If this works, you have opened your query, and the data is available. | |
| 67 | 69 | ||
| 68 | - | ||
| 70 | + | From here on, adding controls is the same as for any database, but just to recap: | |
| 69 | 71 | ||
| 70 | - | ||
| 71 | - | ||
| 72 | + | * Add a TDatasource component (from the Data Access tab) and set its dataset property to the SQLQuery component | |
| 73 | + | * Add some controls from the Data Controls tab. For a quick check, add a TDBGrid component, and set its datasource property to the Datasource component you just added. You should see the data from the query you entered. | |
| 72 | 74 | ||
| 73 | - | ||
| 75 | + | Of course, this is just the beginning, but getting some data in a grid is usually the first step to ensure the database connection is working. More detailed explanations will be found at [[Working With TSQLQuery]] and [[SQLdb Tutorial1]]. | |
| 74 | 76 | ||
| 75 | 77 | ## See also | |
| 76 | 78 | ||
| 77 | - | ||
| 78 | - | ||
| 79 | - | ||
| 80 | - | ||
| 81 | - | ||
| 79 | + | * [[SqlDBHowto]] | |
| 80 | + | * [[SQLdb Programming Reference]] | |
| 81 | + | * [[Databases]] | |
| 82 | + | * [interface view of the classes](<http://z505.com/cgi-bin/powtils/docs/1.6/idx.cgi?file=index-4&unit=sqldb>) | |
| 83 | + | * [some additional documentation/notes](<http://z505.com/cgi-bin/powtils/docs/1.6/idx.cgi?file=fcldbnotes>) | |
| 82 | 84 | ||
| 83 | 85 | --- | |
| 84 | - | ||
| 86 | + | *From the Free Pascal wiki, page "SQLdb Package", as the Internet Archive kept it on 2026-02-10, last changed on 2024-09-23. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).* |