Server Talk > Scripting Releases
libdb (SQLite / MySQL / MariaDB / PostgreSQL / ODBC library)
SyavX:
libdb
Download
libdb - is an external library for the SoldatServer (Windows and Linux) to access SQLite, MySQL/MariaDB, PostgreSQL or any database type (Oracle, Access, etc) via ODBC.
// =====================================
// U S A G E
// =====================================
An attached archive from the download section has the following structure:
libdb-0.3.dll
libdb-0.3.so
libmariadb.dll
libmariadb.so
scripts
├── libdb
│ ├── database.pas
│ └── libdb.pas
└── libdb_test
├── config.ini
└── main.pas
Where libdb.pas is a unit with external functions bindings, database.pas is a unit with some helper functions and libdb_test folder contains an example script that demonstrates the use of libdb with SQLite, MySQL/MariaDB and PostgreSQL.
You should extract an archive into the root directory of your Soldat server. Thus *.dll and *.so files will be placed near to soldatserver.exe (Windows) or soldatserver (Linux) executables and the rest of files will be copied into the ./scripts directory of your server.
Make sure you have AllowDlls enabled in the server.ini.
// =====================================
// N O T E S
// =====================================
Windows:
Everything should work out of the box.
Linux:
libdb-0.3 depends on unixODBC.
For Debian-based distros you could use the following command to install dependencies:
--- Code: (libdb-0.3) ---sudo apt install libodbc1:i386 libltdl7:i386
--- End code ---
There could be a need to add soldatserver's directory into LD_LIBRARY_PATH environment variable to use external library functions with Soldat server. There are a lot ways of doing it, so here are few of simple ones:
* LD_LIBRARY_PATH="$PWD" ./soldatserver - this will set current directory (for this particular run) as LD_LIBRARY_PATH and run the soldatserver
* export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/path/to/soldatserver && ./soldatserver - this will add current directory to LD_LIBRARY_PATH and run the soldatserver
// =====================================
// I N D E X
// =====================================
Plugins:
DB_Plugin_MySQL
DB_Plugin_ODBC
DB_Plugin_PostgreSQL
DB_Plugin_SQLite
Functions:
DB_AffectedRows()
DB_CheckNull()
DB_Close()
DB_ColumnIndex
DB_ColumnName()
DB_Columns()
DB_ColumnSize()
DB_ColumnType()
DB_DriverDescription()
DB_DriverName()
DB_Error()
DB_ExamineDrivers()
DB_FinishQuery()
DB_FirstRow()
DB_GetDouble()
DB_GetFloat()
DB_GetLong()
DB_GetQuad()
DB_GetString()
DB_IsDatabase()
DB_NextDriver()
DB_NextRow()
DB_Open()
DB_PreviousRow()
DB_Query()
DB_QueryX()
DB_SetDouble()
DB_SetFloat()
DB_SetLong()
DB_SetNull()
DB_SetQuad()
DB_SetString()
DB_Update()
DB_UseMySQL
DB_UseODBC
DB_UsePostgreSQL
DB_UseSQLite
DB_GetVersion()
// =====================================
// P L U G I N S
// =====================================
// [Index] =======================================================================================
DB_Plugin_MySQL
Description
MySQL and MariaDB (an opensource fork of MySQL) are powerful, server based database managers which support very large database and high concurrency. libdb uses opensource MariaDB library to connect MySQL and MariaDB databases seemlessly. When shipping your script, you will need to add "libmariadb.dll" (Windows) and "libmariadb.so" (Linux) to your package.
There is no additional driver to install, all is ready to connect a MySQL or MariaDB server. For more information about MariaDB: https://mariadb.org/.
After calling DB_UseMySQL(), a MySQL or MariaDB database has to be connected using DB_Open() before using any other database functions. MySQL specific parameters have to be passed in the DatabaseName parameter of DB_Open():
host - Name of host or IP address to connect to.
port - Port number to connect to at the server host.
dbname - The database name.
If you are running MySQL/MariaDB server on the same machine as soldatserver, you would like to use a socket connection instead of a TCP.
To do so you should specify host=localhost in the DatabaseName parameter and make sure there is a socket path configured in the my.cnf file (usually in /etc/mysql/):
--- Code: ---[mysqld]
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock
--- End code ---
Remarks
See also DB_UseMySQL().
// [Index] =======================================================================================
DB_Plugin_ODBC
Description
ODBC is a standard application programming interface (API) for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems.
After calling DB_UseODBC(), a database has to be opened using DB_Open() with a registered ODBC database name as DatabaseName before using any other database functions.
It is possible to obtain a list of available ODBC drivers by calling the function DB_ExamineDrivers().
Remarks
See also DB_UseODBC().
// [Index] =======================================================================================
DB_Plugin_PostgreSQL
Description
PostgreSQL is a powerful, server based database manager which support very large database and high concurrency. It is free to use in commercial projects. There is no additional driver to install, all is ready to connect a PostgreSQL server. For more information about PostgreSQL: https://www.postgresql.org/
After calling DB_UsePostgreSQL(), a PostgreSQL database has to be connected using DB_Open() before using any other database functions.
PostgresSQL specific parameters can be passed in the DatabaseName parameter of DB_Open():
host - Name of host to connect to.
hostaddr - Numeric IP address of host to connect to.
port - Port number to connect to at the server host.
dbname - The database name. Defaults to be the same as the user name.
connect_timeout - Maximum wait for connection, in seconds (write as a decimal integer string).
Zero or not specified means wait indefinitely.
It is not recommended to use a timeout of less than 2 seconds.
Remarks
See also DB_UsePostgreSQL().
// [Index] =======================================================================================
DB_Plugin_SQLite
Description
SQLite is a file based, serverless database manager. There is no driver or additional files to install, all is ready to use. SQLite is widely spread accross the industry and is considered to be one of the best embedded database manager available. For more information about SQLite: https://www.sqlite.org/
To create a new empty database, create an empty file first. Database commands can now be used to create tables and add records.
After calling DB_UseSQLite(), a SQLite database has to be opened using DB_Open() before using any other database functions.
Remarks
See also DB_UseSQLite().
// =====================================
// F U N C T I O N S
// =====================================
// [Index]
=======================================================================================
Function DB_AffectedRows(DatabaseID: LongInt): LongInt;
Description
Returns the number of rows affected by the last DB_Update() operation.
Parameters
DatabaseID Specifies the database to use.
Return value
Returns the number of the affected rows.
// [Index]
=======================================================================================
Function DB_CheckNull(DatabaseID: LongInt; Column: Word): Boolean;
Description
Checks if the content of the specified database column is null. This command is only valid after a successful DB_FirstRow(), DB_PreviousRow() or DB_NextRow().
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use. DB_ColumnIndex() is available to get the index of a named column.
Return value
Returns True is the data is null, False otherwise.
// [Index] =======================================================================================
Procedure DB_Close(DatabaseID: LongInt);
Description
Close the specified DatabaseID (and connections/transactions if any). No further operations are allowed on this database.
All remaining opened databases are automatically closed when the program ends.
Parameters
DatabaseID Specifies the database to close.
// [Index] =======================================================================================
Function DB_ColumnIndex(DatabaseID: LongInt; ColumnName: WideString): Word;
Description
Returns the index of the column after executing a query with DB_Query() in the opened DatabaseID. This can be useful for use with commands like DB_GetLong() which require a column index.
Parameters
DatabaseID Specifies the database to close.
ColumnName Specifies the name of the column to get the index of.
Return value
Returns the index of the specified column. This is only valid after having executed a query with DB_Query().
// [Index] =======================================================================================
Function DB_ColumnName(DatabaseID, Column: Integer): PChar;
Description
Return the name of the specified column in the DatabaseID.
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use.
Return value
Returns the name of the column.
// [Index]
=======================================================================================
Function DB_Columns(DatabaseID: LongInt): Word;
Description
Returns the numbers of columns (fields) in the opened DatabaseID.
Parameters
DatabaseID Specifies the database to use.
Return value
Returns the number of columns in the database.
// [Index] =======================================================================================
Function DB_ColumnSize(DatabaseID: LongInt; Column: Word): LongInt;
Description
Return the size of the specified column in the DatabaseID. It is especially useful when the size of the column can change depending of the records, like a string column.
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use.
Return value
Returns the size of the column in bytes.
// [Index] =======================================================================================
Function DB_ColumnType(DatabaseID: LongInt; Column: Word): TDatabaseColumnType;
Description
Return the type of the specified column in the DatabaseID.
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use.
Return value
Returns the type of the given column.
Type values can be:
DB_Type_Undefined - the type is undefined or the function has failed (e.g. it was not possible to determine the data type)
DB_Type_Long - Numeric format (a LongInt in Pascal)
DB_Type_String - String format (a PChar in Pascal)
DB_Type_Float - Numeric float format (a Single in Pascal)
DB_Type_Double - Numeric double format (a Double in Pascal)
DB_Type_Quad - Numeric quad format (an Int64 in Pascal)
// [Index] =======================================================================================
Function DB_DriverDescription(): PChar;
Description
Returns the description of the current database driver. Drivers are listed using the DB_ExamineDrivers() and DB_NextDriver() functions.
Return value
Returns the description string.
Remarks
This is an ODBC database specific command.
// [Index] =======================================================================================
Function DB_DriverName(): PChar;
Description
Return the name of the current database driver. Drivers are listed using the DB_ExamineDrivers() and DB_NextDriver() functions.
Return value
Returns the name of the driver.
Remarks
This is an ODBC database specific command.
// [Index] =======================================================================================
Function DB_Error(): PChar;
Description
Returns a description of the last database error in text format. This is especially useful with the following functions: DB_Open(), DB_Query() and DB_Update().
Return value
Returns the error description.
Example
--- Code: (Pascal) --- // First, connect to a database with a 'players' table
If DB_Query(DatabaseID, 'SELECT * FROM players') Then Begin // Get all the records in the 'players' table
// Code goes here...
DB_FinishQuery(DatabaseID);
End Else
WriteLn('Error: Can`t execute the query: '+DB_Error);
--- End code ---
// [Index] =======================================================================================
Function DB_ExamineDrivers(): Boolean;
Description
Examines the database drivers available on the system.
Return value
If ODBC isn't installed or no drivers are available, it returns False, otherwise DB_NextDriver() can be used to list all the drivers.
Remarks
This is an ODBC database specific command.
Example
--- Code: (Pascal) --- If DB_ExamineDrivers Then Begin
WriteLn('List of ODBC drivers installed:');
While DB_NextDriver Do
WriteLn('Name: ' + DB_DriverName + '; Description: ' + DB_DriverDescription);
End Else
WriteLn('No ODBC drivers installed!');
--- End code ---
// [Index] =======================================================================================
Procedure DB_FinishQuery(DatabaseID: LongInt);
Description
Finish the current database SQL query and release its associated resources. Query related functions like DB_FirstRow() or DB_NextRow() can't be used anymore.
Parameters
DatabaseID Specifies the database to use.
Example
--- Code: (Pascal) --- // First, connect to a database with a 'players' table
If DB_Query(DatabaseID, 'SELECT * FROM players') Then Begin // Get all the records in the 'players' table
While DB_NextRow(DatabaseID) Do // Loop for each records
WriteLn(DB_GetString(DatabaseID, 0)); // Display the content of the first column
DB_FinishQuery(DatabaseID);
End;
--- End code ---
// [Index] =======================================================================================
Function DB_FirstRow(DatabaseID: LongInt): Boolean;
Description
Retrieves information about the first DatabaseID row. The DB_QueryX() with a DB_Cursor_Dynamic has to be used instead of DB_Query() to have this command working.
Parameters
DatabaseID Specifies the database to use.
Return value
Returns False, then no row is available.
Remarks
To access fields within a row, DB_GetLong(), DB_GetFloat() and DB_GetString() can be used.
// [Index] =======================================================================================
Function DB_GetDouble(DatabaseID: LongInt; Column: Word): Double;
Description
Returns the content of the specified database column as a double precision floating-point number. This command is only valid after a successful DB_FirstRow(), DB_PreviousRow()DatabaseRow() or DB_NextRow().
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use. DB_ColumnIndex() is available to get the index of a named column.
Return value
Returns a double precision floating-point value.
Remarks
To determine the type of a column, DB_ColumnType() can be used.
Note: This function can be called only once for each column. Therefore if this value needs to be used more than once, the data has to be stored in a variable, since all subsequent calls will return the wrong value. This is an ODBC limitation.
// [Index] =======================================================================================
Function DB_GetFloat(DatabaseID: LongInt; Column: Word): Single;
Description
Returns the content of the specified database column as a floating-point number. This command is only valid after a successful DB_FirstRow(), DB_PreviousRow()DatabaseRow() or DB_NextRow().
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use. DB_ColumnIndex() is available to get the index of a named column.
Return value
Returns a single precision floating-point value.
Remarks
To determine the type of a column, DB_ColumnType() can be used.
Note: This function can be called only once for each column. Therefore if this value needs to be used more than once, the data has to be stored in a variable, since all subsequent calls will return the wrong value. This is an ODBC limitation.
// [Index] =======================================================================================
Function DB_GetLong(DatabaseID: LongInt; Column: Word): LongInt;
Description
Returns the content of the specified DatabaseID column as an integer number. This command is only valid after a successful DB_FirstRow(), DB_PreviousRow()DatabaseRow() or DB_NextRow().
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use. DB_ColumnIndex() is available to get the index of a named column.
Return value
Returns the content of the column as an integer value.
Remarks
To determine the type of a column, DB_ColumnType() can be used.
Note: This function can be called only once for each column. Therefore if this value needs to be used more than once, the data has to be stored in a variable, since all subsequent calls will return the wrong value. This is an ODBC limitation.
// [Index] =======================================================================================
Function DB_GetQuad(DatabaseID: LongInt; Column: Word): Int64;
Description
Returns the content of the specified DatabaseID column as a quad number. This command is only valid after a successful DB_FirstRow(), DB_PreviousRow() or DB_NextRow().
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use. DB_ColumnIndex() is available to get the index of a named column.
Return value
Returns the content of the column as a quad value.
Remarks
To determine the type of a column, DB_ColumnType() can be used.
Note: This function can be called only once for each column. Therefore if this value needs to be used more than once, the data has to be stored in a variable, since all subsequent calls will return the wrong value. This is an ODBC limitation.
// [Index] =======================================================================================
Function DB_GetString(DatabaseID: LongInt; Column: Word): PChar;
Description
Returns the content of the specified DatabaseID column as a string. This command is only valid after a successful DB_FirstRow(), DB_PreviousRow()DatabaseRow() or DB_NextRow().
Parameters
DatabaseID Specifies the database to use.
Column Specifies the column to use. DB_ColumnIndex() is available to get the index of a named column.
Return value
Returns the content of the column as a string.
Remarks
To determine the type of a column, DB_ColumnType() can be used.
Note: This function can be called only once for each column. Therefore if this value needs to be used more than once, the data has to be stored in a variable, since all subsequent calls will return the wrong value. This is an ODBC limitation.
// [Index] =======================================================================================
Function DB_IsDatabase(DatabaseID: LongInt): Boolean;
Description
This function evaluates if the given DatabaseID number is a valid and correctly-initialized database.
Parameters
DatabaseID Specifies the database to use.
Return value
Returns True if DatabaseID is a valid database connection and False otherwise.
Remarks
This function is bullet-proof and can be used with any value. If return value is True then the object is valid and initialized, otherwise it returns False. This is a good way to check that a database is ready to use.
// [Index] =======================================================================================
Function DB_NextDriver(): Boolean;
Description
Retrieves information about the next available database driver. This function must be called after DB_ExamineDrivers(). To get information about the current driver, DB_DriverName() and DB_DriverDescription() can be used.
Return value
If return value is False, no more drivers are available.
Remarks
This is an ODBC database specific command.
// [Index] =======================================================================================
Function DB_NextRow(DatabaseID: LongInt): Boolean;
Description
Retrieves information about the next database row in the DatabaseID. To access fields within a row, DB_GetLong(), DB_GetFloat() and DB_GetString() can be used.
Parameters
DatabaseID Specifies the database to use.
Return value
If return value is False, then no more rows are available (i.e. reached the end of the table).
// [Index] =======================================================================================
Function DB_Open(DatabaseID: LongInt; DatabaseName, User, Password: WideString; Plugin: TDatabasePluginType): LongInt;
Description
Opens a new database connection.
Parameters
DatabaseID Specifies the number by which to refere to the new database. DB_Any can be used to auto-generate this number.
DatabaseName Specifies the name of the database to open.
User Specifies the user name for the connection. This can be an empty string if no user is required.
Password Specifies the password for the connection. This can be an empty string if no password is required.
Plugin This parameter is used to specify the database plugin to use. Possible values are:
DB_Plugin_ODBC - The database will use ODBC backend (DB_UseODBC() has to be called first).
DB_Plugin_SQLite - The database will use SQLite backend (DB_UseSQLite() has to be called first).
DB_Plugin_PostgreSQL - The database will use PostgreSQL backend (DB_UsePostgreSQL() has to be called first).
DB_Plugin_MySQL - The database will use MySQL backend (DB_UseMySQL() has to be called first).
Return value
Returns nonzero if the database connection was established successfully and zero if not. Error information can be received with the DB_Error() command. If DB_Any was used for the DatabaseID parameter, then the generated connection number is returned.
// [Index] =======================================================================================
Function DB_PreviousRow(DatabaseID: LongInt): Boolean;
Description
Retrieves information about the previous database row in the DatabaseID. The flag DB_Cursor_Dynamic has to be specified to DB_QueryX() to have this command working. To access to fields inside a row, DB_GetLong(), DB_GetFloat(), DB_GetString() can be used.
Parameters
DatabaseID Specifies the database to use.
Return value
If return value is False, then no more rows are available (i.e. reached the start of the table).
Remarks
If this function returns False despite additional rows being available before the current one, then the ODBC driver does not support data retrieval in a backwards direction. It is not mandatory for an ODBC driver to support this function (unlike DB_NextRow()). Of course, if this function works, it will work on every computer using the same driver.
// [Index] =======================================================================================
Function DB_Query(DatabaseID: LongInt; Query: WideString): Boolean;
Description
Executes a SQL query on the given database. Only queries which doesn't change the database records are accepted ("SELECT"-like queries). To perform database modification, use DB_Update().
Parameters
DatabaseID Specifies the database to use.
Query Specifies the query to execute.
Return value
Returns True if the query was successful or False if it failed (due to a SQL error or a badly-formatted query).
Remarks
If the query has succeeded then DB_NextRow() can be used to list returned records (see the example below). In the event of an error, the error text can be retrieved with DB_Error(). It is safe to use DB_NextRow() even if the request doesn't return any records. To get the number of columns returned by the query, use DB_Columns().
Once the query results aren't needed anymore, DB_FinishQuery() has to be called to release all the query resources.
The query can contain placeholders for bind variables. Such variables must be set before calling the function using DB_SetString(), DB_SetLong() etc. After executing the query, the bound variables are cleared and have to be set again for future calls. The syntax for specifying bind variables in SQL is dependent on the database. The example below demonstrate the syntax.
See also
DB_QueryX()
Example
--- Code: (Pascal) --- // First, connect to a database with a 'players' table
If DB_Query(DatabaseID, 'SELECT * FROM players') then begin // Get all the records in the 'players' table
While DB_NextRow(DatabaseID) Do // Loop for each records
WriteLn(DB_GetString(DatabaseID, 0)); // Display the content of the first field
DB_FinishQuery(DatabaseID);
End;
--- End code ---
Example Bind variables with SQLite, MySQL and ODBC
--- Code: (Pascal) --- // SQLite, MySQL and ODBC shares the same syntax for bind variables. It is indicated by the '?' character
DB_SetString(DatabaseID, 0, 'Major''s Pain');
If DB_Query(DatabaseID, 'SELECT * FROM players WHERE name=?') Then Begin
// ...
End;
--- End code ---
Example Bind variables with PostgreSQL
--- Code: (Pascal) --- // PostgreSQL uses another syntax: $1, $2.. into the statement to indicate the undefined parameter
DB_SetString(DatabaseID, 0, 'Major''s Pain');
If DB_Query(DatabaseID, 'SELECT * FROM players WHERE name=$1') Then Begin
// ...
End;
--- End code ---
// [Index] =======================================================================================
Function DB_QueryX(DatabaseID: LongInt; Query: WideString; Cursor: TDatabaseCursorType): Boolean;
Description
Executes a SQL query on the given database. Only queries which doesn't change the database records are accepted ("SELECT"-like queries). To performs database modification, use DB_Update().
Parameters
DatabaseID Specifies the database to use.
Query Specifies the query to execute.
Cursor Specifies the database cursor type to use. Possible values are:
DB_Cursor_Static Performs the query to access the result in a sequential manner. It's not possible to rewind with DB_PreviousRow() or DB_FirstRow() on some drivers, but it is the faster way to get the data (it's default for the DB_Query()).
DB_Cursor_Dynamic Performs the query to access the result in a random manner using DB_PreviousRow() or DB_FirstRow(). It can be slower, or even unsupported on some drivers.
Return value
Returns True if the query was successful or False if it failed (due to a SQL error or a badly-formatted query).
Remarks
See DB_Query() for more information.
// [Index] =======================================================================================
Procedure DB_SetDouble(DatabaseID: LongInt; StatementIndex: Word; Value: Double);
Description
Set a double value as a bind variable for the next call to DB_Query() or DB_Update().
Parameters
DatabaseID Specifies the database to use.
StatementIndex Specifies the index of the bind variable within the statement. The first variable has index 0.
Value Specifies the value to use for the bind variable.
Remarks
Bind variables make constructing statements with variable data easier, because there is no need to add the data into the string. The statement string can contain the placeholders and the data is bound before executing the statement. This method also avoids vulnerabilities due to possible SQL injection which can be done if data (such as strings) is directly inserted in the statement text. Since the statement only contains the placeholder, there is no danger.
See DB_Query() and DB_Update() for examples how to specify bind variables in an SQL statement.
// [Index] =======================================================================================
Procedure DB_SetFloat(DatabaseID: LongInt; StatementIndex: Word; Value: Single);
Description
Set a float value as a bind variable for the next call to DB_Query() or DB_Update().
Parameters
DatabaseID Specifies the database to use.
StatementIndex Specifies the index of the bind variable within the statement. The first variable has index 0.
Value Specifies the value to use for the bind variable.
Remarks
Bind variables make constructing statements with variable data easier, because there is no need to add the data into the string. The statement string can contain the placeholders and the data is bound before executing the statement. This method also avoids vulnerabilities due to possible SQL injection which can be done if data (such as strings) is directly inserted in the statement text. Since the statement only contains the placeholder, there is no danger.
See DB_Query() and DB_Update() for examples how to specify bind variables in an SQL statement.
// [Index] =======================================================================================
Procedure DB_SetLong(DatabaseID: LongInt; StatementIndex: Word; Value: LongInt);
Description
Set a long value as a bind variable for the next call to DB_Query() or DB_Update().
Parameters
DatabaseID Specifies the database to use.
StatementIndex Specifies the index of the bind variable within the statement. The first variable has index 0.
Value Specifies the value to use for the bind variable.
Remarks
Bind variables make constructing statements with variable data easier, because there is no need to add the data into the string. The statement string can contain the placeholders and the data is bound before executing the statement. This method also avoids vulnerabilities due to possible SQL injection which can be done if data (such as strings) is directly inserted in the statement text. Since the statement only contains the placeholder, there is no danger.
See DB_Query() and DB_Update() for examples how to specify bind variables in an SQL statement.
// [Index] =======================================================================================
Procedure DB_SetNull(DatabaseID: LongInt; StatementIndex: Word);
Description
Set a bind variable to a NULL value for the next call to DB_Query() or DB_Update().
Parameters
DatabaseID Specifies the database to use.
StatementIndex Specifies the index of the bind variable within the statement. The first variable has index 0.
Remarks
Bind variables make constructing statements with variable data easier, because there is no need to add the data into the string. The statement string can contain the placeholders and the data is bound before executing the statement. This method also avoids vulnerabilities due to possible SQL injection which can be done if data (such as strings) is directly inserted in the statement text. Since the statement only contains the placeholder, there is no danger.
See DB_Query() and DB_Update() for examples how to specify bind variables in an SQL statement.
// [Index] =======================================================================================
Procedure DB_SetQuad(DatabaseID: LongInt; StatementIndex: Word; Value: Int64);
Description
Set a quad value as a bind variable for the next call to DB_Query() or DB_Update().
Parameters
DatabaseID Specifies the database to use.
StatementIndex Specifies the index of the bind variable within the statement. The first variable has index 0.
Value Specifies the value to use for the bind variable.
Remarks
Bind variables make constructing statements with variable data easier, because there is no need to add the data into the string. The statement string can contain the placeholders and the data is bound before executing the statement. This method also avoids vulnerabilities due to possible SQL injection which can be done if data (such as strings) is directly inserted in the statement text. Since the statement only contains the placeholder, there is no danger.
See DB_Query() and DB_Update() for examples how to specify bind variables in an SQL statement.
// [Index] =======================================================================================
Procedure DB_SetString(DatabaseID: LongInt; StatementIndex: Word; Value: WideString);
Description
Set a string value as a bind variable for the next call to DB_Query() or DB_Update().
Parameters
DatabaseID Specifies the database to use.
StatementIndex Specifies the index of the bind variable within the statement. The first variable has index 0.
Value Specifies the value to use for the bind variable.
Remarks
Bind variables make constructing statements with variable data easier, because there is no need to add the data into the string. The statement string can contain the placeholders and the data is bound before executing the statement. This method also avoids vulnerabilities due to possible SQL injection which can be done if data (such as strings) is directly inserted in the statement text. Since the statement only contains the placeholder, there is no danger.
See DB_Query() and DB_Update() for examples how to specify bind variables in an SQL statement.
// [Index] =======================================================================================
Function DB_Update(DatabaseID: LongInt; Query: WideString): Boolean;
Description
Executes a modification query on the given database. This command doesn't return any record. To perform a "SELECT"-like query, use DB_Query().
Parameters
DatabaseID Specifies the database to use.
Query Specifies the query to execute.
Return value
Returns True if the query was successful or False if it failed (due to a SQL error or a badly-formatted query).
Remarks
This function is similar to DB_Query() but is independent from the DB_NextRow() function. Therefore it's not possible to do a "SELECT"-like request with this function. This function is useful for updating records in the database. In the event of an error, the error text can be retrieved with DB_Error().
The update request can contain placeholders for bind variables. Such variables must be set before calling the function using DB_SetString(), DB_SetLong() etc. After executing the update, the bound variables are cleared and have to be set again for future calls. The syntax for specifying bind variables in SQL is dependent on the database. The example below demonstrate the syntax.
Example
--- Code: (Pascal) --- // First, connect to a database with a 'players' table
If DB_Query(DatabaseID, 'SELECT * FROM players') Then Begin // Get all the records in the 'players' table
While DB_NextRow(DatabaseID) Do // Loop for each records
// Update the 'checked' field for each records, assuming the 'id' field is
// the first one in the 'players' table
DB_Update(DatabaseID, 'UPDATE players SET checked=1 WHERE id='+DB_GetString(DatabaseID, 0));
DB_FinishQuery(DatabaseID);
End;
--- End code ---
Example Bind variables with SQLite, MySQL and ODBC
--- Code: (Pascal) --- // SQLite, MySQL and ODBC shares the same syntax for bind variables. It is indicated by the '?' character
DB_SetLong(0, 0, 1);
DB_SetString(0, 1, 'Major''s Pain');
DB_Update(0, 'UPDATE players SET checked=? WHERE id=?');
--- End code ---
Example Bind variables with PostgreSQL
--- Code: (Pascal) --- // PostgreSQL uses another syntax: $1, $2.. into the statement to indicate the undefined parameter
DB_SetLong(0, 0, 1);
DB_SetString(0, 1, 'Major''s Pain');
DB_Update(0, 'UPDATE players SET checked=$1 WHERE id=$2');
--- End code ---
// [Index] =======================================================================================
Procedure DB_UseMySQL(LibraryPath: WideString);
Description
Initialize the MySQL/MariaDB database environment for future use.
Parameters
LibraryPath File path of the dynamic library to use. As most Linux distribution ship with packaged "libmysql.so", it can be set to the correct path of the named library, so the "libmaria.so" doesn't have to be bundled with the script. If this parameter is not specified (by passing an empty string as a parameter value), "libmariadb.dll" (Windows) or "libmariadb.so" (Linux) bundled and placed near to "libdb.dll" or "libdb.so" will be used.
Example
--- Code: (Pascal) --- DB_UseMySQL('');
// You should have a server running on localhost
If DB_Open(0, 'host=localhost port=3306 dbname=test', 'mysql', 'mysql', DB_Plugin_MySQL) Then
WriteLn('Connected to MySQL')
Else
WriteLn('Connection failed: '+DB_Error);
--- End code ---
// [Index] =======================================================================================
Function DB_UseODBC(): Boolean;
Description
Initialize the ODBC database environment for future use. It attempts to load the ODBC driver and allocate the required resources.
Return value
If return value is False, then the ODBC driver is not available or is too old (ODBC 3.0 or higher is needed) and database functions should not be used.
Remarks
It is possible to obtain a list of available drivers by calling the function DB_ExamineDrivers().
Example
--- Code: (Pascal) --- If DB_UseODBC Then
If DB_Open(0, 'MySQL-ODBC', 'mysql', 'mysql', DB_Plugin_ODBC) Then
WriteLn('Connected to MySQL via ODBC')
Else
WriteLn('Connection failed: '+DB_Error)
Else
WriteLn('Failed to initialize ODBC: '+DB_Error);
--- End code ---
// [Index] =======================================================================================
Procedure DB_UsePostgreSQL();
Description
Initialize the PostgreSQL database environment for future use.
Example
--- Code: (Pascal) --- DB_UsePostgreSQL;
// You should have a server running on localhost
If DB_Open(0, 'host=localhost port=5432', 'postgres', 'postgres', DB_Plugin_PostgreSQL) Then
WriteLn('Connected to PostgreSQL')
Else
WriteLn('Connection failed: '+DB_Error);
--- End code ---
// [Index] =======================================================================================
Procedure DB_UseSQLite();
Description
Initialize the SQLite database environment for future use.
Example
--- Code: (Pascal) --- Var
FileStream: TFileStream;
FilePath: String;
Begin
FilePath := 'test.db';
If File.CheckAccess(FilePath) Then
If File.Exists(FilePath) Then Begin
WriteLn('File was found: '+FilePath)
End Else Begin
WriteLn('File was not found: '+FilePath);
Try
FileStream := File.CreateFileStream;
FileStream.SaveToFile(FilePath);
FileStream.Free;
WriteLn('File was created: '+FilePath);
Except
WriteLn('Exception: '+ExceptionParam);
Exit;
End;
End
Else Begin
WriteLn('Access denied: '+FilePath);
Exit;
End;
DB_UseSQLite;
If DB_Open(0, FilePath, '', '', DB_Plugin_SQLite) Then
WriteLn('Connected to SQLite')
Else
WriteLn('Connection failed: '+DB_Error);
End;
End;
--- End code ---
// [Index] =======================================================================================
DB_GetVersion(): Word;
Description
Returns the version number of the libdb library.
Example
--- Code: (Pascal) --- WriteLn('You are using "libdb v' + FormatFloat('#0.0', 0.1 * DB_GetVersion) + '" library.');
--- End code ---
P.S. "Stroke" icon set from the IconShock has been used to create an icon.
CurryWurst:
Nice release. Definitely much better than any custom native scripting solution. Much appreciated. Keep up the good work!
DorkeyDear:
Nice; Good job! I love seeing things like this posted.
jrgp:
Any chance on sharing the source, or at least telling us what you have the binaries written in?
Furthermore, any chance you'll implement prepared statements? I also don't seem to be seeing a function that escapes a string's quotes. Without either prepared statements or a function akin to mysql_real_escape_string, you're vulnerable to sql injection attempts.
SyavX:
As I already answered to CurryWurst in IRC:
There is no mysql_real_escape_string function or smth since this lib supports not only MySQL (via ODBC + MySQL ODBC driver), but also SQLite, PostreSQL, etc. which have different rules and sets of symbols to be escaped.
So, preparing input data is on users' responsibility.
As for the source code, it's simply a proxy functions for the native database functions of PB:
--- Code: ---UseODBCDatabase()
UseSQLiteDatabase()
UsePostgreSQLDatabase()
ProcedureCDLL DB_Close(DatabaseID.w)
CloseDatabase(DatabaseID)
EndProcedure
ProcedureCDLL.s DB_ColumnName(DatabaseID.w, Column.w)
ProcedureReturn DatabaseColumnName(DatabaseID, Column)
EndProcedure
ProcedureCDLL.w DB_ColumnSize(DatabaseID.w, Column.w)
ProcedureReturn DatabaseColumnSize(DatabaseID, Column)
EndProcedure
ProcedureCDLL.w DB_ColumnType(DatabaseID.w, Column.w)
ProcedureReturn DatabaseColumnType(DatabaseID, Column)
EndProcedure
ProcedureCDLL.w DB_Columns(DatabaseID.w)
ProcedureReturn DatabaseColumns(DatabaseID)
EndProcedure
ProcedureCDLL.s DB_Error()
ProcedureReturn DatabaseError()
EndProcedure
ProcedureCDLL.w DB_Query(DatabaseID.w, Query.s)
ProcedureReturn DatabaseQuery(DatabaseID, Query)
EndProcedure
ProcedureCDLL.w DB_Update(DatabaseID.w, Query.s)
ProcedureReturn DatabaseUpdate(DatabaseID, Query)
EndProcedure
ProcedureCDLL DB_FinishQuery(DatabaseID.w)
FinishDatabaseQuery(DatabaseID)
EndProcedure
ProcedureCDLL.w DB_FirstRow(DatabaseID.w)
ProcedureReturn FirstDatabaseRow(DatabaseID)
EndProcedure
ProcedureCDLL.d DB_GetDouble(DatabaseID.w, Column.w)
ProcedureReturn GetDatabaseDouble(DatabaseID, Column)
EndProcedure
ProcedureCDLL.f DB_GetFloat(DatabaseID.w, Column.w)
; Returns Pascal's "Single" values
ProcedureReturn GetDatabaseFloat(DatabaseID, Column)
EndProcedure
ProcedureCDLL.l DB_GetLong(DatabaseID.w, Column.w)
; Returns Pascal's "LongInt" value
ProcedureReturn GetDatabaseLong(DatabaseID, Column)
EndProcedure
ProcedureCDLL.s DB_GetString(DatabaseID.w, Column.w)
ProcedureReturn GetDatabaseString(DatabaseID, Column)
EndProcedure
ProcedureCDLL.w DB_IsDatabase(DatabaseID.w)
ProcedureReturn IsDatabase(DatabaseID)
EndProcedure
ProcedureCDLL.w DB_NextRow(DatabaseID.w)
ProcedureReturn NextDatabaseRow(DatabaseID)
EndProcedure
ProcedureCDLL.w DB_Open(DatabaseID.w, DatabaseName.s, User.s, Password.s, Plugin.w)
ProcedureReturn OpenDatabase(DatabaseID, DatabaseName, User, Password, Plugin)
EndProcedure
ProcedureCDLL.w DB_ExamineDrivers()
ProcedureReturn ExamineDatabaseDrivers()
EndProcedure
ProcedureCDLL.w DB_NextDriver()
ProcedureReturn NextDatabaseDriver()
EndProcedure
ProcedureCDLL.s DB_DriverDescription()
ProcedureReturn DatabaseDriverDescription()
EndProcedure
ProcedureCDLL.s DB_DriverName()
ProcedureReturn DatabaseDriverName()
EndProcedure
--- End code ---
Navigation
[0] Message Index
[#] Next page
Go to full version