[GUIDE] Run SQL Query from LUA script

Quote:

Originally Posted by c1ph3r 1. os.execute will cause a short freeze for every 2. Even summon slots and creature code ;)

1. i dont think that
echo text > filename
will take more than couple of msec=)
same with some loops in script as in creaturesetup.lua where is used FOR loop script to find all creatures
2. tested, idk why, but doesnt work. ive tried to write a code and tried it many times, for gcv it works with hp, lv, jp but dont work with code and something else from there. same with slot_* in character table - doesnt return values when GV...
even if u wouldn't use this in LUA, i will xD
ismoke said that he will, too. as i understood from his words.
and i have some work now to modify the schema to get values from database, not only set it. it will be much useful in combination with shit that ive written here xD

Quote:

Originally Posted by mongreldogg 1. i dont think that echo text > filename will take more than couple of msec=) same with some loops in script as in creaturesetup.lua where is used FOR loop script to find all creatures 2. tested, idk why, but doesnt work. ive tried to write a code and tried it many times, for gcv it works with hp, lv, jp but dont work with code and something else from there. same with slot_* in character table - doesnt return values when GV... even if u wouldn't use this in LUA, i will xD ismoke said that he will, too. as i understood from his words. and i have some work now to modify the schema to get values from database, not only set it. it will be much useful in combination with shit that ive written here xD

1. os.execute = EVERYTIME feelable freeze for all player because gs is on hold
for loop = one player and no freeze cause gs is running the whole time

2. never talked about the gv function...it's more a combination of some functions...

@smoke: nice a free rebirthscroll xD you can use the rebirthscroll functions to do sth like this. 1. skill+jobreset 2. set class via .lua. This will need 1 flag for the class.

So we know indisputably that all commands issued by the GS will cause minor hang-times? Or just os.execute? I looked around for a method of writing to a file from LUA that did not involve OS.Execute and came up with this:

Quote:

Originally Posted by ac-web-forums Code: Hey everyone, I decided I would share some of my knowledge of Lua, so I thought a bit and chose that I'd make a tutorial on file input/output with Lua. Without further ado, we shall begin... In order to write to a file in Lua, we must first have the actual file itself created. In this tutorial I'll be using a file named example.txt. To write to my file, I first must actually 'open' the file in Lua. Note: the file isn't actually opened when you do this. Code: local file = io.open("example.txt", "w") The above opens the file example.txt in 'write' mode. So now, the pointer for the file is a local variable called file. Note: Write mode will overwrite any text in the file each time the variable - file is called upon. Now we want to actually write something to the file. So we use this: Code: file:write("Lalala. Alvanaar is amazing.") That function writes the text in red to our file - file ('example.txt'). Easy. We then need to close the file: Code: file:close() Our file example.txt will now look like this: Originally Posted by example.txt Lalala. Alvanaar is amazing. And there you go. That's how you write to a file with Lua. Very simple. Below is a list of functions, etc you can use for file i/o. Basic file operations and useful symbols, etc: Code: local file = io.open("filename", "mode") - file is now the file - "filename" - mode: "w" = write mode (all file text is erased) - mode: "a" = append mode (used for adding onto the end of the file) file:write("text") - writes "text" to file file:flush() - saves the file - file file:close() - 'closes' and saves the file - file \n - represents a break (like pressing the enter key or return key) o e.g. file:write("Hi\nHow are you?") will appear like so: Hi How are you?

This is a very interesting chain of thought, I would like to see where it goes.

P.S.

I stand corrected about not effecting the GS, was not realizing that the GS would have to execute a DOS Command and had to wait for a return code from it.

Os.execute is calling the Shell to execute the command nothing more.

For the Rest of you questions LUA is able to do almost everything but the needed libraries have to be in the GS. There are lots of SQL functions in LUA but won't work on rappelz cause some libraries are missing. Don't know if it's possible to Update the .lua runtime in the GS. I'm pretty sure there is no native way in the Rappelz GS to write files with lua. There is no need for a function like that.

I think the whole goal of this is not to write files from lua or to execute sql cmd's from lua but for me to be able to set/retrieve custom variables in-game.

- Example User takes Hero Trial Quest
-- Users Class ID is stored via Custom Variable
-- Users Current LV, JLV are stored via Custom Variable
-- User Quest Status gets set via dbo.Quest

- User completes Hero Trial Quest
-- Users Class ID, LV, JLV are retrieved from Custom Variable
-- Users Class, LV and JLV are returned to normal
-- User Quest Status gets set via dbo.Quest
-- User Custom Variable is_hero set to 1

I think the need to read/store custom data would be incredibly useful for a situation such as this. This is achievable using the get/set_flag method but the results would be invisible out of game, furthermore trying to update a new "column" such as col_example with lua in-game via sv/gv won't work to my knowledge as any field the GS is not programmed to know of will be ignored. Like say you added col_example to dbo.Character and put a 1 there the lua will refuse to set or get the value for this field.

Flags are in dbo.Character and accessible ingame...where is your problem. If you want to modify telecaster you have to modify the GS!

Where is the difference of a new field like col_abc and the flag column in dbo.Character. If that isn't enough use global variables which are in DB and Ingame too. Use the given functions instead of third party custom bullshit which will make the server unstable. Don't use third party programs if there isn't a need to use programs like that.

I knew that the flags got set to dbo.character just wasn't sure how to read them (as I have never tried) but I had already figured it would be easy. However the thought of having seperated/organized and specifically defined info would have been a more stable route in the end.

Quote:

Originally Posted by c1ph3r Flags are in dbo.Character and accessible ingame...where is your problem. If you want to modify telecaster you have to modify the GS! Where is the difference of a new field like col_abc and the flag column in dbo.Character. If that isn't enough use global variables which are in DB and Ingame too. Use the given functions instead of third party custom bullshit which will make the server unstable. Don't use third party programs if there isn't a need to use programs like that.

difference is: flag list can not include very much flags (VARCHAR 1000)

Eh then all you'd have to do is change it to VARCHAR(MAX) -- the max character limit for any field is 8000 (8k) characters. This however can be extended to upwards of 10,15k with proper setup. So flaglist can actually be REALLY long :D

Quote:

Originally Posted by ismokedrow However the thought of having seperated/organized and specifically defined info would have been a more stable route in the end.

right. much easier to work with tables if u edit a program for example, make advanced constrution to work with such a table using simple LUA functions. problem can be just in programmer's skills to do it.
thats not a problem to make advanced functions using simple functions of LUA and connection to database. we can make universal system to work with any table in DB as we need. its a possibility to make much more than new values or logging some events using flags and global variables (using global variables is a rubbish for database if we would use it as alternative to flags, cuz there is only 2 columns and if we need to use much more values for 1 character and our flag_list is full, we are decided to use globals and soon our DB will sunk in da shit)

Quote:

Originally Posted by ismokedrow So flaglist can actually be REALLY long :D

and its not a feature right?
its even hard for adm to look there and try to understand where is the shit he wants.
moreover, sometimes if u try to modify flag_list in database by yourself, you can get char bugged and BYEBYE char

Quote:

Originally Posted by c1ph3r Flags are in dbo.Character and accessible ingame...where is your problem. If you want to modify telecaster you have to modify the GS!

much better to FIND IT USEFUL than FIND IT USELESS =)

Quote:

Originally Posted by mongreldogg right. much easier to work with tables if u edit a program for example, make advanced constrution to work with such a table using simple LUA functions. problem can be just in programmer's skills to do it. thats not a problem to make advanced functions using simple functions of LUA and connection to database. we can make universal system to work with any table in DB as we need. its a possibility to make much more than new values or logging some events using flags and global variables (using global variables is a rubbish for database if we would use it as alternative to flags, cuz there is only 2 columns and if we need to use much more values for 1 character and our flag_list is full, we are decided to use globals and soon our DB will sunk in da shit) and its not a feature right? its even hard for adm to look there and try to understand where is the shit he wants. moreover, sometimes if u try to modify flag_list in database by yourself, you can get char bugged and BYEBYE char much better to FIND IT USEFUL than FIND IT USELESS =)

okay after reading that...you are talking about programmer skills ask a dev what he is thinking about a program which is calling a runtime which is calling the servershell to execute a simple sqlcommand. nothing more to say

and till now i don't get the problem with the flag list...three lines of code to get them into an array/vector...which is as easy to handle as a database^^ and on top of that it's a native server feature which should be stable^^

Quote:

Originally Posted by c1ph3r okay after reading that...you are talking about programmer skills ask a dev what he is thinking about a program which is calling a runtime which is calling the servershell to execute a simple sqlcommand. nothing more to say

I lol´d :D

Quote:

Originally Posted by c1ph3r okay after reading that...you are talking about programmer skills ask a dev what he is thinking about a program which is calling a runtime which is calling the servershell to execute a simple sqlcommand. nothing more to say and till now i don't get the problem with the flag list...three lines of code to get them into an array/vector...which is as easy to handle as a database^^ and on top of that it's a native server feature which should be stable^^

mate, i dont want to argue bout this, i just need some progress. im working on it and trying to use different ways. if talk about features - every new shit that released can make some features. and if this shit costs some attention, there is a good reason to work and make something better secured and featured. and why da hell can't u understand - i didnt gave something totally finished. i gave a main idea and yes i want damn conversation that we have now cuz i want progress as i said.
but i also want constructive dialog.
okay, what about os.execute - ye not easy way. for example, much easier would be if we use set_global_variable to write query into database and configure our program to execute all queries written into this table automatically. of cuz there would be some problems with using SELECT operator for getting Glory of God, for example :D
and i fcka tired to make examples again and again=)
im working. thanx for your comments. i found something useful in ur comments but as i see, you didnt do the same reading mine=)

Quote:

Originally Posted by c1ph3r program which is calling a runtime which is calling the servershell to execute a simple sqlcommand

let me edit:
program which is calling a runtime to write some text into file.
another program runs queries from damn file. queries execution doesn't touch GS.
k sure bad idea. oh no sorry, wrong. BAD ATTEMPT

Quote:

Originally Posted by mongreldogg mate, i dont want to argue bout this, i just need some progress. im working on it and trying to use different ways. if talk about features - every new shit that released can make some features. and if this shit costs some attention, there is a good reason to work and make something better secured and featured. and why da hell can't u understand - i didnt gave something totally finished. i gave a main idea and yes i want damn conversation that we have now cuz i want progress as i said. but i also want constructive dialog. okay, what about os.execute - ye not easy way. for example, much easier would be if we use set_global_variable to write query into database and configure our program to execute all queries written into this table automatically. of cuz there would be some problems with using SELECT operator for getting Glory of God, for example :D and i fcka tired to make examples again and again=) im working. thanx for your comments. i found something useful in ur comments but as i see, you didnt do the same reading mine=) let me edit: program which is calling a runtime to write some text into file. another program runs queries from damn file. queries execution doesn't touch GS. k sure bad idea. oh no sorry, wrong. BAD ATTEMPT

As I said do what you want and i'm reading all of your attemps to ignore the biggest problem on your os.execute...the short freeze. Till now there is now way to fix this and there won't be a way to fix it in the future.

But if it's all that you want: Hey mongreldogg it's a great idea to call the Lua Runtime to call the Server Shell to write a textfile with the echo command. And there is no argue at first it was a discussion now it's trolling :D

Way to go: Wirte a programm using the (oldfashioned) Telnetinterface while keeping a SQL Connection open.
Problem: No real trigger for the program to grab data. So it could only work in interval (not that fast, since the telnetinterface isn't that fast).
Specifics: Communication via ENVs. Nothing will be stored, everything will be kept in RAM. You will need a little lua tho.

eg.
You start the server and the "addon". Addon connects to SQL and checks via telnet "GET newcommand" every second waiting for a 1 there. GM wants to Edit something in SQL and writes "/run fancyluaname("Delete * from character"). The lua will:
check whether get_env("newcommand") == 1 and write an Error, so you have to retry in some time again or else set_env("newcommand",1); set_env("command","Delete * from character"). The addon eventually notices that newcommand = 1 and grabs "command" aswell. Executes n shit and afterwards sets newcommand to 0. Technically it could also return a single value (or lines via sql print) to the gs, save as env and broadcast it to the person that issued the command.

Without a decent multithreading/queue design this is no option for direct player interaction. But it could be handy for some gm, admin luas. (quick leveling+staging pets or other things).

I found a good telnetbasis for a program like this, but it requiers some mods, since you only have a passwordrequest and the stupid interface is working with NULL and some other freaky things :D