Addon skript-db 0.2.0

[btk5h]

btk5h submitted a new resource:

skript-db - Sensible SQL support for Skript

Awesome direct database access for Skript

What makes skript-db different from other SQL addons?

• Automatic SQL injection protection
• Support for connections to multiple databases
• An amazingly intuitive list variable system for reading query outputs

Documentation/Source - Releases

Quickstart

on script load:
  set...

Read more about this resource...

 

[Tai1er]

Hi,

I have some problems with variables in query.
When I try this,
"execute "INSERT INTO Coordonnees (`id`,`region`) VALUES ('%{_x}%', 1)" in {sql}"

I receive this mysql error:
"Parameter index out of range (1 > number of parameters, which is 0)."

It works normaly without %{_x}% in the query.

 

[btk5h]

Hi,

I have some problems with variables in query.
When I try this,
"execute "INSERT INTO Coordonnees (`id`,`region`) VALUES ('%{_x}%', 1)" in {sql}"

I receive this mysql error:
"Parameter index out of range (1 > number of parameters, which is 0)."

It works normaly without %{_x}% in the query.

This is a quirk caused by skript-db's automatic SQL injection protection. If you want to embed Skript expressions into a query, don't surround them in quotes or perform any escaping yourself. Skript-db will handle this part for you.

Try removing the apostrophes around the expression like so:

execute "INSERT INTO Coordonnees (`id`,`region`) VALUES (%{_x}%, 1)" in {sql}

[doublepost=1511285632,1511198547][/doublepost]

Hi, I have an other question.
There is a way to force the server to wait for the answer of a query? like Skutilities do with yaml files? because of this, it's impossible to use skript-db in a function which return a value.

It's a bad idea to make IO operations block the main thread. Long running queries can halt the server for noticeable periods of time, creating undesirable server lag. The fact that functions cannot have wait statements is an issue that should be resolved on Skript's end.

Although I've never used MundoSK myself, you might be able to workaround your issue using this feature.

 

[Tai1er]

Thanks for your fast answer.

The async expression from MundoSK changes anythink.
The expression "execute" is like a "wait" for Skript.

 

[Pikachu]

Thanks for your fast answer.

The async expression from MundoSK changes anythink.
The expression "execute" is like a "wait" for Skript.

There isn't really much you can do about this. The only workaround is using skquery functions which *are* compatible with async effects. I have used this a lot with reqn. They kinda suck, and skQuery shouldn't really be used anymore but at least it works. Another thing you can do is use mundosk custom events as functions and communicate the data through variables that you can grab with skript-mirror.

 

[MartinOdum]

Hello, I'm switching from skellett's mysql to this and I ran into an issue

command /mysqltest:
    trigger:
        execute "INSERT INTO data (`uuid`, `displayname`, `username`, `first login`, `last login`) VALUES (%uuid of player%, %display name of player%, %name of player%, %year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00, %year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00)" in {x_sql}

and this is my table setup, using skellett syntax but just to show the data types

mysql update "CREATE TABLE IF NOT EXISTS `data` (`uuid` varchar(255), `displayname` varchar(255), `username` varchar(255), `first login` DATETIME, `last login` DATETIME)"

So when I ran that command there would not be any changes in the database and I figured maybe it was something within the date formatting since its so long that some character in there might be screwing with the syntax or something so I shortened it to the following and it worked:

execute "INSERT INTO data (`uuid`, `displayname`, `username`) VALUES (%uuid of player%, %display name of player%, %name of player%)" in {x_sql}

So basically my question is do you know why it doesn't work with the first code? I don't get any errors or anything it just doesn't insert any data in


And this is what the database looks like, the first row is an input from a while ago using skellett's syntax and the second row is my shortened version of the code using skript-db

 

[Pikachu]

Hello, I'm switching from skellett's mysql to this and I ran into an issue

command /mysqltest:
    trigger:
        execute "INSERT INTO data (`uuid`, `displayname`, `username`, `first login`, `last login`) VALUES (%uuid of player%, %display name of player%, %name of player%, %year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00, %year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00)" in {x_sql}

and this is my table setup, using skellett syntax but just to show the data types

mysql update "CREATE TABLE IF NOT EXISTS `data` (`uuid` varchar(255), `displayname` varchar(255), `username` varchar(255), `first login` DATETIME, `last login` DATETIME)"

So when I ran that command there would not be any changes in the database and I figured maybe it was something within the date formatting since its so long that some character in there might be screwing with the syntax or something so I shortened it to the following and it worked:

execute "INSERT INTO data (`uuid`, `displayname`, `username`) VALUES (%uuid of player%, %display name of player%, %name of player%)" in {x_sql}

So basically my question is do you know why it doesn't work with the first code? I don't get any errors or anything it just doesn't insert any data in


And this is what the database looks like, the first row is an input from a while ago using skellett's syntax and the second row is my shortened version of the code using skript-db

I don't know the issue however the date formatting would be much better stored as epoch time

 

[btk5h]

Hello, I'm switching from skellett's mysql to this and I ran into an issue

command /mysqltest:
    trigger:
        execute "INSERT INTO data (`uuid`, `displayname`, `username`, `first login`, `last login`) VALUES (%uuid of player%, %display name of player%, %name of player%, %year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00, %year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00)" in {x_sql}

and this is my table setup, using skellett syntax but just to show the data types

mysql update "CREATE TABLE IF NOT EXISTS `data` (`uuid` varchar(255), `displayname` varchar(255), `username` varchar(255), `first login` DATETIME, `last login` DATETIME)"

So when I ran that command there would not be any changes in the database and I figured maybe it was something within the date formatting since its so long that some character in there might be screwing with the syntax or something so I shortened it to the following and it worked:

execute "INSERT INTO data (`uuid`, `displayname`, `username`) VALUES (%uuid of player%, %display name of player%, %name of player%)" in {x_sql}

So basically my question is do you know why it doesn't work with the first code? I don't get any errors or anything it just doesn't insert any data in

skript-db doesn't support dates right now, so you'll need work around this using skript-db's unsafe expression to bypass SQL injection protection.

set {_first login} to "'%year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00'"
set {_last login} to "'%year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00'"
execute "INSERT INTO data (`uuid`, `displayname`, `username`, `first login`, `last login`) VALUES (%uuid of player%, %display name of player%, %name of player%, %unsafe {_first login}%, %unsafe {_last login}%)" in {x_sql}

Also, for future reference, skript-db errors are kept in a separate "last sql error" expression.

 

[MartinOdum]

I don't know the issue however the date formatting would be much better stored as epoch time

good idea, I'll probably switch to that, thanks!

skript-db doesn't support dates right now, so you'll need work around this using skript-db's unsafe expression to bypass SQL injection protection.

set {_first login} to "'%year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00'"
set {_last login} to "'%year from date now%-%month from date now%-%day of month from date now% %hours from date now%:%minutes from date now%:00'"
execute "INSERT INTO data (`uuid`, `displayname`, `username`, `first login`, `last login`) VALUES (%uuid of player%, %display name of player%, %name of player%, %unsafe {_first login}%, %unsafe {_last login}%)" in {x_sql}

Also, for future reference, skript-db errors are kept in a separate "last sql error" expression.

Ah i see, thanks for the info and quick reply!

 

[FUZIK]

please add to `todo` list

Spoiler: skript

command sql:
    trigger:
        execute "select `default` from `sql-database`" in {sql} and store result in {_test::*}
        broadcast "test:%{_test::default::*}%"

        set {_arg} to "default"
        execute "select %{_arg}% from `sql-database`" in {sql} and store result in {_test_arg::*}
        broadcast "test_arg:%{_test_arg::%{_arg}%::*}%"

Spoiler: in console

[INFO]: test:data and store
[INFO]: test_arg:data, data and data

ps this is bug report)

 

[btk5h]

please add to `todo` list

Spoiler: skript

command sql:
    trigger:
        execute "select `default` from `sql-database`" in {sql} and store result in {_test::*}
        broadcast "test:%{_test::default::*}%"

        set {_arg} to "default"
        execute "select %{_arg}% from `sql-database`" in {sql} and store result in {_test_arg::*}
        broadcast "test_arg:%{_test_arg::%{_arg}%::*}%"

Spoiler: in console

[INFO]: test:data and store
[INFO]: test_arg:data, data and data

ps this is bug report)

Could you post more details about how your database and tables are set up so I can replicate this?

 

[FUZIK]

Could you post more details about how your database and tables are set up so I can replicate this?

what information about the database do you need?

 

[btk5h]

what information about the database do you need?

From an empty database, what queries would I execute to set up tables and entries?

 

[pepper82]

I keep getting "Too many connections" error after some time (like when executing 5 insert - operations in a row). And my max-connections in mysql is set to 100. Are you closing them correctly?

https://hastebin.com/upiviyifab.swift

 

[btk5h]

I keep getting "Too many connections" error after some time (like when executing 5 insert - operations in a row). And my max-connections in mysql is set to 100. Are you closing them correctly?

https://hastebin.com/upiviyifab.swift

That's strange, even if the connections weren't being closed correctly, the connection pool should be limited to 10 connections. I'll look into it, but I'll need more information to replicate this issue.

 

[pepper82]

What information do you need?

 

[btk5h]

What information do you need?

I just need enough information to reproduce the issue on my own machine.

 

[pepper82]

So there is the script that causes this error when you enter the command ~7 times in a row:

options:
    database: yourdatabase
    user: youruser
    password: yourpassword

command /mysqlinsertcode:
    trigger:
        set {_code} to "test"
        set {_sql} to the database "mysql://localhost:3306/{@database}?user={@user}&password={@password}&useSSL=false"
        execute "INSERT INTO yourtable (`username`, `uuid`, `code`) VALUES (%name of player%, %uuid of player%, %{_code}%)" in {_sql}

Database: Server-Version: 10.1.26-MariaDB - mariadb.org binary distribution
Skript: latest bensku skript release
Server: PaperSpigot (latest 1.12.2)

 

[xXAndrew28Xx]

So there is the script that causes this error when you enter the command ~7 times in a row:

options:
    database: yourdatabase
    user: youruser
    password: yourpassword

command /mysqlinsertcode:
    trigger:
        set {_code} to "test"
        set {_sql} to the database "mysql://localhost:3306/{@database}?user={@user}&password={@password}&useSSL=false"
        execute "INSERT INTO yourtable (`username`, `uuid`, `code`) VALUES (%name of player%, %uuid of player%, %{_code}%)" in {_sql}

Database: Server-Version: 10.1.26-MariaDB - mariadb.org binary distribution
Skript: latest bensku skript release
Server: PaperSpigot (latest 1.12.2)

I wonder if this is a MariaDB specific issue. There is a JDBC driver for MariaDB.

 

[MartinOdum]

Hey is there any way to run sql code under a return function? Basically I have a bank system and I'd like to have a function that would return the player's bank balance and then I would just run the function under async, the code below is what I'm talking about

function checkbankbalance(p: player) :: number:
    async:
        execute "select * from BankData where uuid = %{_p}'s uuid%" in {x_sql} and store the result in {_result::*}
    if {_result::balance::*} is not set:
        return 0
    else:
        set {_balance} to "%{_result::balance::*}%" parsed as number
        return {_balance}
command /bankbalance:
    trigger:
        async:
            set {_balance} to checkbankbalance(player)
        send "&aBank: &c$%{_balance}%" to player

At the moment the code doesn't work I get the skript error: "A return statement after a delay is useless, as the calling trigger will resume when the delay starts..."