Author Topic: libdb (SQLite / MySQL / MariaDB / PostgreSQL / ODBC library)  (Read 62893 times)

0 Members and 1 Guest are viewing this topic.

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338

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) [Select]
sudo apt install libodbc1:i386 libltdl7:i386
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: [Select]
[mysqld]
socket=/tmp/mysql.sock

[client]
socket=/tmp/mysql.sock

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) [Select]
  // 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;


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) [Select]
  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);


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) [Select]
  // 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;


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) [Select]
  // 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
Code: (Pascal) [Select]
  // 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
Code: (Pascal) [Select]
  // 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;


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) [Select]
  // 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
Code: (Pascal) [Select]
  // 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
Code: (Pascal) [Select]
  // 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);


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) [Select]
  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;


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) [Select]
  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();


Description
Initialize the PostgreSQL database environment for future use.

Example
Code: (Pascal) [Select]
  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();


Description
Initialize the SQLite database environment for future use.

Example
Code: (Pascal) [Select]
  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;


Description
Returns the version number of the libdb library.

Example
Code: (Pascal) [Select]
  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.
« Last Edit: February 05, 2019, 04:05:37 pm by SyavX »

Offline CurryWurst

  • Camper
  • ***
  • Posts: 265
    • Soldat Global Account System
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #1 on: July 06, 2012, 11:22:18 am »
Nice release. Definitely much better than any custom native scripting solution. Much appreciated. Keep up the good work!
Soldat Global Account System: #soldat.sgas @ quakenet

Offline DorkeyDear

  • Veteran
  • *****
  • Posts: 1507
  • I also go by Curt or menturi
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #2 on: July 06, 2012, 11:25:45 am »
Nice; Good job! I love seeing things like this posted.

Offline jrgp

  • Administrator
  • Flamebow Warrior
  • *****
  • Posts: 5037
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #3 on: July 06, 2012, 02:41:07 pm »
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.
« Last Edit: July 08, 2012, 12:44:08 am by jrgp »
There are other worlds than these

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #4 on: July 07, 2012, 07:50:38 am »
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: [Select]
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
« Last Edit: July 07, 2012, 07:58:21 am by SyavX »

Offline jrgp

  • Administrator
  • Flamebow Warrior
  • *****
  • Posts: 5037
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #5 on: July 07, 2012, 02:48:20 pm »
So, preparing input data is on users' responsibility.

Sorry, but that's easily the worst answer you could have given me.

Since you have no form of SQL injection built in (and you completely blew off my suggestion of prepared statements), your code here is completely useless as there *will* be cases of injection since odds are people will not be willing to properly sanitize input prior to including it in their queries.
There are other worlds than these

Offline ExHunter

  • Inactive Soldat Developer
  • Soldier
  • ******
  • Posts: 154
  • Speedy go!
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #6 on: July 17, 2012, 02:55:45 pm »
Well,

it is still possible to do by yourself prepared statements for the script. Everybody who works with databases and can script, can make a workaround for this. I am happy and thankful, that he did all that work.

Also this "worst answer" is always true. Even if he implement such a function like mysql_real_escape_string, it is still in the users' responsibility to use it.

Offline Mighty

  • Camper
  • ***
  • Posts: 276
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #7 on: November 12, 2012, 02:25:34 pm »
Ok, I have some problems with this (or with externals in general). Need help.
I'm at Win XP SP3, wanting to make a script using your libdb.

So I downloaded, extracted and tried launching with -safe 0.
Code: [Select]
Hit CTRL+C to Exit
 Please command the server using the Soldat Admin program
Welcome to Soldat 1.6.3
 Server name: Soldat Server
 Server PID: 4000
 [*] ScriptCore v2.4 loaded! Found 2 scripts...
 [*] Compiling CWSystem2 -> CWSystem2.pas...
 [*] Compiling libdb -> libdb_windows.pas...
Server Encountered an error:
Unable to find variable
Shutting down server...
Shutt

I'm new to databases overall but I think it's a good motivation to learn.

Quote
Plus, as it's stated here, there could be a need to do an export of LD_LIBRARY_PATH.
Quote
You also need to install libiodbc dependency!
Should I do these on Windows? If so, how, because it seems I can't figure it out myself...
Oh, and the link is dead. The one to the devs wiki
xFire: macmil        e-mail: macekmil@gmail.com
My scripts: Accuracy Script       Flashbang       Punishments GUID
            CatchMe Gamemod       AntiFake
            CW System             AntiFakeGUID

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #8 on: November 12, 2012, 03:57:55 pm »
Make sure that libdb.dll is placed near (extracted to the same dir as...) the soldatserver.exe file you are trying to run.

Should I do these on Windows? If so, how, because it seems I can't figure it out myself...
No, you don't. That's a Linux-related notes.

P.S. I had no problems with WinXP SP3 hosted on VBox.

Offline Mighty

  • Camper
  • ***
  • Posts: 276
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #9 on: November 13, 2012, 01:03:11 pm »
Ok now I lost whole faith in myself. :p
I thought I unpacked tho whole .zip, but sadly I had only "scripts" folder selected when I did this.
Damn I'm dumb. Nevermind, everything's fine.
xFire: macmil        e-mail: macekmil@gmail.com
My scripts: Accuracy Script       Flashbang       Punishments GUID
            CatchMe Gamemod       AntiFake
            CW System             AntiFakeGUID

Offline Mighty

  • Camper
  • ***
  • Posts: 276
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #10 on: November 21, 2012, 02:38:54 pm »
OK, further problems. Linux this time.

 - Ubuntu 12.04 x84_64, FAT32
 - Installed 32-bit libraries
 - All the files have maximum permissions
 - I have installed libiodbc
 - echo $LD_LIBRARY_PATH gives me ~/server/, which is exactly where soldatserver and libdb.so are
 - running server with -safe 0 option
 - using correct .pas file with .so library instead of .dll

Still...
Code: [Select]

             -= Soldat Dedicated Server 1.6.3 - 2.7.3 =-

----------------------------------------------------------------
         Soldat Dedicated Server initializing...
----------------------------------------------------------------

   Need help running your server?
   IRC: #soldat.devs @ irc.quakenet.org

   ---> http://forums.soldat.pl/

   Additional parameters:
   ./soldatserver -p PORT -l MAXPLAYERS -k PASSWORD
   Example: ./soldatserver -p 23073 -l 16 -k "my pass"

Using Indy 9


 Hit CTRL+C to Exit
 Please command the server using the Soldat Admin program
Welcome to Soldat 1.6.3
 Server name: Soldat Server
 Server PID: 11627
 [*] ScriptCore v2.4 loaded! Found 1 scripts...
 [*] Compiling libdb -> libdb_linux.pas...
Server Encountered an error:
Unable to find variable
Shutting down server...
Shutting down admin server...
Shutting down ScriptCore...

If mods find this post connected with using .dlls more than just libdb, let me know/move to appropriate.
xFire: macmil        e-mail: macekmil@gmail.com
My scripts: Accuracy Script       Flashbang       Punishments GUID
            CatchMe Gamemod       AntiFake
            CW System             AntiFakeGUID

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #11 on: November 21, 2012, 07:08:13 pm »
Try to re-install libiodbc (it conflicts with unixODBC and may require removal of some dependencies):
Code: [Select]
apt-get install libiodbc2
« Last Edit: November 21, 2012, 07:23:52 pm by SyavX »

Offline Mighty

  • Camper
  • ***
  • Posts: 276
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #12 on: November 28, 2012, 05:00:09 pm »
Even more.

So I had a problem making soldatserver see a .so file on Debian, but it worked after editing /etc/ld.so.conf, adding a path to the .so file. So Soldat sees it now, but...

 - proper permissions
 - iodbc and libiodbc (re)installed
 - drivers() says there are no, but that's not the case, since it says so on my local machine and still everything works just fine.

Code: [Select]
[*] ScriptCore v2.4 loaded! Found 3 scripts...
(22:35:30)   ...
(22:35:30)  [*] Compiling CWSystem2 -> libdb_linux.pas...
(22:35:30)  [*] Compilation Complete.
(22:35:31) /create1 (83.6.220.140)
(22:35:31) Failed to open database

I run out of ideas. Completely.
xFire: macmil        e-mail: macekmil@gmail.com
My scripts: Accuracy Script       Flashbang       Punishments GUID
            CatchMe Gamemod       AntiFake
            CW System             AntiFakeGUID

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #13 on: November 29, 2012, 03:36:41 pm »
I assume you've changed an example, as there was no "Failed to open database" message in the original script. So, I need to see your code to say something more specific.

If you just added an error output for the if DB_Open() statement:
Code: [Select]
if DB_Open(0, db_path, '', '', DB_Plugin_SQLite) <> 0 then
begin
WriteLn('Database "'+db_path+'" opened...');
CheckDatabaseUpdate(0, 'CREATE TABLE IF NOT EXISTS test(id INTEGER PRIMARY KEY, name STRING, value INTEGER);');
CheckDatabaseUpdate(0, 'INSERT INTO test(name, value) VALUES("name1", 5);');
CheckDatabaseUpdate(0, 'INSERT INTO test(name, value) VALUES("name2", 10);');
CheckDatabaseUpdate(0, 'INSERT INTO test(name, value) VALUES(''name3'', 15);');
WriteLn('Values inserted...');
DB_Close(0);
WriteLn('Database "'+db_path+'" closed...');
end
else
WriteLn('Failed to open database');
then you should try to test file creating/reading with scripts on your server. Here is a simple example: WriteFile.
I suppose that database file you are trying to open was not created. As you can see, there is no error check after using of WriteFile() in my example:
Code: [Select]
if Not FileExists(db_path) then
if WriteFile(db_path,'') then
WriteLn('File "'+db_path+'" created...');

Offline Mighty

  • Camper
  • ***
  • Posts: 276
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #14 on: November 29, 2012, 03:43:22 pm »
Yes, I just added an "else" to see that it actually tries to execute. It didn't say that the file was created because that was not the first run, I've been testing with WriteFile and ReadFile, both work.

Here's the code anyway:
Quote
procedure create_db1();
var
  db_path: String;
begin
   db_path:= 'test.db';
   //creating a new file if not exists
   if Not FileExists(db_path) then
      if WriteFile(db_path,'') then
         WriteLn('File "'+db_path+'" created...')
        else
            WriteLn('Failed to create file');
   
   if DB_Open(0, db_path, '', '', DB_Plugin_SQLite) <> 0 then
   begin
      WriteLn('Database "'+db_path+'" opened...');
      CheckDatabaseUpdate(0, 'CREATE TABLE IF NOT EXISTS test(id INTEGER PRIMARY KEY, name STRING, value INTEGER);');
      CheckDatabaseUpdate(0, 'INSERT INTO test(name, value) VALUES("name1", 5);');
      CheckDatabaseUpdate(0, 'INSERT INTO test(name, value) VALUES("name2", 10);');
      CheckDatabaseUpdate(0, 'INSERT INTO test(name, value) VALUES(''name3'', 15);');
      WriteLn('Values inserted...');
      DB_Close(0);
      writeln('Database "'+db_path+'" closed...');
   end
    else WriteLn('Failed to open database')
end;   
xFire: macmil        e-mail: macekmil@gmail.com
My scripts: Accuracy Script       Flashbang       Punishments GUID
            CatchMe Gamemod       AntiFake
            CW System             AntiFakeGUID

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #15 on: November 29, 2012, 05:24:13 pm »
I don't know... Try to comment out:
Code: [Select]
//if Not FileExists(db_path) thenso the database file will be overwritten on every call of create_db1().

I've attached a test.db (result of create_db2()), so you could test it with /query1 & /query2 commands.

Offline Mighty

  • Camper
  • ***
  • Posts: 276
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #16 on: November 30, 2012, 04:30:28 am »
Result:
Code: [Select]
(10:23:32) /recompile CWSystem2 (83.9.97.128)
(10:23:32)  [*] Compiling CWSystem2 -> libdb_linux.pas...
(10:23:32)  [*] Compilation Complete.
(10:23:37) /create1 (83.9.97.128)
(10:23:37) File "test.db" created...
(10:23:37) Failed to open database

Could you list all stuff that mightbe needed to run this? Just to make sure I have everything
« Last Edit: November 30, 2012, 04:36:44 am by Mighty »
xFire: macmil        e-mail: macekmil@gmail.com
My scripts: Accuracy Script       Flashbang       Punishments GUID
            CatchMe Gamemod       AntiFake
            CW System             AntiFakeGUID

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #17 on: November 30, 2012, 05:48:47 am »
Windows:
  • nothing

Linux:
  • libiodbc

P.S. Have you tried to open/query database attached to my previous post?
« Last Edit: November 30, 2012, 06:14:55 am by SyavX »

Offline Mighty

  • Camper
  • ***
  • Posts: 276
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #18 on: November 30, 2012, 06:31:34 am »
When i try to install libiodbc it says that the package is not found, but I have installed libiodbc2.

I tried:
Code: [Select]
(12:16:29) /query1 (83.9.97.128)
(12:16:29) Can not open database "test.db"!
xFire: macmil        e-mail: macekmil@gmail.com
My scripts: Accuracy Script       Flashbang       Punishments GUID
            CatchMe Gamemod       AntiFake
            CW System             AntiFakeGUID

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #19 on: November 30, 2012, 07:06:46 am »
Try to use DB_Error() to get an error information:
Code: [Select]
...
else
begin
WriteLn('Failed to open database');
WriteLn(DB_Error());
end;
...

Offline Mighty

  • Camper
  • ***
  • Posts: 276
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #20 on: November 30, 2012, 08:01:19 am »
Sadly, empty string :(
xFire: macmil        e-mail: macekmil@gmail.com
My scripts: Accuracy Script       Flashbang       Punishments GUID
            CatchMe Gamemod       AntiFake
            CW System             AntiFakeGUID

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #21 on: November 30, 2012, 10:51:21 am »
Now I'm out of ideas too...

Offline ExHunter

  • Inactive Soldat Developer
  • Soldier
  • ******
  • Posts: 154
  • Speedy go!
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #22 on: December 11, 2012, 03:43:03 pm »
Hey,

We also encountered some problems with the ODBC drivers/connectors... We are able to run the server with the library, but we do not get any drivers installed..

System info and what we tried to do to get it work:
Quote
CentOS 5.8
libiodbc completely installed (also re-installed)
$LD_LIBRARY_PATH correct
mysql-connector-odbc installed 3.51 64b
tested with
mysql-connector-odbc 5.2 64b
3.51 32b and 5.2 32b

looking for drivers:
Quote
(20:12:17) /drivers (*.*.*.*)
(20:12:17) No ODBC drivers installed!

trying to connect to a db DB_error() shows:
Quote
(20:12:22) Data source name not found and no default driver specified. Driver could not be loaded


What possibilities do we have to get the driver loaded? I would appreciate any help, since we cannot think of anything more than that now. Thanks ;)

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #23 on: December 12, 2012, 03:34:23 am »
I've updated the libdb (check attachments in the 1st post), so it's Linux version requires unixODBC instead of iODBC now. Maybe it would help to solve the problems mentioned above.

ExHunter, unfortunately I'm not experienced with Linux enough, but I could suggest you to read this first: Configuring MySQL Connector/ODBC
and, since libdb was updated, this: Guide to getting unixODBC and MySQL/MyODBC working.

Also, you could try Mr's SoldatSOCI.
« Last Edit: December 12, 2012, 08:10:59 am by SyavX »

Offline Szaman

  • Soldier
  • **
  • Posts: 145
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #24 on: August 10, 2013, 05:16:58 pm »
Hi,

I have some problems with compiling libdb on server (1.6.5 version, Debian hosted) :(

When I try to compile it there is an error:
►   00:13:31 
  • Compiling libdb-0.2 -> libdb_linux.pas...

►   00:13:31 
  • libdb-0.2 -> [Error] (23:1): Semicolon (';') expected

►   00:13:31 
  • Disabling libdb-0.2


Line 23 is:
Function DB_ColumnName(DatabaseID, Column: Integer): PChar;

Does anybody had the same problem and solved it ?

Thanks in advace for the response.

Best regards,
Peter.

Offline Falcon`

  • Flagrunner
  • ****
  • Posts: 792
  • A wanted lagger
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #25 on: August 10, 2013, 07:05:24 pm »
you have safemode off. Launch soldatserver with -safe 0 switch.
If you're not paying for something, you're not the customer; you're the product being sold.
- Andrew Lewis

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.

Offline Szaman

  • Soldier
  • **
  • Posts: 145
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #26 on: August 11, 2013, 04:44:54 am »
Oh... such a stupid bug  :-\ Sorry for wasting your time  :(

And thank you very much for explaining that error :) Now everything works :)

Offline Leader

  • Major(1)
  • Posts: 9
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #27 on: January 14, 2015, 01:02:29 am »
Hello there! Ive got a problem! I try to learn database scripting. I started it connecting databasing with script core2 and it works with safe 0. But Id like to move to script core 3 and I cannot. When I recompile trial script I get an error like when it would be sc2 with safe on "Semicolon expected". Could someone tell me what should I install/change/remove or anything?

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #28 on: January 14, 2015, 04:01:36 am »
Try to enable external DLLs in ScriptCore3 config file: AllowDlls=1

Offline Nedi

  • Major(1)
  • Posts: 47
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #29 on: May 06, 2015, 07:28:38 am »
Hello,
I have some problems with running soldatserver with this lib:

Code: [Select]
             -= Soldat Dedicated Server 1.6.8 - 2.7.8 =-

----------------------------------------------------------------
         Soldat Dedicated Server initializing...
----------------------------------------------------------------

   Need help running your server?
   IRC: #soldat.devs @ irc.quakenet.org

   ---> http://forums.soldat.pl/

   Additional parameters:
   ./soldatserver -p PORT -l MAXPLAYERS -k PASSWORD
   Example: ./soldatserver -p 23073 -l 16 -k "my pass"


 Compiled with FreePascal (Indy 10)

 Hit CTRL+C to Exit
 Please command the server using the Soldat Admin program
Welcome to Soldat 1.6.8
 Server name: Soldat Server
 Server PID: 6217
 [*] Compiling stats -> libdb_linux.pas...
 [*] Compiling stats -> stats.pas...
 [*] stats -> Cannot Import dll:libdb-0.2.so
 [*] Disabling stats
----------------------------------------------------------------
Mapslist loaded mapslist
ctf_nothard
 Server IP: 0.0.0.0 Port: 23073
 Connection for file server started. Port: 23083
Registering server @ 67.23.118.179


I have debian 7 64bit
libdb-0.2.so is placed in soldatserver root folder
also in:
/var/lib/libdb-0.2.so
/etc/lib/libdb-0.2.so
/lib64/libdb-0.2.so
/lib/libdb-0.2.so
/usr/local/lib/libdb-0.2.so
/usr/lib/libdb-0.2.so

Do you have any ideas why it doesn't work?

Offline jrgp

  • Administrator
  • Flamebow Warrior
  • *****
  • Posts: 5037
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #30 on: May 06, 2015, 06:12:13 pm »
Hello,
I have some problems with running soldatserver with this lib:

Code: [Select]
             -= Soldat Dedicated Server 1.6.8 - 2.7.8 =-

----------------------------------------------------------------
         Soldat Dedicated Server initializing...
----------------------------------------------------------------

   Need help running your server?
   IRC: #soldat.devs @ irc.quakenet.org

   ---> http://forums.soldat.pl/

   Additional parameters:
   ./soldatserver -p PORT -l MAXPLAYERS -k PASSWORD
   Example: ./soldatserver -p 23073 -l 16 -k "my pass"


 Compiled with FreePascal (Indy 10)

 Hit CTRL+C to Exit
 Please command the server using the Soldat Admin program
Welcome to Soldat 1.6.8
 Server name: Soldat Server
 Server PID: 6217
 [*] Compiling stats -> libdb_linux.pas...
 [*] Compiling stats -> stats.pas...
 [*] stats -> Cannot Import dll:libdb-0.2.so
 [*] Disabling stats
----------------------------------------------------------------
Mapslist loaded mapslist
ctf_nothard
 Server IP: 0.0.0.0 Port: 23073
 Connection for file server started. Port: 23083
Registering server @ 67.23.118.179


I have debian 7 64bit
libdb-0.2.so is placed in soldatserver root folder
also in:
/var/lib/libdb-0.2.so
/etc/lib/libdb-0.2.so
/lib64/libdb-0.2.so
/lib/libdb-0.2.so
/usr/local/lib/libdb-0.2.so
/usr/lib/libdb-0.2.so

Do you have any ideas why it doesn't work?

Can you paste the output of "ldd libdb-0.2.so" in your soldat's root folder?
There are other worlds than these

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #31 on: May 07, 2015, 05:36:20 am »
Hello,
I have some problems with running soldatserver with this lib:

Seems like there is something wrong with server compiled with Indy 10, try to run soldatserver_legacy

Btw, I've updated notes section to make it more specific about dependencies.

Offline Nedi

  • Major(1)
  • Posts: 47
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #32 on: May 07, 2015, 11:35:53 am »
Can you paste the output of "ldd libdb-0.2.so" in your soldat's root folder?
There was:
Code: [Select]
libodbc.so.1 => not found
Btw, I've updated notes section to make it more specific about dependencies.
It works now

Thanks

Offline Walls

  • Major(1)
  • Posts: 5
Re: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)
« Reply #33 on: June 17, 2015, 08:24:19 am »
Yeah, I was going to make a note on that all deps must be 32bits, took quite a while for exhunter and me to get it working on our servers.
So it shall work on other distros as long as you get all odbc and deps on 32b.

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 338
Re: libdb (SQLite / MySQL / MariaDB / PostgreSQL / ODBC library)
« Reply #34 on: January 27, 2019, 09:09:50 am »
Version 0.3 (27 Jan 2019):
+ Added MySQL support (see DB_Plugin_MySQL)
+ Added SQL bind variables (see DB_SetString(), DB_SetLong() etc.)
+ Added things which I can't recall...
% Modified library bindings and example code (rewritten with ScriptCore3 syntax)
% Modified some bindings and internal code which broke backward compatibility, see description below for more info

1. Most functions that were returning non zero on success and 0 otherwise are returning Boolean now (e.g. DB_NextRow(), DB_Query() etc.)
For example:
Code: (Pascal) [Select]
if DB_NextRow(0) <> 0 then
// and
if DB_NextRow(0) = 0 then

should be replaced with
Code: (Pascal) [Select]
if DB_NextRow(0) then
// and
if not DB_NextRow(0) then

2. You should now call one of DB_UseMySQL(), DB_UseODBC(), DB_UsePostgreSQL(), DB_UseSQLite() procedures before using any functions related to the corresponding database plugins.

3. Database plugins and column type constants are declared as enumerations now:
Code: (Pascal) [Select]
{ Database plugins enumeration for the DB_Open() function }
type TDatabasePluginType = (DB_Plugin_Undefined, DB_Plugin_ODBC, DB_Plugin_SQLite, DB_Plugin_PostgreSQL, DB_Plugin_MySQL);

{ Database column types enumeration for the DB_ColumnType() function }
type TDatabaseColumnType = (DB_Type_Undefined, DB_Type_Long, DB_Type_String, DB_Type_Float, DB_Type_Double, DB_Type_Quad);


See first post for the download link...
« Last Edit: February 05, 2019, 04:00:15 pm by SyavX »

Offline jrgp

  • Administrator
  • Flamebow Warrior
  • *****
  • Posts: 5037
Re: libdb (SQLite / MySQL / MariaDB / PostgreSQL / ODBC library)
« Reply #35 on: January 30, 2019, 07:31:12 pm »
Nice addition of the bind variables! Great to finally have prepared statements.
There are other worlds than these

Offline Savage

  • Soldier
  • **
  • Posts: 155
Re: libdb (SQLite / MySQL / MariaDB / PostgreSQL / ODBC library)
« Reply #36 on: February 10, 2019, 08:40:47 am »
Amazing work  ;)