Difference between revisions of "fcl-db"

From Lazarus wiki
Jump to navigationJump to search
(→‎Units: fpjsondataset)
 
(11 intermediate revisions by 4 users not shown)
Line 1: Line 1:
The package FCL-db contains most of FPC's higher level database system, plus table drivers for some popular systems.
+
{{fcl-db}}
 +
 
 +
The package '''fcl-db''' contains most of FPC's higher level database system, plus table drivers for some popular systems. (<LAZDIR>/fpc/3.0.0/source/packages/fcl-db)
  
 
== Known issues/shortcomings ==
 
== Known issues/shortcomings ==
 
 
* Master - detail relations are not complete; however you can implement it using [[MasterDetail]]
 
* Master - detail relations are not complete; however you can implement it using [[MasterDetail]]
 
* Calculated field support is not complete
 
* Calculated field support is not complete
Line 18: Line 19:
 
Most of these are being worked on, and the status changes on a regular basis, so be sure to do your own testing and source inspection, since this will be most certainly out of date.
 
Most of these are being worked on, and the status changes on a regular basis, so be sure to do your own testing and source inspection, since this will be most certainly out of date.
  
A testsuite exists (see [[Databases#Running_FPC_database_tests]]) and is expanded when new bugs and functionality occurr.
+
A [[Databases#Running_FPC_database_tests|testsuite]] exists (test/testresult-db) and is expanded when new bugs and functionality occur.
  
 
== Units ==
 
== Units ==
  
(In the below table the subdir is listed as "submodule", so one can see easily to which subsystem the unit belongs.
+
(In the below table the subdirectory is listed as "submodule", so one can see easily to which subsystem the unit belongs.
  
 
{| class="wikitable sortable"
 
{| class="wikitable sortable"
Line 29: Line 30:
 
|[[browseds]]||sqlite||
 
|[[browseds]]||sqlite||
 
|----
 
|----
|[[bufdataset]]||base||In memory dataset. More capable than memds. See [[How_to_write_in-memory_database_applications_in_Lazarus/FPC#TBufDataSet]] for some usage examples
+
|[[bufdataset]]||base||In memory dataset. More capable than memds. See [[How to write in-memory database applications in Lazarus/FPC#TBufDataSet|TBufDataSet]] for some usage examples
 
|----
 
|----
 
|[[bufdataset_parser]]||base||
 
|[[bufdataset_parser]]||base||
Line 99: Line 100:
 
|[[dbf_wtil]]||dbase||
 
|[[dbf_wtil]]||dbase||
 
|----
 
|----
|[[mmsqlconn#dblib|dblib]]||||Wrapper around FreeTDS; required for the mssqlconn SQLDB driver for Microsoft SQL Server and Sybase ASE
+
|[[mssqlconn#dblib|dblib]]||||Wrapper around FreeTDS; required for the mssqlconn SQLDB driver for Microsoft SQL Server and Sybase ASE
 
|----
 
|----
 
|[[dbwhtml]]||base||
 
|[[dbwhtml]]||base||
Line 117: Line 118:
 
|[[fpdatadict]]||datadict||
 
|[[fpdatadict]]||datadict||
 
|----
 
|----
|[[fpdbexport]]||export||See the dbftool example included in FPC 2.7.1+: creating, using DBF files and exporting data using db export. Also used in the Lazarus db export component.
+
|[[fpDBExport|fpdbexport]]||export||See the dbftool example included in FPC 2.7.1+: creating, using DBF files and exporting data using db export. Also used in the Lazarus db export component.
 
|----
 
|----
 
|[[fpdbfexport]]||export||
 
|[[fpdbfexport]]||export||
Line 171: Line 172:
 
|[[mssqlconn]]||sqldb/mssqlconn||Microsoft SQL Server and Sybase ASE drivers, introduced in FPC 2.6.1. Requires dblib.
 
|[[mssqlconn]]||sqldb/mssqlconn||Microsoft SQL Server and Sybase ASE drivers, introduced in FPC 2.6.1. Requires dblib.
 
|----
 
|----
|[[mysql40conn]]||sqldb/mysql||Connector for MySQL server using MySQL 4.0 client library
+
|[[mysql|mysql40conn]]||sqldb/mysql||Connector for MySQL server using MySQL 4.0 client library
 
|----
 
|----
|[[mysql41conn]]||sqldb/mysql||Connector for MySQL server using MySQL 4.1 client library
+
|[[mysql|mysql41conn]]||sqldb/mysql||Connector for MySQL server using MySQL 4.1 client library
 
|----
 
|----
|[[mysql4conn]]||sqldb/mysql||Connector for MySQL server using MySQL 4?? client library
+
|[[mysql|mysql4conn]]||sqldb/mysql||Connector for MySQL server using MySQL 4?? client library
 
|----
 
|----
|[[mysql50conn]]||sqldb/mysql||Connector for MySQL server using MySQL 5.0 client library
+
|[[mysql|mysql50conn]]||sqldb/mysql||Connector for MySQL server using MySQL 5.0 client library
 
|----
 
|----
|[[mysql51conn]]||sqldb/mysql||Connector for MySQL server using MySQL 5.1 client library
+
|[[mysql|mysql51conn]]||sqldb/mysql||Connector for MySQL server using MySQL 5.1 client library
 
|----
 
|----
|[[mysql55conn]]||sqldb/mysql||Connector for MySQL server using MySQL 5.5 client library
+
|[[mysql|mysql55conn]]||sqldb/mysql||Connector for MySQL server using MySQL 5.5 client library
 
|----
 
|----
 
|[[ODBCConn|odbcconn]]||sqldb/odbc||Connector for ODBC databases (e.g. Microsoft Access, DB2)
 
|[[ODBCConn|odbcconn]]||sqldb/odbc||Connector for ODBC databases (e.g. Microsoft Access, DB2)
Line 201: Line 202:
 
|[[sqliteds]]||sqlite||
 
|[[sqliteds]]||sqlite||
 
|----
 
|----
|[[sqlscript]]||base||
+
|[[sqlscript]]||base||SQL scripting component that lets you run multiple SQL statement as a batch. Has support for Firebird SET TERM. See example in Lazarus examples/database/tsqlscript and [[Firebird#Creating_objects_programmatically]]
 
|----
 
|----
 
|[[tdbf_l]]||dbase||
 
|[[tdbf_l]]||dbase||
Line 224: Line 225:
 
|----
 
|----
 
|[[xmldatapacketreader]]||base||
 
|[[xmldatapacketreader]]||base||
 +
|----
 +
|[[fpjsondataset]]||json||Dataset for JSON data
 
|}
 
|}
  
Line 229: Line 232:
  
 
* [[Package_List|Packages List]]
 
* [[Package_List|Packages List]]
 
 
[[Category:Packages]]
 
[[Category:Free Component Library]]
 
[[Category:Databases]]
 

Latest revision as of 21:31, 1 April 2021

English (en) français (fr) polski (pl)

The package fcl-db contains most of FPC's higher level database system, plus table drivers for some popular systems. (<LAZDIR>/fpc/3.0.0/source/packages/fcl-db)

Known issues/shortcomings

  • Master - detail relations are not complete; however you can implement it using MasterDetail
  • Calculated field support is not complete
  • No binary data transfer using parameters (everything is converted to ASCII) in most drivers.
  • There are some floating point issues, with precision and scale parameters only minimally supported in some drivers (amongst others mysql)
  • Most character encoding issues are solved fairly ad hoc. There is no way to set the fundamental encodings manually:
    1. the encoding of the connection
    2. the encoding of the components' internal storage
    3. the encoding of GUI components
    4. (optionally, encoding of exports, or fileformats)
  • Many driver dependent issues in datetime types and timezone support.
  • Between the drivers, Firebird is used the most, then Mysql, SQLite, PostgreSQL and ODBC. Finally comes Oracle which is mostly still at a proof of concept level. The Microsoft SQL Server and Sybase ASE drivers are a recent addition to FPC (2.6.1 and higher) and Lazarus.
  • Before FPC 2.6: no stored procedure resultset >1 row

Most of these are being worked on, and the status changes on a regular basis, so be sure to do your own testing and source inspection, since this will be most certainly out of date.

A testsuite exists (test/testresult-db) and is expanded when new bugs and functionality occur.

Units

(In the below table the subdirectory is listed as "submodule", so one can see easily to which subsystem the unit belongs.

Unit submodule comment
browseds sqlite
bufdataset base In memory dataset. More capable than memds. See TBufDataSet for some usage examples
bufdataset_parser base
concurrencyds sqlite
createds sqlite
customsqliteds sqlite
db base
dbcoll base
dbconst base
dbf dbase TDBF components for DBase/FoxPro/Visual Foxpro tables (upstream code: Sourceforge TDBF project). See also Lazarus Tdbf Tutorial
dbf_avl dbase
dbf_collate dbase
dbf_common dbase
dbf_cursor dbase
dbf_dbffile dbase
dbf_fields dbase
dbf_idxcur dbase
dbf_idxfile dbase
dbf_lang dbase
dbf_memo dbase
dbf_parser dbase
dbf_pgcfile dbase
dbf_pgfile dbase
dbf_prscore dbase
dbf_prsdef dbase
dbf_prssupp dbase
dbf_reg dbase
dbf_str dbase
dbf_str_es dbase
dbf_str_fr dbase
dbf_str_ita dbase
dbf_str_nl dbase
dbf_str_pl dbase
dbf_str_pt dbase
dbf_str_ru dbase
dbf_wtil dbase
dblib Wrapper around FreeTDS; required for the mssqlconn SQLDB driver for Microsoft SQL Server and Sybase ASE
dbwhtml base
fillds sqlite
fpcgcreatedbf codegen
fpcgdbcoll codegen
fpcgsqlconst codegen
fpcgtiopf codegen
fpcsvexport export
fpdatadict datadict
fpdbexport export See the dbftool example included in FPC 2.7.1+: creating, using DBF files and exporting data using db export. Also used in the Lazarus db export component.
fpdbfexport export
fpddcodegen codegen
fpdddbf datadict
fpdddiff datadict
fpddfb datadict
fpddmysql40 datadict
fpddmysql41 datadict
fpddmysql50 datadict
fpddodbc datadict
fpddoracle datadict
fpddpopcode codegen
fpddpq datadict
fpddregstd datadict
fpddsqldb datadict
fpddsqlite3 datadict
fpfixedexport export Dataset export to fixed width text format
fprtfexport export Dataset export to RTF format
fpsimplejsonexport export Dataset export to JSON format
fpsimplexmlexport export Dataset export to ASCII encoded XML
fpsqlexport export Dataset export to SQL Insert/Update statements
fpstdexports export
fptexexport export Dataset export to Latex format
fpXMLXSDExport export Dataset export to various XML formats: Access, ADO.Net, Excel, Delphi ClientDataset
ibconnection sqldb/interbase
memds memds In memory dataset. Not as capable as bufdataset. See How_to_write_in-memory_database_applications_in_Lazarus for some usage examples
mssqlconn sqldb/mssqlconn Microsoft SQL Server and Sybase ASE drivers, introduced in FPC 2.6.1. Requires dblib.
mysql40conn sqldb/mysql Connector for MySQL server using MySQL 4.0 client library
mysql41conn sqldb/mysql Connector for MySQL server using MySQL 4.1 client library
mysql4conn sqldb/mysql Connector for MySQL server using MySQL 4?? client library
mysql50conn sqldb/mysql Connector for MySQL server using MySQL 5.0 client library
mysql51conn sqldb/mysql Connector for MySQL server using MySQL 5.1 client library
mysql55conn sqldb/mysql Connector for MySQL server using MySQL 5.5 client library
odbcconn sqldb/odbc Connector for ODBC databases (e.g. Microsoft Access, DB2)
oracleconnection sqldb/oracle Connector for Oracle (XE) databases
pqconnection sqldb/postgres Connector for PostgreSQL databases
sqlite3conn sqldb/sqlite
paradox paradox
sdfdata sdf CSV dataset support
sqldb sqldb
sqlite3ds sqlite
sqliteds sqlite
sqlscript base SQL scripting component that lets you run multiple SQL statement as a batch. Has support for Firebird SET TERM. See example in Lazarus examples/database/tsqlscript and Firebird#Creating_objects_programmatically
tdbf_l dbase
testcp memds
testdbf dbase
testds sqlite
testfix sdf
testld memds
testopen memds
testpop memds
testsdf sdf
testsqldb sqldb
xmldatapacketreader base
fpjsondataset json Dataset for JSON data

See also