extDB (arma3 extension linux/windows)

[sany1984 10]

Hello,

I tryed to Compile extDB2 under Linux, with Poco 1.6.0 with the Changes from extDB2.

Now i become this Errors by Compiling:

g++ -DPOCO_BUILD_HOST=arma3 -D_XOPEN_SOURCE=500 -D_REENTRANT -D_THREAD_SAFE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE -DPOCO_HAVE_FD_EPOLL -g -Wl,-rpath,/root/poco-1.6.0-all/lib/Linux/i686 -o /root/poco-1.6.0-all/Data/MySQL/testsuite/bin/Linux/i686/testrunnerd /root/poco-1.6.0-all/Data/MySQL/testsuite/obj/Linux/i686/debug_shared/MySQLTestSuite.o /root/poco-1.6.0-all/Data/MySQL/testsuite/obj/Linux/i686/debug_shared/Driver.o /root/poco-1.6.0-all/Data/MySQL/testsuite/obj/Linux/i686/debug_shared/MySQLTest.o /root/poco-1.6.0-all/Data/MySQL/testsuite/obj/Linux/i686/debug_shared/SQLExecutor.o -L/root/poco-1.6.0-all/lib/Linux/i686 -lPocoDataMySQLd -lPocoDatad -lPocoFoundationd -lCppUnitd -lpthread -ldl -lrt -L/usr/local/lib/mysql -L/usr/lib/mysql -L/usr/mysql/lib -L/usr/mysql/lib/mysql -L/usr/local/mysql/lib -lmysqlclient -lz -lpthread -ldl

/root/poco-1.6.0-all/Data/MySQL/testsuite/obj/Linux/i686/debug_shared/MySQLTest.o: In function `MySQLTest::connectNoDB()':

/root/poco-1.6.0-all/Data/MySQL/testsuite/src/MySQLTest.cpp:94: undefined reference to `Poco::Data::MySQL::Connector::KEY'

/root/poco-1.6.0-all/Data/MySQL/testsuite/obj/Linux/i686/debug_shared/MySQLTest.o: In function `MySQLTest::suite()':

/root/poco-1.6.0-all/Data/MySQL/testsuite/src/MySQLTest.cpp:842: undefined reference to `Poco::Data::MySQL::Connector::KEY'

/root/poco-1.6.0-all/Data/MySQL/testsuite/src/MySQLTest.cpp:851: undefined reference to `Poco::Data::MySQL::Connector::KEY'

Can you help me ?

Thanks.

[torndeco 128]

Just use the static builds...

Anyway your problem is you can't build the testsuite when compiling Poco as a Static Library.

As such i never bothered to check if any of my changes break the testsuite aswell.

Also i don't provide support for compiling from source code...

Its harsh but most people build from source code just because they can and not for a valid reason.

Edited March 8, 2015 by Torndeco

[sany1984 10]

Hello Torndeco,

Thanks for you answer,

I tryed compile poco as shared-library and as static-lib failes everythin on the testsuite / testrunnerd

Without copy the changes from extDB2, compiles poco-lib fine. But extDB2 failed to compile.

These files are missing in the github version:

src/bercon.cpp

src/bercon.h

src/steam.cpp

src/steam.h

I am a c++ developer, think it's a shame if source code is available, it is either incomplete or does not work while the github source defective or incomplete.

The binary lib of extDB2 works not on debian 7.5.. => Segmentation fault.

[torndeco 128]

The code is complete, i just not walking every single person through building it from source code.

1)Poco Library will not build as static unless you disable testsuite

1a)Poco Library Custom Changes will more than likely break the testsuite (i never updated them)

2)extDB2 will not build without the custom Poco Changes i added, (noidea why you would expect it would compile without them)

3)Makefile is slightly outof data, you update the filenames etc, that got renamed... (its a minor change)

3a)Look @ cmake build files if you get confused...

Also extDB2 binary does not work on Debian 7.5 due to Glibc + librt changes.

Thats why Linux Requirements mention Debian 8 not 7

Edited March 8, 2015 by Torndeco

[torndeco 128]

Just a small heads up coming changes for next version, should be out in the next day or 2.

SQL_CUSTOM Bool Option - done

SQL_CUSTOM Date Option - not done yet (i.e return date in arma array format, will allow people to localize it etc)

Redis Support - nearly done

For people building from source code on Linux,

CMakefiles have been updated to build static version, old Makefiles has been removed.

Also thanks for https://github.com/MaHuJa for taking the time to go through extDB2 code + fixing / improving the code

for example the Unique ID generator..

Edited March 18, 2015 by Torndeco

[torndeco 128]

Version 47

First of all thanks for MaHuja,

for taking the time to look over some extDB2, and pointing out some issues + fixes.

Code for generating Unique ID code is alot simpler and faster now

Redis support added (ASYNC only atm)

.

Changes: version 47

• ADDED: REDIS_RAW Protocol
  
• ADDED: SQL_CUSTOM: BOOL Option
  
• UPDATED: Improved UniqueID Generator Code
  
• KNOWN ISSUES: There is no exceptional handling for Redis atm, so if loses connection will crash extension.
  

Changes: version 46

• FIXED: Windows Lockup
  

Changes: version 45

• FIXED: SQL_CUSTOM: Return ID
  
• FIXED: SQL_CUSTOM: Callname: Prepared Statement override
  
• FIXED: Logging Options + Updated Code
  

Downloads

https://github.com/Torndeco/extDB2/releases

[rimblock 0]

Well you have been busy. Thought you were stopping development.

I am still on v31 from Jan and here we are at the end of march and we are 16 versions on.... :eek:.

v31 is running fine for what I need it for at this time. Will look at upgrading later. Steam protocol support is nice to have along with RCon but they are needed closer to a release which we are still far from at the moment.

Some more info on $Custom_X$ would be good. Very little on the Wiki that I have seen.

[torndeco 128]

$Custom_X$ is for dynamic inserting text into SQL Statement i.e what A3Wasteland does with DB_CUSTOM_V3

An example if sql_custom file using $CUSTOM_x$ @ https://github.com/Torndeco/ArmA3_Wasteland.Altis/blob/Development_main/sql_custom/a3wasteland.ini

Hopefully the above changes will get merged into offical A3Wasteland soon.

--------------------------------------------------------------------------

DB_CUSTOM_V5 + SQL_CUSTOM uses prepared statements that are cached.

With Prepared Statements, only the value can change in the sql statement you can't have

SQL1_1 = SELECT ? FROM ServerObjects WHERE ServerID = ? AND MapID = ?;

To workaround this

SQL1_1 = SELECT $CUSTOM_1$ FROM ServerObjects WHERE ServerID = ? AND MapID = ?;

Also when calling extDB2 the order of inputs are

<inputs>:<custom_inputs>

---------

The downsides to using $Custom_x$ is its less secure and the prepared statement isn't cached.

If you were previous using DB_CUSTOM_V5 you can don't need to worry about $CUSTOM_x$

Also $CUSTOM_x$ was only added in extDB2

---------

Btw the version number increase could be a simple as new feature or a single bug fix... so 16 versions later might not mean to much :)

If you need a hand updating from extDB -> extDB2 give me a shout on Skype, other main change is preventing segfaults when database connection is dropped (timing issues)

Edited March 29, 2015 by Torndeco

[rimblock 0]

Cheers,

I say v31 as that is what my archive folder is named but I suspect it may be v33 or v34. I am def using extDB2. Will check the version from the server logs tonight. I am also using DB_Custom_V5 IIRC.

No issues with what I am using it for so far. The build seems pretty stable but I have not lost DB connectivity.

Ok, got the $CUSTOM_x$ functionality. Not needed for me at this time but can see where it can be helpful.

Yeah, I saw some of the versions were just bug fixes :). I may try to catch you at the weekend if I see you on Skype. I am using custom sqf to handle the calls, returns and error handling but as long as the same results come back int he same format then there shouldn't be an issue. The INSERTID may also be of interest to save a DB call to find the ID of a new character added to the DB.

[Tisor 17]

Hi Torndeco.

I'm having trouble when trying to use extdb2 to retrieve some data for a custom mision I'm making.

The setup looks correct and connects fine to the database. My problem comes when trying to retrieve data from an A-Sync call.

After making the query with call_type = 2, I start to make querys with the returned ID and the call_type = 5.

First query returns [3]. That means it is not ready yet. Then I sleep 0.01 and try again. Now it returns the info, something like: [1, [["_SP_PLAYER_", "Tisor"]]].

Then I try again. Wiki says that I have to try until I get an empty string or [1, ""]. So this is what I'm doing. But next call is returning me <null>. Then my code crash because it cannot handle that null.

My code looks like this:

while {_noFinalizado} do {
     _resultado = ['5:%1', _IDConsulta] call Servidor_LlamadaDB;

     if (isNil '_resultado'} exitWith {}; // The result has not been returned
     if (typeName _resultado == 'STRING') exitWith {}; // I've got the empty string here ""

     if (_resultado select 0 == 3) then { sleep 0.1 }; //Is not ready, sleep and try again.

     if {_resultado select 0 == 1) then { // Nice, I got something, lets go parse it
           if (typeName (_resultado select 1) == 'STRING'] exitWith {_noFinalizado = false}; // I got the [1,""] so I'm done
           _resultadoFinal = _resultadoFinal + ((_resultado select 1) select 1);
    }
}

Just to remember again, the results I'm getting looks like:

[3]

[1, ARRAYWITHDATA]

<null> ????????

I'm using SQL_RAW and my first call to the database to retrieve the data looks like this:

_resultado = "extDB2" callExtension  "2:SQL2:SELECT * FROM....";

Could you please give me advice on this? Why I'm I getting a null result? Is this expected or I've something wrong?

Regards and thanks in advance

[torndeco 128]

@Tisor

It will return empty string i.e

""

not

[1,""]

Have a look @ https://github.com/Torndeco/extDB2/blob/master/examples/sqf/fn_async.sqf#L43

I would recommend you use fn_async + just change SQL_CUSTOM_ID to whatever you are using.

https://github.com/Torndeco/extDB2/blob/master/examples/sqf/fn_async.sqf#L23

--------------

Also try out the test application, can make it easier to see what is happening aswell without having to start arma.

Edited March 31, 2015 by Torndeco

[z0kng 10]

Hi,

i started converting my stuff from raw to custom, and got on some queries the following problem "wrong bind parameters count".

Here is an example:

[updateWanted]
;;                                                        1                      2                                                 3
SQL1_1 = UPDATE playerstates b, player a SET b.crimes = '?' WHERE a.playerUID = ? AND a.playerID = b.playerID AND b.fraction = ? AND b.alive = 1;
SQL1_INPUTS = 1-String, 2, 3

Number of Inputs = 3

[10:54:00 +02:00] [Thread 1082764718] extDB2: Extension Input from Server: 1:165401:updateWanted:"[[`Totschlag`],8500]":76561198062277188:CIV
[10:54:00 +02:00] [Thread 1561815867] extDB2: SQL_CUSTOM: Trace: UniqueID: 0 Input: updateWanted:"[[`Totschlag`],8500]":76561198062277188:CIV
[10:54:00 +02:00] [Thread 1561815867] extDB2: SQL_CUSTOM: Error StatementException: MySQL: [MySQL]: [Comment]: wrong bind parameters count	[statemnt]: UPDATE playerstates b, player a SET b.crimes = '?' WHERE a.playerUID = ? AND a.playerID = b.playerID AND b.fraction = ? AND b.alive = 1;
[10:54:00 +02:00] [Thread 1561815867] extDB2: SQL_CUSTOM: Wiping Statements + Session
[10:54:00 +02:00] [Thread 1561815867] extDB2: SQL_CUSTOM: Error Exception: UniqueID: 0 SQL: updateWanted:"[[`Totschlag`],8500]":76561198062277188:CIV

if i insert the query manuel into the database it works. Does anyone have any idea what I'm doing wrong?

kind regards

z0Kng

[torndeco 128]

Hi,

[updateWanted]
;;                                                        1                      2                                                 3
SQL1_1 = UPDATE playerstates b, player a SET b.crimes = '?' WHERE a.playerUID = ? AND a.playerID = b.playerID AND b.fraction = ? AND b.alive = 1;
SQL1_INPUTS = 1-String, 2, 3

Number of Inputs = 3

Wrong bind parameters count means MySQL was expecting different number of bind values (?) than it got

For the above extDB2 sent it 3 bind values 1-String,2,3

But your SQL Prepared Statement only had 2 bind values (?) not 3

The mistake is you wrapped in ? in quotes, so instead of using a bind value MySQL thinks your you mean the character/string ?

To fix it use

SQL1_1 = UPDATE playerstates b, player a SET b.crimes = ? WHERE a.playerUID = ? AND a.playerID = b.playerID AND b.fraction = ? AND b.alive = 1;

[z0kng 10]

Thanks for your help and awesome stuff.

i knew it was something stupid.

[yourry 14]

I encounter some difficulties to write the sql_custom.

For example, at the first connection of the player I would write his name (Name) and iud (PlayerUID) in the DB:

sql_custom:

[playerInfoInsertOnDB]
SQL1_1 = INSERT INTO players_save ($CUSTOM_1$,$CUSTOM_2$) VALUES (?,?);
Number of Inputs = 4
Number of Custom Inputs = 2
SQL1_INPUTS = 3, 4

OUTPUT = 1-STRIP-STRING, 2-STRIP-STRING, 3, 4-STRING

sqf:

_result = [format["playerInfoInsertOnDB:%1:%2:%3:%4", "PlayerUID", "Name", _playerUID, _playerName], 1, true] call extDB_fnc_async;

I tested this and it works:

SQL1_1 = INSERT INTO players_save (PlayerUID, Name) VALUES (?,?);
SQL1_INPUTS = 1, 2-STRING
Number of Inputs = 2

_result = [format["playerInfoInsertOnDB:%1:%2", _playerUID, _playerName], 1, false] call extDB_fnc_async;

The latter method works but I want something more flexible where I can in the code .sqf asked columns.

I think it is this "$CUSTOM_1$" history that I have not yet understood.

Another question, if I change my sql_custom, it seems that we must restart the game. Is that true?

To save time, do what I can redo the init extDB2 process?

(https://github.com/Torndeco/extDB2/blob/master/examples/sqf/init.sqf)

[torndeco 128]

@Yourry

[playerInfoInsertOnDB]
SQL1_1 = INSERT INTO players_save ($CUSTOM_1$,$CUSTOM_2$) VALUES (?,?);
Number of Inputs = 4
Number of Custom Inputs = 2
SQL1_INPUTS = 3, 4
OUTPUT = 1-STRIP-STRING, 2-STRIP-STRING, 3, 4-STRING  

Change to

[playerInfoInsertOnDB]
SQL1_1 = INSERT INTO players_save ($CUSTOM_1$,$CUSTOM_2$) VALUES (?,?);
SQL1_INPUTS = 1, 2
Number of Inputs = 2
Number of Custom Inputs = 2

Note there is no OUTPUT for INSERT SQL Command

---------

Also when you are passing info to extDB2 the order is <inputs>:<custom inputs>

_result = [format["playerInfoInsertOnDB:%1:%2:%3:%4", "PlayerUID", "Name", _playerUID, _playerName], 1, true] call extDB_fnc_async;  

Change to

_result = [format["playerInfoInsertOnDB:%1:%2:%3:%4", _playerUID, _playerName, "PlayerUID", "Name"], 1, true] call extDB_fnc_async;  

---------------

Its not possible to reload / unload Protocols atm

Personally i type the commands into a text editor like

9:ADD_DATABASE:.......
9:ADD_DATABASE_PROTOCOL:...............
0:.............<command to test>

Then you can just paste the entire thing into the test application to test out the command.

---------- Post added at 23:17 ---------- Previous post was at 22:37 ----------

Version 49

Just mainly a bug fix release + some new options for SQL_CUSTOM

SQL_CUSTOM Notes

Version number = 9

You will need to changed "Strip Custom Input Chars" -> "Strip Custom Chars"

This was due to typo were code was checking for "Strip Custom Input Chars", but documentation + examples were using "Strip Custom Chars".

Bumped version number, so people will be sure they are using the correct option in there sql_custom.ini file.

-Vac_SteamID / -VacBeGUID

These new SQL_CUSTOM Options will use playerUID / steamID to check for Vac Bans depending on extDB-conf settings.

Should be usefully when saving / retreiving playerinfo from database etc.

It is the same as using STEAM Protocol, except that it doesn't return any values and saves you from using doing a seperate callExtension.

Changes v49

• FIXED: SQL_CUSTOM: Strip Custom Input Chars -> Strip Custom Chars
  
• UPDATED: SQL_CUSTOM version = 9
  

.

Changes v48

• ADDED: SQL_CUSTOM: Support for loading multiple sql_custom.ini files
  
• ADDED: SQL_CUSTOM: Vac_SteamID + Vac_BeGUID Options
  
• FIXED: Loading MISC + LOG Protocols
  

Downloads

https://github.com/Torndeco/extDB2/releases

Edited April 6, 2015 by Torndeco

[armatech 8]

thanks mate

[yourry 14]

Thank you for the help, I begin to understand.

I took the example on your git. I want to know if the player is known in the DB.

So I have the following code sql_custom:

[playerCheckSave]
Prepared Statement Cache = false
SQL1_1 = SELECT IF ((SELECT 1 FROM players_save WHERE PlayerUID = ?), 'true', 'false');

Number of Inputs = 1
SQL1_INPUTS = 1

.Sqf next query is as follows :

_result = [format["playerCheckSave:%1", _uid], 2, true] call extDB_fnc_async;

I have the following returns:

19:26:42 "extDB2: Protocol Error: [0,"Error Data Exception"]"

Edited April 6, 2015 by Yourry

[torndeco 128]

@Yourry

Lose the

Prepared Statement Cache = false

That just an option for when you only run the SQL Statement i.e @ server startup for loading objects / vehicles etc

Or if your testing + altering the Database Scheme while the server is running.

Use the following

[Default]
Prepared Statement Cache = false

Otherwise the statement will fail if its cached + the scheme has changed.

Note it will work fine after it has failed, since the cached prepared statements get wiped when they encounter an error.

-------------------

Anyway the errors extDB report back to SQF are generic error messages.

You need to look @ extDB2/logs to see detailed info on whats its complaining about.

Edited April 6, 2015 by Torndeco

[yourry 14]

An excerpt from the log:

[21:32:10 +02:00] [Thread 615662119] extDB2: SQL_CUSTOM: Trace: UniqueID: 9816 Input: playerCheckSave:12345

[21:32:10 +02:00] [Thread 615662119] extDB2: SQL_CUSTOM: Error DataException: SQL Statement invalid: SQL error or missing database: no such function: IF

[21:32:10 +02:00] [Thread 615662119] extDB2: SQL_CUSTOM: Wiping Statements + Session

[21:32:10 +02:00] [Thread 615662119] extDB2: SQL_CUSTOM: Error Exception: UniqueID: 9816 SQL: playerCheckSave:12345

[21:32:10 +02:00] [Thread 959710652] extDB2: Extension Input from Server: 4:9816

[21:32:10 +02:00] [Thread 959710652] extDB2: Extension Input from Server: 1:679518:playerInfoInsertOnDB:12345:=[TTK]= Yourry:PlayerUID:Name

[21:32:10 +02:00] [Thread 615662119] extDB2: SQL_CUSTOM: Trace: UniqueID: 0 Input: playerInfoInsertOnDB:12345:=[TTK]= Yourry:PlayerUID:Name

[21:32:10 +02:00] [Thread 615662119] extDB2: SQL_CUSTOM: Trace: UniqueID: 0 Result: [1,[]]

[21:33:09 +02:00] [Thread 959710652] extDB2: Stopping ...

I did not notice but the log is written that when the game is stopped ?

Another problem is when I go to look for information in the DB:

[21:28:09 +02:00] [Thread 3598687997] extDB2: SQL_CUSTOM: Trace: UniqueID: 9848 Input: playerGetInfo:12345:Name

I have the following return (correct):

[21:28:09 +02:00] [Thread 3598687997] extDB2: SQL_CUSTOM: Trace: UniqueID: 9848 Result: [1,[[=[TTK]= Yourry]]]

The concern is that = [TTK] = Yourry is not between "" and it makes crash .sqf

I'm sorry to bother you as much. I'm really a beginner.

[torndeco 128]

@Yourry

You could try catch me on Skype, i tend to check there for messages.

For playername you can use

SQL1_INPUTS = 1-STRING

This way playername is wrapping quotes inside the Database when it gets stored.

Or

OUTPUT = 1-STRING

That way playername is only wrapping in quotes when the data is fetched.

Plus no quotes are stored in the database itself.

----------------

SQLite apparently doesn't support IF statements.

You have afew different ways you could try solve the problem, by using either of the following functions instead.

SELECT + Just code SQF code to check if the result/array is empty.

or

CASE

or

http://stackoverflow.com/questions/418898/sqlite-upsert-not-insert-or-replace/4330694#4330694

or

COUNT

or

select exists(
   select 1
   from "Player Info"
   where Name = 'abc'
);

If you use "COUNT" or "SELECT exists" method it will return a number then you can combine that with "OUTPUT=1-BOOL".

This will convert the result

0 = false

1+ = true

----------------

Lastly if you need Realtime logging edit extdb-conf.ini

[Log]
Flush = true

Edited April 6, 2015 by Torndeco

[yourry 14]

Again thank you for your help.

I have not yet tested the back with "OUTPUT=1-BOOL" but the rest is working properly.

I might still be a few questions but for now I'll leave you a little quiet;)

Does Skype instead, you're also available by STEAM?

[torndeco 128]

Version 50

Just a minor bug fix release

SQL_RAW_V2 +SQL_CUSTOM_V2

They are the exact same, the only difference is when returning an empty result you get

[1,[]] instead of [1,[[]]]

If you are confused then chances are you never encountered it, don't worry about it.

That is the only difference, i don't plan on deprecate the original protocols anytime soon.

So there is no rush on having to update to V2.

Changes v50

• ADDED: V2 SQL Protocols, empty results return [] instead of [[]]
  
• 
  
• FIXED: Loading STEAM Protocol
  
• FIXED: Removed debug output for timing mutex locks
  

[HazJ 1289]

Hi, I have just installed extDB2 by following the guide you posted on GitHub but I have no idea how to use it. I have also setup the .ini config file too. Maybe someone can help? I have two variables (there is actually more but using these two as an example) setup in my mission, one for XP and the other for Money. How would I go about saving them to a MySQL database and then loading them from the MySQL database on connect/spawn? I would really like to figure this out as I want to use this instead of iniDB. (:

https://github.com/Torndeco/extdb/wiki/Setup:-Windows

Any help is much appreciated!

Dirty Haz

[torndeco 128]

@Dirty Haz

You have any prior knowledge of messing around with SQL Databases ?

Best thing is to mess around with MySQL Workbench to make up a simple Database Table containing all the columns you need.

Then mess around with SELECT / UPDATE SQL Statements to change the values in the Database.

Once you figured that out, will be easy enough todo the extDB2 part.

Try give me a shout in Skype