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:
sudo apt install libodbc1:i386 libltdl7:i386There 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_MySQLDescriptionMySQL 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/):
[mysqld]
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock
RemarksSee also
DB_UseMySQL().
// [Index] =======================================================================================
DB_Plugin_ODBCDescriptionODBC 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().
RemarksSee also
DB_UseODBC().
// [Index] =======================================================================================
DB_Plugin_PostgreSQLDescriptionPostgreSQL 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.
RemarksSee also
DB_UsePostgreSQL().
// [Index] =======================================================================================
DB_Plugin_SQLiteDescriptionSQLite 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.
RemarksSee also
DB_UseSQLite().
// =====================================
// F U N C T I O N S
// =====================================
// [Index]
=======================================================================================
Function DB_AffectedRows(DatabaseID: LongInt): LongInt;DescriptionReturns the number of rows affected by the last
DB_Update() operation.
ParametersDatabaseID Specifies the database to use.
Return valueReturns the number of the affected rows.
// [Index]
=======================================================================================
Function DB_CheckNull(DatabaseID: LongInt; Column: Word): Boolean;DescriptionChecks 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().
ParametersDatabaseID Specifies the database to use.
Column Specifies the column to use.
DB_ColumnIndex() is available to get the index of a named column.
Return valueReturns
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
// 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);
// [Index] =======================================================================================
Function DB_ExamineDrivers(): Boolean;DescriptionExamines the database drivers available on the system.
Return valueIf ODBC isn't installed or no drivers are available, it returns
False, otherwise
DB_NextDriver() can be used to list all the drivers.
RemarksThis is an ODBC database specific command.
Example 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!');
// [Index] =======================================================================================
Procedure DB_FinishQuery(DatabaseID: LongInt);DescriptionFinish the current database SQL query and release its associated resources. Query related functions like
DB_FirstRow() or
DB_NextRow() can't be used anymore.
ParametersDatabaseID Specifies the database to use.
Example // 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;
// [Index] =======================================================================================
Function DB_FirstRow(DatabaseID: LongInt): Boolean;DescriptionRetrieves 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.
ParametersDatabaseID Specifies the database to use.
Return valueReturns
False, then no row is available.
RemarksTo access fields within a row,
DB_GetLong(),
DB_GetFloat() and
DB_GetString() can be used.
// [Index] =======================================================================================
Function DB_GetDouble(DatabaseID: LongInt; Column: Word): Double;DescriptionReturns 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().
ParametersDatabaseID Specifies the database to use.
Column Specifies the column to use.
DB_ColumnIndex() is available to get the index of a named column.
Return valueReturns a double precision floating-point value.
RemarksTo 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;DescriptionReturns 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().
ParametersDatabaseID Specifies the database to use.
Column Specifies the column to use.
DB_ColumnIndex() is available to get the index of a named column.
Return valueReturns a single precision floating-point value.
RemarksTo 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;DescriptionReturns 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().
ParametersDatabaseID Specifies the database to use.
Column Specifies the column to use.
DB_ColumnIndex() is available to get the index of a named column.
Return valueReturns the content of the column as an integer value.
RemarksTo 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;DescriptionReturns 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().
ParametersDatabaseID Specifies the database to use.
Column Specifies the column to use.
DB_ColumnIndex() is available to get the index of a named column.
Return valueReturns the content of the column as a quad value.
RemarksTo 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;DescriptionReturns 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().
ParametersDatabaseID Specifies the database to use.
Column Specifies the column to use.
DB_ColumnIndex() is available to get the index of a named column.
Return valueReturns the content of the column as a string.
RemarksTo 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;DescriptionThis function evaluates if the given
DatabaseID number is a valid and correctly-initialized database.
ParametersDatabaseID Specifies the database to use.
Return valueReturns
True if
DatabaseID is a valid database connection and
False otherwise.
RemarksThis 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;DescriptionRetrieves 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 valueIf return value is
False, no more drivers are available.
RemarksThis is an ODBC database specific command.
// [Index] =======================================================================================
Function DB_NextRow(DatabaseID: LongInt): Boolean;DescriptionRetrieves 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.
ParametersDatabaseID Specifies the database to use.
Return valueIf 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;DescriptionOpens a new database connection.
ParametersDatabaseID 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 valueReturns 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;DescriptionRetrieves 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.
ParametersDatabaseID Specifies the database to use.
Return valueIf return value is
False, then no more rows are available (i.e. reached the start of the table).
RemarksIf 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;DescriptionExecutes 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().
ParametersDatabaseID Specifies the database to use.
Query Specifies the query to execute.
Return valueReturns
True if the query was successful or
False if it failed (due to a SQL error or a badly-formatted query).
RemarksIf 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 alsoDB_QueryX()Example // 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;
Example Bind variables with SQLite, MySQL and ODBC
// 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;
Example Bind variables with PostgreSQL
// 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;
// [Index] =======================================================================================
Function DB_QueryX(DatabaseID: LongInt; Query: WideString; Cursor: TDatabaseCursorType): Boolean;DescriptionExecutes 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().
ParametersDatabaseID 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 valueReturns
True if the query was successful or
False if it failed (due to a SQL error or a badly-formatted query).
RemarksSee
DB_Query() for more information.
// [Index] =======================================================================================
Procedure DB_SetDouble(DatabaseID: LongInt; StatementIndex: Word; Value: Double);DescriptionSet a double value as a bind variable for the next call to
DB_Query() or
DB_Update().
ParametersDatabaseID 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.
RemarksBind 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);DescriptionSet a float value as a bind variable for the next call to
DB_Query() or
DB_Update().
ParametersDatabaseID 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.
RemarksBind 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);DescriptionSet a long value as a bind variable for the next call to
DB_Query() or
DB_Update().
ParametersDatabaseID 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.
RemarksBind 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);DescriptionSet a bind variable to a NULL value for the next call to
DB_Query() or
DB_Update().
ParametersDatabaseID Specifies the database to use.
StatementIndex Specifies the index of the bind variable within the statement. The first variable has index 0.
RemarksBind 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);DescriptionSet a quad value as a bind variable for the next call to
DB_Query() or
DB_Update().
ParametersDatabaseID 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.
RemarksBind 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);DescriptionSet a string value as a bind variable for the next call to
DB_Query() or
DB_Update().
ParametersDatabaseID 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.
RemarksBind 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;DescriptionExecutes a modification query on the given database. This command doesn't return any record. To perform a "SELECT"-like query, use
DB_Query().
ParametersDatabaseID Specifies the database to use.
Query Specifies the query to execute.
Return valueReturns
True if the query was successful or
False if it failed (due to a SQL error or a badly-formatted query).
RemarksThis 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 // 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;
Example Bind variables with SQLite, MySQL and ODBC
// 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=?');
Example Bind variables with PostgreSQL
// 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');
// [Index] =======================================================================================
Procedure DB_UseMySQL(LibraryPath: WideString);DescriptionInitialize the MySQL/MariaDB database environment for future use.
ParametersLibraryPath 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 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);
// [Index] =======================================================================================
Function DB_UseODBC(): Boolean;DescriptionInitialize the ODBC database environment for future use. It attempts to load the ODBC driver and allocate the required resources.
Return valueIf 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.
RemarksIt is possible to obtain a list of available drivers by calling the function
DB_ExamineDrivers().
Example 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);
// [Index] =======================================================================================
Procedure DB_UsePostgreSQL();DescriptionInitialize the PostgreSQL database environment for future use.
Example 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);
// [Index] =======================================================================================
Procedure DB_UseSQLite();DescriptionInitialize the SQLite database environment for future use.
Example 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;
// [Index] =======================================================================================
DB_GetVersion(): Word;DescriptionReturns the version number of the
libdb library.
Example WriteLn('You are using "libdb v' + FormatFloat('#0.0', 0.1 * DB_GetVersion) + '" library.');
P.S. "Stroke" icon set from the IconShock has been used to create an icon.
