 |
Conclave
Newbie
Joined: 25 Oct 2007
Posts: 1
|
 Posted: Thu Oct 25, 2007 4:28 pm
Cmud - SQL Server 2005
|
I am looking for some help on linking a SQL server 2005 database up with CMUD. Ideally whenever an item is identified, the trigger will fire to insert into the database the values that are captured. I can write the query just fine for the SQL syntax, but I am clueless as to how to configure CMUD to capture this data and submit it in the correct format in real time. Ideally, this script will also capture anomalies in data (i.e. item #12345 has been altered since it's last capture, or some such), so any help is appreciated.
The databases are already created and I know the text trigger that the scripts will fire on, I just need help configuring CMUD. Any help is appreciated.
darkconclave@comcast.net |
|
|
|
|
Rainchild
Wizard
Joined: 10 Oct 2000
Posts: 1551
Location: Australia
|
Posted: Thu Oct 25, 2007 10:59 pm
|
I posted this a long time back on the zMUD forums, you probably can use the same syntaxes in CMUD, but I don't guarantee it.
| Rainchild wrote: |
Note: This is written with SQL Server 2000 / MSDE 2000 in mind... we haven't yet made the switch to SQL Server 2005 at work so don't know if this will be 100% compatible with SQL 2005. Also, all references to file paths/etc assume default installation on Windows XP Pro.
Before you start:
- make sure SQL server is set for 'mixed mode' logins (registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode = 0x00000002 (2)) See here for more details.
- make sure SQL server is set for 'TCP/IP' protocol (the config program is C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe)
- restart SQL server to pick up the changes
(you can also specify these options from the command line as you are installing SQL Server for the first time, consult the documentation)
You will also need to create an ODBC definition:
Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC) -> System DSN -> Add -> Select 'SQL Server' from the list -> Finish -> Give your datasource a name. I have used 'MSSQLDS' for my example. Select your server from the drop down. For the server on your local machine you can just type in '(local)'. Hit next. Choose "With SQL Server authentication using a login ID and password entered by the user". Enter in the login and password, click next. Leave everything at defaults. Click next. Leave everything at defaults. Click finish. Click 'Test Data Source'... should say successful, if not, check the server is running, the login/password is specified correctly, etc. Click 'OK' to all windows and close the control panel.
You can now get direct access to the database and execute SQL statements on it as follows:
First create an ADODB Connection:
| Code: |
| #VAR ADOConnection %comcreate( "ADODB.Connection" ) |
Then define the connection string (replace the login, password, data source, and initial catalog with the appropriate values for your setup):
| Code: |
| #NOOP %comset(ADOConnection,"ConnectionString","Driver={SQL Server};user id='sa';password='my_password';data source=MSSQLDS;initial catalog=my_mud_db;") |
Note: if you haven't created a database yet, set the initial catalog to 'master', execute the 'create database xxx' statement, then change the initial catalog to your databases name.
Then open the database:
| Code: |
| #CALL @ADOConnection.Open |
In order to query the database, you need to crate a recordset object:
| Code: |
| #VAR ADORecordSet %comcreate( "ADODB.Recordset" ) |
This is how you execute a SQL query:
| Code: |
| #CALL @ADORecordSet.Open( "SELECT * FROM my_table WHERE my_id='123'", @ADOConnection ) |
You can now get access to the results with (using either an integer column offset, or the column name):
| Code: |
| #SAY @ADORecordSet.Fields( my_column ).Value |
When you're done with that SQL statement close the recordset:
| Code: |
| #CALL @ADORecordSet.Close |
If you're dealing with multiple rows in a recordset then you might find the following functions useful:
| Code: |
#SAY @ADORecordSet.EOF - returns -1 if you're at the end of the recordset
#CALL @ADORecordSet.MoveNext - moves to the next record
#CALL @ADORecordSet.MoveFirst - moves to the first record |
If you want to write to/delete from the database (eg execute a non-query), you don't need to use a recordset object, you can just execute a statement on the connection as follows:
| Code: |
| #CALL @ADOConnection.Execute( "INSERT INTO my_table( my_id, my_column ) VALUES( 123, 'testing' )" ) |
Once you're done executing all your SQL queries and want to release the resources, execute the following:
| Code: |
#CALL @ADORecordSet.Close - if you haven't already done it
#CALL @ADOConnection.Close
#UNVAR ADORecordSet
#UNVAR ADOConnection |
The way I typically call a SQL statement while developing my triggers/etc is (this is an example from my XP tracker):
| Code: |
#VARIABLE SQL {INSERT INTO xp( killdate, mobname, tankname, xpgained, xplost, numflees ) VALUES ( GETDATE( ), '@killedmob', '@tank', %1, @lost, @flees )}
#CALL @ADOConnection.Execute( @SQL )
#SAY ~> @SQL |
The #SAY ~> @SQL lets me see exactly what is being executed, so if it fails I can look at the finished syntax and go 'hmm, that looks wrong' and fix it. In my above example, the killdate column is of type 'datetime' and I record a timestamp - the call to GETDATE() in SQL returns the current date/time. The next two columns are text cones - hence surrounded by single quotes. Then is the %1 - thats actually the experience readout from a trigger "You gain (%d) experience." Followed by two more variables containing integers of the total xp lost doing that fight, and the total number of flee's. The reason I record that is you can backstab a mob, flee (lose exp), hide, go back, backstab it again... so I log the amount lost vs the amount gained to work out how much in total I got.
Hope that example helps some more.
To execute a stored proecdure, I believe the syntax is:
| Code: |
| #CALL @ADOConnection.Execute( "EXEC dbo.stored_proc_name 'testing', 123" ) |
This assumes you have a level of experience with using SQL statements to do things, but you should be able to call stored procedures, create tables, drop databases, whatever you want to do.
Check the Microsoft documentation for SQL syntax and examples at http://msdn.microsoft.com
I hope this helps you get going.
|
|
|
|
|
|
Seb
Wizard
Joined: 14 Aug 2004
Posts: 1269
|
Posted: Sat Oct 27, 2007 12:37 am
|
#NOOP %comset ...
might need changing to:
#CALL %comset ...
in CMUD, as there are some instances where #NOOP %someFunction() need to be changed to #CALL. |
|
|
|
|
Fang Xianfu
GURU
Joined: 26 Jan 2004
Posts: 5155
Location: United Kingdom
|
Posted: Sat Oct 27, 2007 3:05 am
|
I don't think there are any instances where it needs to be changed, just that it should be. Noop is only really there any more for compatability.
|
|
|
|
|
Seb
Wizard
Joined: 14 Aug 2004
Posts: 1269
|
Posted: Sat Oct 27, 2007 4:20 pm
|
There was one time I suggested someone changed it in their script, and it started working for them...
|
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
