How long time we were searching for solution?
Lets have some guide and use it to f**k officials by doggie style=)
Everything we must have - one function, one program and one brain to find it useful.
UPDATED! Changed a working schema to keep a good performance.
There is some files in the pack below:
Lua script for such a function sql_query.
VB Source code for Console SQL Querier (CSQLQ.exe)
Folder with all files to put into GS folder
How to use: put all files into GS folder, restart a server and start CSQLQ.exe
then use sql_query function from anywhere using LUA.
To see a progress, look at CSQLQ console.
If you wanna get logfile - change a code
Finally, we have a solution to run SQL query from any place in LUA script.
P.S. command doesnt work with SELECT operator to return values. It can be used for example, with UPDATE, DELETE or INSERT. But you can edit a program and LUA script to do it.
P.P.S. dont push me with my **** VB script lol its just a main idea how to run queries secure and easy, not more=) If you can make it better - make it better!
This isn't the best way. The GS will freeze till it gets a returncode from the program. It will cause horrible lags/freezes if the commandexecution will take some time.
I tried something like this some time ago and server crashed lots of times if there are more than 20 players.
Oh and it's a biiiiiig lag of security. In my opinion nobody should use this on a productive Server if he don't know the mssql functions and what everyone with permission 100 is able to do with a function like that.
@Mongledogg: your first p.s. is wrong. you are able to do much more with that function.
This isn't the best way. The GS will stop till it gets a returncode from the program. It will cause horrible lags if the commandexecution will take some time.
This isn't the best way. The GS will stop till it gets a returncode from the program. It will cause horrible lags if the commandexecution will take some time.
so where is the best solution?
and what about coroutines in LUA?
Something like this used for only background updates (e.g. things that aren't dynamically used by the gs) would be wonderful -- saying you don't allow anyone to use it (e.g. keeping it out of dbo.allowedcommandsforpermission) The security implications are minimal saying you have a secured machine (within reason). As far as the way the GS would respond the impact would also be minimal.
For example I am creating a new series of quests in which the user must shift from one class to another while info about said class gets stored (so that when they take their main class back certain values are returned: level, jp, jlv etc..) Originally I thought of doing such with set_flag/get_flag but this method would be far better. As the variables I set would have no true bearing on the data being used by the GS I see no reason why the GS would act oddly.
I look forward to testing some implications of this method, thank you.
Something like this used for only background updates (e.g. things that aren't dynamically used by the gs) would be wonderful -- saying you don't allow anyone to use it (e.g. keeping it out of dbo.allowedcommandsforpermission) The security implications are minimal saying you have a secured machine (within reason). As far as the way the GS would respond the impact would also be minimal.
For example I am creating a new series of quests in which the user must shift from one class to another while info about said class gets stored (so that when they take their main class back certain values are returned: level, jp, jlv etc..) Originally I thought of doing such with set_flag/get_flag but this method would be far better. As the variables I set would have no true bearing on the data being used by the GS I see no reason why the GS would act oddly.
I look forward to testing some implications of this method, thank you.
ye, maybe much better to use flags and global variables in scripts to make some advanced scripts, but as i said:
we must find it useful.
as example, we can not get some values from dbo.Character and dbo.Summon using simple gv or gcv commands (such as summon slots or creature code, and i was looking for solution to find it and use in script).
of course proggie can be modified to work with SELECT operator and get values we need using queries. and we can also use LUA coroutines, we can use everything we know and will know getting experience in our work.
and as i said: thats just a main idea. modifying of this feature is work that can give a results, isnt it?
about security - if you have bad secured machine, you will lost database or GS with and without this feature=)
Quote:
Originally Posted by c1ph3r
@Mongledogg: your first p.s. is wrong. you are able to do much more with that function.
i thought the same when i tried to test but i see that without modifying a proggie or sql query function, i cant return values that i got by SELECT
*edit : ye right sorry. updated main thread
Quote:
Originally Posted by ismokedrow
I see no reason why the GS would act oddly.
C1ph3r's right. if we won't use something like threading, GS will freeze until program returns that query's done. because os.execute command runs and it will stop only when program returns something like 'done' or it crashes or closes itself. so if we will make query that will be executed by 10 or 15 sec, all this time GS will be freezed (as with WHILE operator, for example).
you should use SQL Server Management Studio to do that, a SQL Server is not local only, it's a real server which you can open to the internet. But you have to add some security like a password for sa to do that (but that's would be definitely much secure and stable than using the gameserver)
If you don't know how to have a well secured server, then make your CQSQL.exe to be a server. Use of SSH can be a solution to limit security consideration in the CQSQL.exe program. .NET can offer some encryption algorithm if needed
you should use SQL Server Management Studio to do that, a SQL Server is not local only, it's a real server which you can open to the internet. But you have to add some security like a password for sa to do that (but that's would be definitely much secure and stable than using the gameserver)
If you don't know how to have a well secured server, then make your CQSQL.exe to be a server. Use of SSH can be a solution to limit security consideration in the CQSQL.exe program. .NET can offer some encryption algorithm if needed
Something like this used for only background updates (e.g. things that aren't dynamically used by the gs) would be wonderful -- saying you don't allow anyone to use it (e.g. keeping it out of dbo.allowedcommandsforpermission) The security implications are minimal saying you have a secured machine (within reason). As far as the way the GS would respond the impact would also be minimal.
For example I am creating a new series of quests in which the user must shift from one class to another while info about said class gets stored (so that when they take their main class back certain values are returned: level, jp, jlv etc..) Originally I thought of doing such with set_flag/get_flag but this method would be far better. As the variables I set would have no true bearing on the data being used by the GS I see no reason why the GS would act oddly.
I look forward to testing some implications of this method, thank you.
1. You can't dissallow anything for permission 100. Only possible to dissallow script execution via gameserver.opt and this feature is bugged.
2. Since it's possible to execute almost every dos command via sql commands directly on the root machine it's one of the biggest security issues in my opinion.
3. Keep this in your mind: Every player will have a short gamefreeze during the SQL Command!
The functions of the cqsql should be limited for security reasons. Or the user rights for the executing user must be limited!
3. Keep this in your mind: Every player will have a short gamefreeze during the SQL Command!
updated main thread=)
found a solution. diferrence is:
lua function just writes query into file.
proggie opens a file and executes a query written there every 0.1 seconds.
i think its much better for performance and it will stop freezes
Quote:
Originally Posted by c1ph3r
1. You can't dissallow anything for permission 100. Only possible to dissallow script execution via gameserver.opt and this feature is bugged.
another way, you can give permission 100 to NOBODY.
and you can manage permissions list in database (i already did)
updated main thread=)
found a solution. diferrence is:
lua function just writes query into file.
proggie opens a file and executes a query written there every 0.1 seconds.
i think its much better for performance and it will stop freezes
another way, you can give permission 100 to NOBODY.
and you can manage permissions list in database (i already did)
yeah great idea every 0.1 ms a new sql connection let the gs crash.
but hey it's only my opinion and I personally don't like that way^^ Everyone can use it. But keep in mind what you are doing with a function like that.
Connection.ConnectionString = "Data Source=127.0.0.1;Initial Catalog=Telecaster;User ID=sa;Password="
Connection.Open()
Dim i As Integer = 0
While i < 1
aTimer = New System.Timers.Timer(10000)
AddHandler aTimer.Elapsed, AddressOf OnTimedEvent
aTimer.Interval = 15
aTimer.Enabled = True
Console.ReadLine()
End While
Connection.Close()
every 0.1 ms - just checking files for existing query text and executes query ONLY IF IT EXISTS and after clearing a file.
If IO.File.ReadAllText(file1) = "" Then
'here is nothing to do xD
Else
Try
myCmd.CommandText = IO.File.ReadAllText(file1)
IO.File.Create(file1)
myCmd.ExecuteNonQuery()
Console.WriteLine("Query execution successful!")
Catch ex As Exception
Console.WriteLine("ERROR occured when tried to execute SQL query!!!")
End Try
End If
Quote:
Originally Posted by c1ph3r
but hey it's only my opinion and I personally don't like that way^^ Everyone can use it. But keep in mind what you are doing with a function like that.
i understand and as i said and will say again
we must find it useful
even if its useless.
even more, its really useful i think
Connection.ConnectionString = "Data Source=127.0.0.1;Initial Catalog=Telecaster;User ID=sa;Password="
Connection.Open()
Dim i As Integer = 0
While i < 1
aTimer = New System.Timers.Timer(10000)
AddHandler aTimer.Elapsed, AddressOf OnTimedEvent
aTimer.Interval = 15
aTimer.Enabled = True
Console.ReadLine()
End While
Connection.Close()
every 0.1 ms - just checking files for existing query text and executes query ONLY IF IT EXISTS and after clearing a file.
If IO.File.ReadAllText(file1) = "" Then
'here is nothing to do xD
Else
Try
myCmd.CommandText = IO.File.ReadAllText(file1)
IO.File.Create(file1)
myCmd.ExecuteNonQuery()
Console.WriteLine("Query execution successful!")
Catch ex As Exception
Console.WriteLine("ERROR occured when tried to execute SQL query!!!")
End Try
End If
i understand and as i said and will say again
we must find it useful
even if its useless.
even more, its really useful i think
my fault but as i said i wouldn't use this in .lua. it's not very elegant because every os.execute will cause a short freeze for every player.
and for me there is no need to execute sql via .lua. You can modify everything with the given functions. Even summon slots and creature code
[Guide]how to add pets/mounts to your server and how to make a query 07/19/2013 - EO PServer Guides & Releases - 26 Replies i made that post because more than 10 memmbers from Epvp asked me to teach them how to add a new pet or mount . Hope you like it ;)
------------------------------------------------- -
first of all here's how u can make a query :-
READ THE SECOND BEFORE THIS ONE
first
delete from cq_action where id >= 727440 and id <= 728445;
insert into cq_action (id,id_next,id_nextfail,type,data,param)
values
[Suche]Query für "Alteklinge, Reichsklinge" &' Stichdolche NUR QUERY! 08/18/2010 - Metin2 Private Server - 1 Replies Ich weiß ich werd nervig aber ich suche die Query's
für Alteklinge, Reichsklinge &' Strichdolche
Why?
Naya, weil ich in der DB i-wie nur Drachenmaulglocke hab
... not more -.-
Auf jeden Fall Icon's etc. etc. etc. hab ich ich brauche nur die Query's
![[GUIDE] Run SQL Query from LUA script](https://www.elitepvpers.com/forum/iconimages/rappelz-private-server/guide-run-sql-query-lua-script_ltr.gif){kind=small}
let the gs crash. 
