View source for Database library

A library of functions to make it easy to create, read, update, examine and delete database tables, without having to write SQL. It works with Microsoft Access, Microsoft SQL Server, MariaDB, and PostgreSQL, and can be expanded to support other database platforms.

''Database access functions need {{Analytica Developer}}, [[Optimizer]], or [[ACP]].''

__TOC__

:[[File:DB_Library_Top_Level.png|600px]]

You can '''download this library here''': [[Media:Database library.ana|Database library.ana]]

==Supported Database Platforms==

You can select any of these supported platforms from the '''DB Platform''' menu:

* Microsoft Access
* Microsoft SQL Server
* MariaDB
* PostgreSQL

See section [[#Adding DB Platforms]] on how to add another database platform (for advanced users).

==User Functions==

These functions retrieve and update records in a selected table:

* '''DB_Retrieve_Record:''' Retrieves columns «retrieveField» from an existing database table «tbl» for records where column names «lookupField» have entries of «lookupValue». Parameters «retrieveField», «lookupField» and «lookupValue» are optional. Omitting «retrieveField» will return all columns in the table. Omitting either «lookupField» or «lookupValue» will return all records in the table.

* '''DB_Update_Record:''' Syntax to update the values in an existing record in database table «tbl». The field and updated value pairs are specified by the «updatedField» and «updatedValue» parameters, respectively. The existing record to be updated is found by searching for the field and value pairs specified by the «lookupField» and «lookupValue» parameters, respectively.

* '''DB_Add_Record:''' Adds a new record to database table «tbl». The field and value pairs of the new record are specified by the «field» and «value» parameters, respectively.

* '''DB_Delete_Record:''' Deletes existing database records where column names «field» have entries of «value».

These functions create, review, modify, and delete tables from the database schema:

* '''DB_Create_Table:''' Create a new database table having column names of «field», data formats of «type», and optional null or non-null designation of «constraint». 

* '''DB_List_Tables:''' Lists all the table names from an existing database table referenced in the «connectStr».

* '''DB_List_Columns:''' Lists all the column names in an existing database table «tbl».

* '''DB_Add_Column:''' Adds a new column «field» to an existing database table «tbl».

* '''DB_Delete_Column:''' Deletes an existing column «field» from an existing database table «tbl».

* '''DB_Delete_Table:''' Deletes an existing database table «tbl».

==Examples Module==
To quickly try out this library with a live test database, you can '''download this example module here''': [[Media:Database library demo.ana|Database library demo.ana]]. This module will guide you through creating a table and inserting, retrieving, and deleting data. 

==Adding DB Platforms==

The bane of SQL programmers is that each database platform uses a slightly different syntax.  Advanced users who want to use another DB platform (beyond the four we currently support) can add their syntax through the '''DB Syntax Definitions''' module. Add th enew platform to the '''DB Platforms''' index and syntax to each of the provided syntax variables, then switch the selected '''DB Platform''' at the top of the library and test your syntax by making use of the debug parameter provided by each DB function.

==See also==

* [[Database access]] Introduction to accessing databases
* [[DbQuery]] Built-in function to query  or search a database using SQL
* [[DbWrite]] Built-in function to write to or change a database using SQL

==History==

Updated with new DB Functions library in Feb, 20, 2024
[[Category:Database Functions]]