Author Topic: libdb (ODBC / SQLite / PostgreSQL library for Windows/Linux servers)  (Read 15076 times)

0 Members and 1 Guest are viewing this topic.

Offline SyavX

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

libdb ? is an external library for the SoldatServer (Windows and Linux) to work with ODBC, SQLite and PostgreSQL databases.

// =====================================
//               U S A G E
// =====================================

  • Extract an attached archive into the root of your SoldatServer directory.
    Thus libdb.dll (Windows) and libdb.so (Linux) will be placed near to soldatserver.exe (Windows) and soldatserver (Linux) executable files and /libdb directory with an examples code will be copied into the /scripts directory of your server.
  • Run server using a "-safe 0" command line switch.

// =====================================
//               N O T E S
// =====================================

Windows:
  • Everything should work out of the box

Linux:
  • You should comment the "libdb_windows.pas" line and uncomment the "libdb_linux.pas" line in the /libdb/Includes.txt file to make an example code to work on Linux.
  • There could be a need to do an export of LD_LIBRARY_PATH (export LD_LIBRARY_PATH=/path/to/soldatserver) to use external library functions
  • libdb-0.1 depends on iODBC
  • libdb-0.2 depends on unixODBC
For Debian-based distros:
Code: (libdb-0.1) [Select]
sudo apt-get install libiodbc2:i386
Code: (libdb-0.2) [Select]
sudo apt-get install libodbc1:i386 libltdl7:i386
// =====================================
//            E X A M P L E S
// =====================================

Code: (Pascal) [Select]
// =====================================
//          C O N S T A N T S
// =====================================
{ Database plugins enumeration
  for the DB_Open() function   }
Const DB_Plugin_ODBC       = 1;
Const DB_Plugin_SQLite     = 2;
Const DB_Plugin_PostgreSQL = 3;

{ Database column types enumeration
  for the DB_ColumnType() function  }
Const DB_Type_Double = 1;
Const DB_Type_Float  = 2;
Const DB_Type_Long   = 3;
Const DB_Type_String = 4;

// =====================================
//        D E C L A R A T I O N S
// =====================================
Procedure DB_Close(DatabaseID: Integer);
External 'DB_Close@libdb.dll cdecl';

Function DB_ColumnName(DatabaseID, Column: Integer): PChar;
External 'DB_ColumnName@libdb.dll cdecl';

Function DB_ColumnSize(DatabaseID, Column: Integer): Integer;
External 'DB_ColumnSize@libdb.dll cdecl';

Function DB_ColumnType(DatabaseID, Column: Integer): Integer;
External 'DB_ColumnType@libdb.dll cdecl';

Function DB_Columns(DatabaseID: Integer): Integer;
External 'DB_Columns@libdb.dll cdecl';

Function DB_Error(): PChar;
External 'DB_Error@libdb.dll cdecl';

Function DB_Query(DatabaseID: Integer; Query: PChar): Integer;
External 'DB_Query@libdb.dll cdecl';

Function DB_Update(DatabaseID: Integer; Query: PChar): Integer;
External 'DB_Update@libdb.dll cdecl';

Procedure DB_FinishQuery(DatabaseID: Integer);
External 'DB_FinishQuery@libdb.dll cdecl';

Function DB_FirstRow(DatabaseID: Integer): Integer;
External 'DB_FirstRow@libdb.dll cdecl';

Function DB_GetDouble(DatabaseID, Column: Integer): Double;
External 'DB_GetDouble@libdb.dll cdecl';

Function DB_GetFloat(DatabaseID, Column: Integer): Single;
External 'DB_GetFloat@libdb.dll cdecl';

Function DB_GetLong(DatabaseID, Column: Integer): LongInt;
External 'DB_GetLong@libdb.dll cdecl';

Function DB_GetString(DatabaseID, Column: Integer): PChar;
External 'DB_GetString@libdb.dll cdecl';

Function DB_IsDatabase(DatabaseID: Integer): Integer;
External 'DB_IsDatabase@libdb.dll cdecl';

Function DB_NextRow(DatabaseID: Integer): Integer;
External 'DB_NextRow@libdb.dll cdecl';

Function DB_Open(DatabaseID: Integer; DatabaseName, User, Password: PChar; Plugin: Integer): Integer;
External 'DB_Open@libdb.dll cdecl';

Function DB_ExamineDrivers(): Integer;
External 'DB_ExamineDrivers@libdb.dll cdecl';

Function DB_NextDriver(): Integer;
External 'DB_NextDriver@libdb.dll cdecl';

Function DB_DriverDescription(): PChar;
External 'DB_DriverDescription@libdb.dll cdecl';

Function DB_DriverName(): PChar;
External 'DB_DriverName@libdb.dll cdecl';

Function DB_GetVersion(): Integer;
External 'DB_GetVersion@libdb.dll cdecl';

// =====================================
//            E X A M P L E S
// =====================================
function CheckDatabaseUpdate(Database: Integer; Query: PChar): Integer;
var
Check: Integer;
begin
WriteLn('>'+Query); //debug output
Check:= DB_Update(Database, Query);

if Check = 0 then
WriteLn(DB_Error());

Result:= Check;
end;


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...');

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;
end;


procedure create_db2();
var
  db_path: String;
  query: String;
  db, i: Integer;
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...');

db:= 0;
if DB_Open(db, db_path, '', '', DB_Plugin_SQLite) <> 0 then
begin
WriteLn('Database "'+db_path+'" opened...');
query:= 'CREATE TABLE IF NOT EXISTS test(';
query:= query+'id INTEGER PRIMARY KEY, ';
query:= query+'name TEXT NOT NULL, ';
query:= query+'value INTEGER, ';
query:= query+'UNIQUE(name) ON CONFLICT IGNORE);';
if CheckDatabaseUpdate(db, query) <> 0 then
begin
DB_Update(db, 'BEGIN;');
for i:= 1 to 20 do
CheckDatabaseUpdate(db, 'INSERT INTO test(name, value) VALUES("name'+IntToStr(i)+'", '+IntToStr(i)+');');

DB_Update(db, 'COMMIT;');
WriteLn('Values inserted...');
end;
DB_Close(db);
WriteLn('Database "'+db_path+'" closed...');
end;
end;


procedure query_db(param: Integer);
var
  db_path, query, result_srt: String;
  i, columns: Integer;
begin
db_path:= 'test.db';
if DB_Open(0, db_path, '', '', DB_Plugin_SQLite) <> 0 then
begin
WriteLn('Database "'+db_path+'" opened...');
query:= 'SELECT * FROM test WHERE value > '+IntToStr(param)+';';
if DB_Query(0, query) <> 0 then
begin
WriteLn('>'+query); //debug output
columns:= DB_Columns(0);
while DB_NextRow(0) <> 0 do
begin
result_srt:='';
for i:= 0 to columns - 1 do
result_srt:= result_srt +DB_ColumnName(0, i)+': '+DB_GetString(0, i)+'; ';

WriteLn(result_srt);
end;

DB_FinishQuery(0);
end;

DB_Close(0);
WriteLn('Database "'+db_path+'" closed...');
end
else
WriteLn('Can not open database "'+db_path+'"!');
end;


procedure drivers();
begin
if DB_ExamineDrivers() <> 0 then
begin
WriteLn('ODBC drivers installed:');
while DB_NextDriver() <> 0 do
begin
WriteLn('Name - '+DB_DriverName());
WriteLn('Desc - '+DB_DriverDescription());
WriteLn('* * * * *');
end;
end
else
WriteLn('No ODBC drivers installed!');
end;


procedure OnAdminMessage(IP, Msg: String);
begin
case LowerCase(Msg) of
'/create1' : create_db1();
'/create2' : create_db2();
'/query1'  : query_db(7);
'/query2'  : query_db(14);
'/drivers' : drivers();
'/version' : WriteLn('libdb v0.'+IntToStr(DB_GetVersion()));
end;
end;



// =====================================
//              I N D E X
// =====================================

Constats:
    DB_Plugin_ODBC
    DB_Plugin_SQLite
    DB_Plugin_PostgreSQL
   
Functions:
    DB_Close()
    DB_ColumnName()
    DB_ColumnSize()
    DB_ColumnType()
    DB_Columns()
    DB_Error()
    DB_Query()
    DB_Update()
    DB_FinishQuery()
    DB_FirstRow()
    DB_GetDouble()
    DB_GetFloat()
    DB_GetLong()
    DB_GetString()
    DB_IsDatabase()
    DB_NextRow()
    DB_Open()
    DB_ExamineDrivers()
    DB_NextDriver()
    DB_DriverDescription()
    DB_DriverName()
    DB_GetVersion()

// =====================================
//          C O N S T A N T S
// =====================================

// [Index] =======================================================================================
Const DB_Plugin_ODBC       = 1;


It is possible to obtain a list of available ODBC drivers by calling the function DB_ExamineDrivers().

MySQL ODBC drivers:
http://dev.mysql.com/downloads/connector/odbc/

MySQL connection strings:
http://www.connectionstrings.com/mysql#p31

// [Index] =======================================================================================
Const DB_Plugin_SQLite     = 2;


SQLite is a file based, serverless database manager. 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: http://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.


Example
Code: (Pascal) [Select]
  Filename := 'test_db.sqlite';

  If Not FileExists(Filename) Then
    If WriteFile(Filename,'') Then
      WriteLn('Database file created');
 
  If DB_Open(0, Filename, '', '', DB_Plugin_SQLite) <> 0 Then
  Begin
    WriteLn('Connected to '+Filename);
    If DB_Update(0, 'CREATE TABLE info (test VARCHAR(255));') <> 0 Then
      WriteLn('Table created');
  End;

// [Index] =======================================================================================
Const DB_Plugin_PostgreSQL = 3;


PostgreSQL is a powerful, server based database manager which support very large database and high concurrency. It is free to use in commercial projects, unlike MySQL which requiers a licence to use it in a non-GPL program. For more information about PostgreSQL: http://www.postgresql.org

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.


Example
Code: (Pascal) [Select]
  // You should have a server running on localhost
  If DB_Open(0, "host=localhost port=5432", "postgres", "postgres", DB_Plugin_PostgreSQL) <> 0 Then
    WriteLn('Connected to PostgreSQL')
  Else
    WriteLn('Connection failed: '+DB_Error());

// =====================================
//          F U N C T I O N S
// =====================================

// [Index] =======================================================================================
Procedure DB_Close(DatabaseID: Integer);


Syntax
DB_Close(DatabaseID);


Description
Close the specified DatabaseID (and connections/transactions if any). No further operations are allowed on this database.

Parameters
DatabaseID Specifies the database to close.

Return value
This function has no return value.

// [Index] =======================================================================================
Function DB_ColumnName(DatabaseID, Column: Integer): PChar;


Syntax
Text := DB_ColumnName(DatabaseID, Column);


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_ColumnSize(DatabaseID, Column: Integer): Integer;


Syntax
Result := DB_ColumnSize(DatabaseID, Column);


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, Column: Integer): Integer;


Syntax
Result := DB_ColumnType(DatabaseID, Column);


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. If Result is 0, the type is undefined or the function has failed (e.g. it was not possible to determine the data type).
Type values can be:
  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)

// [Index] =======================================================================================
Function DB_Columns(DatabaseID: Integer): Integer;


Syntax
Result := DB_Columns(DatabaseID);


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_Error(): PChar;


Syntax
Error := DB_Error();


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().

Parameters
This function has no parameters.

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') <> 0 Then // Get all the records in the 'players' table
  Begin
    // Code goes here...
    DB_FinishQuery(DatabaseID);
  End
  Else
    WriteLn('Error: Can`t execute the query: '+DB_Error());

// [Index] =======================================================================================
Function DB_Query(DatabaseID: Integer; Query: PChar): Integer;


Syntax
Result := DB_Query(DatabaseID, Query);


Description
Executes a SQL query on the given database.

Parameters
DatabaseID Specifies the database to use.
Query      Specifies the query to execute.

Return value
Returns nonzero if the query was successful or zero 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 execute a request that doesn't return any records, use DB_Update() instead of DB_Query().

Once the query results aren't needed anymore, DB_FinishQuery() has to be called to release all the query resources.

Example
Code: (Pascal) [Select]
  // First, connect to a database with a 'players' table
  If DB_Query(DatabaseID, 'SELECT * FROM players') <> 0 Then // Get all the records in the 'players' table
  Begin
    While DB_NextRow(DatabaseID) <> 0 Do // Loop for each records
      WriteLn(DB_GetString(DatabaseID, 0)); // Display the content of the first field

    DB_FinishQuery(DatabaseID);
  End;

// [Index] =======================================================================================
Function DB_Update(DatabaseID: Integer; Query: PChar): Integer;


Syntax
Result := DB_Update(DatabaseID, Query);


Description
Executes a query on the given database without returning any records.

Parameters
DatabaseID Specifies the database to use.
Query      Specifies the query to execute.

Return value
Returns nonzero if the query was successful or zero 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().

Example
Code: (Pascal) [Select]
  // First, connect to a database with a 'players' table
  If DB_Query(DatabaseID, 'SELECT * FROM players') <> 0 Then // Get all the records in the 'players' table
  Begin
    While DB_NextRow(DatabaseID) <> 0 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;

// [Index] =======================================================================================
Procedure DB_FinishQuery(DatabaseID: Integer);


Syntax
DB_FinishQuery(DatabaseID);


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.

Return value
This function has no return value.

Example
Code: (Pascal) [Select]
  // First, connect to a database with a 'players' table
  If DB_Query(DatabaseID, 'SELECT * FROM players') <> 0 Then // Get all the records in the 'players' table
  Begin
    While DB_NextRow(DatabaseID) <> 0 Do // Loop for each records
      WriteLn(DB_GetString(DatabaseID, 0)); // Display the content of the first field
   
    DB_FinishQuery(DatabaseID);
  End;

// [Index] =======================================================================================
Function DB_FirstRow(DatabaseID: Integer): Integer;


Syntax
Result := DB_FirstRow(DatabaseID);


Description
Retrieves information about the first DatabaseID row.

Parameters
DatabaseID Specifies the database to use.

Return value
If Result is 0, 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, Column: Integer): Double;


Syntax
Result := DB_GetDouble(DatabaseID, Column);


Description
Returns the content of the specified database column as a double precision floating point number.

Parameters
DatabaseID Specifies the database to use.
Column     Specifies the column to use.

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, Column: Integer): Single;


Syntax
Result := DB_GetFloat(DatabaseID, Column);


Description
Returns the content of the specified database column as a floating point number.

Parameters
DatabaseID Specifies the database to use.
Column     Specifies the column to use.

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, Column: Integer): LongInt;


Syntax
Result := DB_GetLong(DatabaseID, Column);


Description
Returns the content of the specified DatabaseID column as an integer number.

Parameters
DatabaseID Specifies the database to use.
Column     Specifies the column to use.

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_GetString(DatabaseID, Column: Integer): PChar;


Syntax
Text := DB_GetString(DatabaseID, Column);


Description
Returns the content of the specified DatabaseID column as a string.

Parameters
DatabaseID Specifies the database to use.
Column     Specifies the column to use.

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: Integer): Integer;


Syntax
Result := DB_IsDatabase(DatabaseID);


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 nonzero if DatabaseID is a valid database connection and zero otherwise.

Remarks
This function is bullet-proof and can be used with any value. If Result is not zero then the object is valid and initialized, otherwise it returns zero. This is a good way to check that a database is ready to use.

// [Index] =======================================================================================
Function DB_NextRow(DatabaseID: Integer): Integer;


Syntax
Result := DB_NextRow(DatabaseID);


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 Result is 0, then no more rows are available (i.e. reached the end of the table).

// [Index] =======================================================================================
Function DB_Open(DatabaseID: Integer; DatabaseName, User, Password: PChar; Plugin: Integer): Integer;


Syntax
Result := DB_Open(DatabaseID, DatabaseName, User, Password, Plugin);


Description
Opens a new database connection.

Parameters
DatabaseID   Specifies the number by which to refere to the new database.
DatabaseName Specifies the name of the database to open.
User         Specifies the user name for the connection.
Password     Specifies the password for the connection. This can be an empty string if no password is required.
Plugin       This parameter can be used to specify the database plugin to use. Possible values are:
  DB_Plugin_ODBC       - The database will use ODBC backend.
  DB_Plugin_SQLite     - The database will use SQLite backend.
  DB_Plugin_PostgreSQL - The database will use PostgreSQL backend.

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.

// [Index] =======================================================================================
Function DB_ExamineDrivers(): Integer;


Syntax
Result := DB_ExamineDrivers();


Description
Examines the database drivers available on the system.

Parameters
This function has no parameters.

Return value
If ODBC isn't installed or no drivers are available, it returns 0, otherwise DB_NextDriver() can be used to list all the drivers.

Remarks
This is an ODBC database specific command.

// [Index] =======================================================================================
Function DB_NextDriver(): Integer;


Syntax
Result := DB_NextDriver();


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.

Parameters
This function has no parameters.

Return value
If Result is 0, no more drivers are available.

Remarks
This is an ODBC database specific command.

// [Index] =======================================================================================
Function DB_DriverDescription(): PChar;


Syntax
Text := DatabaseDriverDescription();


Description
Returns the description of the current database driver. Drivers are listed using the DB_ExamineDrivers() and DB_NextDriver() functions.

Parameters
This function has no parameters.

Return value
Returns the description string.

Remarks
This is an ODBC database specific command.

// [Index] =======================================================================================
Function DB_DriverName(): PChar;


Syntax
Text := DB_DriverName();


Description
Return the name of the current database driver. Drivers are listed using the DB_ExamineDrivers() and DB_NextDriver() functions.

Parameters
This function has no parameters.

Return value
Returns the name of the driver.

Remarks
This is an ODBC database specific command.

// [Index] =======================================================================================
DB_GetVersion(): Integer;


Syntax
Result := DB_GetVersion();


Description
Returns the version of the libdb library.

Parameters
This function has no parameters.

P.S. "Stroke" icon set from the IconShock has been used to create an icon.
« Last Edit: June 14, 2018, 04:50:38 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: 1506
  • Also known as Curt
    • Soldat Global Account System
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.
Soldat Global Account System (SGAS): QuakeNet in #soldat.sgas
Rent Cheap Servers: contact CurryWurst

Offline jrgp

  • Administrator
  • Flamebow Warrior
  • *****
  • Posts: 5002
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 »
Careful with that axe, Eugene.

Offline SyavX

  • Soldat Beta Team
  • Camper
  • ******
  • Posts: 336
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: 5002
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.
Careful with that axe, Eugene.

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: 336
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://soldatforums.com/

   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: 336
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: 336
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: 336
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: 336
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: 336
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;
...