Intercept Database (SQL Database Extension)

[xjoker_ 25]

I don't know what's the best solution.

But for my personnal usecase, I know that I have many checks like that 

if !(_groupId isEqualTo "") then {
  _obj setVariable ["groupId",_groupId,true];
};

to do something for example when a foreign key is not null for certain rows

so i'll have to rewrite few files (not a big deal btw but just saying) to replace that with a

if(!isNil "_var") then

 

[xjoker_ 25]

Bigger problem : dbResultToParsedArray and dbResultToArray

_query = dbPrepareQuery ["SELECT steamid, position FROM players WHERE playerid = ?", [279]];
_result = DB_connection dbExecute _query;

steamid is a varchar(17)

position is a varchar(45)

 

diag_log str ["RESULT",dbResultToArray _result];

will print 

19:08:18 "[""RESULT"",[[""7656119807XXXXXXX"",""[8280.25,9981.17,0.00143433]""]]]" // pos is not parsed so i can't use directly the setPos command

And

diag_log str ["RESULT PARSED",dbResultToParsedArray _result];

will print

19:08:18 "[""RESULT PARSED"",[[7.65612e+016,[8280.25,9981.17,0.00143433]]]]" // i can use setPos, but steamid has been parsed too and i need it as a string (to use with getPlayerUID)

So we need a way to choose fields that we want to parse 

for example in extDB3 : OUTPUT = 1-STRING, 2-STRING, 3, 4

[Dedmen 2716]

On 6/28/2019 at 1:55 PM, xjoker_ said:

_groupId isEqualTo ""

I can make that return true with a custom null type, no problem.

 

21 hours ago, xjoker_ said:

So we need a way to choose fields that we want to parse

Just put quotes around it with your SQL query.

 

 

 

SELECT CONCAT('"',steamID, '"') -> "76561198114764155"
instead of
SELECT steamID -> 76561198114764155

 

[xjoker_ 25]

On 6/29/2019 at 4:44 PM, Dedmen said:

 

SELECT CONCAT('"',steamID, '"') -> "76561198114764155"
instead of
SELECT steamID -> 76561198114764155

 

 

Ok so i tried to use that, and it seems to add more quotes than needed ? 

And on top of that i added the inventory (from getUnitLoadout command) and it doesn't appear in the parsed result. 

 

[Dedmen 2716]

diag_log additionally escapings things just makes everything look much worse.

Why are you doing that? "diag_log str [...]" ? That just makes everything look more confusing. diag_log takes ANY, you can just pass the array.

 

What is the value from the database inside inventory? and what datatype is that column?

I'll have to test that myself.

I think I can already see the bug on the parsedArray with the steamid. It doesn't actually parse the string, it just passes it through.

[xjoker_ 25]

type = TEXT

value = [[],[],[],["U_C_Blouson_Noir",[]],[],[],"","",[],["ItemMap","","","","",""]]

[Dedmen 2716]

Gives me steamid ,inventory. I'm actually storing the steamid as bigint.
ToArray

[["""76561198049878030""","[[],[],[],[""U_C_Blouson_Noir"",[]],[],[],"""","""",[],[""ItemMap"","""","""","""","""",""""]]"]]

ToParsedArray

[["""76561198049878030""",[[],[],[],["U_C_Blouson_Noir",[]],[],[],"","",[],["ItemMap","","","","",""]]]]

the loadout looks good to me.
The steamid thing is a bug yep. Fixing right now..


Aaand...

 

ToParsedArray

[["76561198049878030",[[],[],[],["U_C_Blouson_Noir",[]],[],[],"","",[],["ItemMap","","","","",""]]]]

 

looks good to me.

Keep in mind that, in order to work with parseSimpleArray your string cannot have whitespace at the end after the closing ] (I tripped on that when copying your example as there was a whitespace at the end

 

https://github.com/intercept/intercept-database/commit/500047b07f356b135119b10721477a6e5d8f2c9a

Here's a build with the fix:  https://github.com/intercept/intercept-database/files/3343206/idb_x64_30062019.zip

 

On the other thing

 

On 6/28/2019 at 1:55 PM, xjoker_ said:

if !(_groupId isEqualTo "") then

 

 

I think I'm not gonna make that possible. But I'll make isNull work on null values, and return null instead of nils.

 

[xjoker_ 25]

I tested with the new build and it works great even with a far more complicated inventory 

thanks 👌

[xjoker_ 25]

this has no effect (it works in mysql workbench)

_query = dbPrepareQuery ["UPDATE players SET playtime=playtime+(((time_to_sec(timediff(NOW(),datelastseen)))/60)/60) WHERE playerid = ?", [279]];
DB_connection dbExecute _query;

no error in server logs

playtime is a FLOAT (number of hours played on the server)

datelastseen is a DATETIME

[Dedmen 2716]

Okey here we go with version 1.4

https://github.com/intercept/intercept-database/releases/tag/1.4

 

Added blob support for dbToParsedArray
Added ability to bind null values (nil/objNull/ctrlNull/whateverNull binds to null SQL value)
Fixed ToParsedArray not properly parsing quoted strings
Added parseDateType config option
Added parseTinyintAsBool config option
Added ability to specify per-statement config options
Added dbGetBoundValues command

 

Ofc all the documentation was also updated with the last changes: https://intercept-database.readthedocs.io/en/latest/index.html

 

[Dedmen 2716]

And to not get into the habit of not releasing stuff to stable, here comes 1.5

 

Fixed binding null values on async query not working
Fixed handling of null values in result (would show the previous value of same column in a row where value is null)
Better handling and error reporting for bindValues (Throws error if invalid type was passed, and automatically converts array to string internally)
Added dbNull type (https://intercept-database.readthedocs.io/en/latest/api/results.html#dbnull)

 

I would've liked that dbNull can be isEqualTo compared to empty string like someone requested, but seems like Arma engine limitations prevent that.

[Dedmen 2716]

First Intercept Database linux build is ready.
I've pushed the v1.5 build here:
https://github.com/intercept/intercept-database/releases/tag/1.5

It is experimental and kinda hacky and it needs some more changes so that it can be built directly from git, but it seems to work fine on my server.
If anyone else is running a Linux server too, throw your feedback at me.

[Terrano. 0]

I still can't connect to my database 😞 in log is writing - only - not connected

 

version 1.5

 

17:12:53 C:\Program Files (x86)\Steam\steamapps\common\Arma 3 Server\@InterceptDB\addons\intercept_core.pbo - unknown
17:12:53 C:\Program Files (x86)\Steam\steamapps\common\Arma 3 Server\@InterceptDB\addons\main.pbo - unknown
      
17:12:53                                       @InterceptDB |         @InterceptDB |      false |      false |             GAME DIR | da39a3ee5e6b4b0d3255bfef95601890afd80709 |  11fdd19c | C:\Program Files (x86)\Steam\steamapps\common\Arma 3 Server\@InterceptDB
      
17:12:54 Initializing Intercept
17:12:54 CallExtension loaded: intercept (C:\Program Files (x86)\Steam\steamapps\common\Arma 3 Server\@InterceptDB\intercept_x64.dll) [] []

17:12:54 Intercept initialization part 1/3: 1
17:12:54 Intercept initialization part 2/3: true
17:12:54 Intercept Loading Plugin: intercept-database
17:12:54 "Intercept: Load completed [intercept-database]"
17:12:54 Intercept initialization part 3/3: -1
      
2019/07/22, 17:13:18 Intercept Invoker SQF handler initializing...
2019/07/22, 17:13:18 Intercept Invoker test result:  == 
2019/07/22, 17:13:18 Intercept Invoker initialized.
2019/07/22, 17:13:18 Intercept Pre-Init...
2019/07/22, 17:13:18 Intercept Pre-Init Completed.
    
2019/07/22, 17:14:07 Intercept Post-Init...
2019/07/22, 17:14:07 Intercept Post-Init Finished...

 

when i trying to use config to connect

DB_connection = dbCreateConnection "productionDB";

// return

2019/07/22, 17:15:24 Error in expression <DB_connection = dbCreateConnection "productionDB";diag_l>
2019/07/22, 17:15:24   Error position: <dbCreateConnection "productionDB";diag_l>
2019/07/22, 17:15:24   Error dbCreateConnection account "productionDB" not found in config
2019/07/22, 17:16:24 "[any]"

when trying script:

dbCreateConnection ["127.0.0.1",3306,"arma3","","altislife"];

// return

2019/07/22, 17:20:52 "[<not connected>]"
2019/07/22, 17:23:59 "[<not connected>]"

help, please

[Dedmen 2716]

16 hours ago, Terrano. said:

Error dbCreateConnection account "productionDB" not found in config

Well. It says right there.

 

16 hours ago, Terrano. said:

2019/07/22, 17:20:52 "[<not connected>]"

Did you read the documentation?

https://intercept-database.readthedocs.io/en/latest/api/connection.html

 

Quote

Connection is not established until the first query.

 

[Terrano. 0]

1 hour ago, Dedmen said:

Well. It says right there.

 

Did you read the documentation?

https://intercept-database.readthedocs.io/en/latest/api/connection.html

 

 

yes, i do all like in documentation

#See https://intercept-database.readthedocs.io/en/stable/intro/config-file.html for more information
global:
 enableDynamicQueries: true #Allow queries to be created from SQF, if false only statements from config are allowed

accounts:
 maindb: #production db, don't break things here!
  ip: stuff
  username: arma3
  password: pass
  database: altislife
  port: 3306 #optional
 productionDB:
  ip: 127.0.0.1
  username: arma3
  password:
  database: altislife
  port: 3306
schemas:
 test: schema.sql

statements:
 getMissionName: SELECT 1
 date:
  query: SELECT NOW()
  parseDateType: stringMS

#DB_query = dbPrepareQueryConfig ["date", [400]];
#DB_connection = dbCreateConnection "maindb";
#DB_query = dbPrepareQueryConfig "date";
#DB_connection dbExecuteAsync DB_query;

 

[Dedmen 2716]

12 minutes ago, Terrano. said:

yes, i do all like in documentation

So I guess it doesn't see the config then. Is the config in the correct place?

Arma 3/@InterceptDB/config.yaml
Case sensitive?

[Terrano. 0]

34 minutes ago, Dedmen said:

So I guess it doesn't see the config then. Is the config in the correct place?

Arma 3/@InterceptDB/config.yaml
Case sensitive?

C:\Program Files (x86)\Steam\steamapps\common\Arma 3 Server\@InterceptDB\config.yaml

my folder named `Arma 3 Server` all mods here

 

maybe i need to move only config? to Arma3\@InterceptDB\config.yaml

[Terrano. 0]

30 minutes ago, Terrano. said:

C:\Program Files (x86)\Steam\steamapps\common\Arma 3 Server\@InterceptDB\config.yaml

my folder named `Arma 3 Server` all mods here

 

maybe i need to move only config? to Arma3\@InterceptDB\config.yaml

2019/07/23, 13:01:04 "any"
2019/07/23, 13:01:04 Error in expression <DB_connection = dbCreateConnection "productionDB"; 
diag>
2019/07/23, 13:01:04   Error position: <dbCreateConnection "productionDB"; 
diag>
2019/07/23, 13:01:04   Error dbCreateConnection account "productionDB" not found in config

still don't work

 

[Dedmen 2716]

48 minutes ago, Terrano. said:

my folder named `Arma 3 Server` all mods here

looks correct

 

48 minutes ago, Terrano. said:

maybe i need to move only config? to Arma3\@InterceptDB\config.yaml

nope. Just needs to be in arma folder

 

Have you checked RPT log? it might log errors when parsing config. Not sure if empty password is allowed.

Maybe try

password: ""

Actually yes. Password like you set it is "null" and null is invalid. It should be throwing ""Undefined account value: password" error into your RPT.

[Terrano. 0]

26 minutes ago, Dedmen said:

looks correct

 

nope. Just needs to be in arma folder

 

Have you checked RPT log? it might log errors when parsing config. Not sure if empty password is allowed.

Maybe try

password: ""

Actually yes. Password like you set it is "null" and null is invalid. It should be throwing ""Undefined account value: password" error into your RPT.

i have tryed:

password: ""

// then

password: ''

// but still getting 

"<not connected>"

 

[Terrano. 0]

@Dedmen I changed user in settings, set password. But still getting <not connected>

 

new config:

#See https://intercept-database.readthedocs.io/en/stable/intro/config-file.html for more information
global:
 enableDynamicQueries: true #Allow queries to be created from SQF, if false only statements from config are allowed

accounts:
 maindb: #production db, don't break things here!
  ip: 127.0.0.1
  username: interceptor
  password: rb25
  database: altislife
  port: 3306 #optional
 productionDB:
  ip: 127.0.0.1
  username: interceptor
  password: rb25
  database: altislife
  port: 3306
schemas:
 test: schema.sql

statements:
 getMissionName: SELECT 1
 date:
  query: SELECT NOW()
  parseDateType: stringMS

#DB_query = dbPrepareQueryConfig ["date", [400]];
#DB_connection = dbCreateConnection "maindb";
#DB_query = dbPrepareQueryConfig "date";
#DB_connection dbExecuteAsync DB_query;

 

[Dedmen 2716]

13 minutes ago, Terrano. said:

But still getting <not connected>

I already answered that above. Read the documentation.

[Terrano. 0]

55 minutes ago, Dedmen said:

I already answered that above. Read the documentation.

I read documentation again and again, but still not working. Tryed new users, tryed create connection from script, but nothing. <not connected>

[Dedmen 2716]

48 minutes ago, Terrano. said:

I read documentation again and again, but still not working.

 

[Terrano. 0]

56 minutes ago, Dedmen said:

 

ah, I'm so stupid. Thank you very much! Everything works just great! Great plugin 👍