Resource icon

Addon skript-db 0.2.0

  • Welcome to skUnity!

    Welcome to skUnity! This is a forum where members of the Skript community can communicate and interact. Skript Resource Creators can post their Resources for all to see and use.

    If you haven't done so already, feel free to join our official Discord server to expand your level of interaction with the comminuty!

    Now, what are you waiting for? Join the community now!

btk5h

Addon Developer
Jan 25, 2017
154
159
0
Pyongyang, North Korea
Hi, :emoji_grinning:

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.
 
Last edited:
Hi, :emoji_grinning:

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:
code_language.skript:
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.
 
Thanks for your fast answer. :emoji_grinning:

The async expression from MundoSK changes anythink.
The expression "execute" is like a "wait" for Skript.
 
Last edited:
Thanks for your fast answer. :emoji_grinning:

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.
 
Hello, I'm switching from skellett's mysql to this and I ran into an issue
code_language.skript:
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
code_language.skript:
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:
code_language.skript:
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
Yhp3tVL.png
 
Hello, I'm switching from skellett's mysql to this and I ran into an issue
code_language.skript:
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
code_language.skript:
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:
code_language.skript:
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
Yhp3tVL.png
I don't know the issue however the date formatting would be much better stored as epoch time
 
  • Like
Reactions: MartinOdum
Hello, I'm switching from skellett's mysql to this and I ran into an issue
code_language.skript:
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
code_language.skript:
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:
code_language.skript:
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.
code_language.skript:
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.
 
  • Like
Reactions: 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.
code_language.skript:
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!
 
please add to `todo` list :emoji_fingers_crossed:
code_language.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}%::*}%"
code_language.skript:
[INFO]: test:data and store
[INFO]: test_arg:data, data and data
ps this is bug report)
 
please add to `todo` list :emoji_fingers_crossed:
code_language.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}%::*}%"
code_language.skript:
[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?
 
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.
 
So there is the script that causes this error when you enter the command ~7 times in a row:

code_language.skript:
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)
 
So there is the script that causes this error when you enter the command ~7 times in a row:

code_language.skript:
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.
 
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

code_language.skript:
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..."