 |
jennyA
Novice
Joined: 13 Oct 2003
Posts: 31
|
 Posted: Thu Oct 23, 2003 5:32 am
SQL Server
|
Anyone accessing a SQL Server database via zMud?
|
|
|
|
|
Darker
GURU
Joined: 24 Sep 2000
Posts: 1237
Location: USA
|
Posted: Fri Oct 24, 2003 7:29 pm
|
I'm not and havent, but you could do this with VBScript inside zMUD - just use ADODB objects like you would in an ASP or WSH script.
|
|
|
|
|
Ryntrax
Wanderer
Joined: 15 Mar 2004
Posts: 55
Location: USA
|
Posted: Thu Mar 18, 2004 2:35 pm
|
Just posting to get it to the top of the list For an update.
|
|
|
|
|
Darker
GURU
Joined: 24 Sep 2000
Posts: 1237
Location: USA
|
Posted: Thu Mar 18, 2004 3:02 pm
|
Erm, what do you need an update on? I already said what tools to use.
|
|
|
|
|
Carabas
GURU
Joined: 28 Sep 2000
Posts: 434
Location: USA
|
Posted: Thu Mar 18, 2004 7:36 pm
|
Using zMUD and COM would work just as well. I haven't used zMUD to connect to a MS SQL server, but it should be similar to connecting to a MySQL Server.
#ALIAS myInit {
#VARIABLE myConn ""
#VARIABLE myConn %comcreate("ADODB.Connection")
#VARIABLE myConn.ConnectionString @myConnStr
#CALL @myConn.Open
#VARIABLE myRs ""
#VARIABLE myRs %comcreate("ADODB.Recordset")
#VARIABLE myRs.ActiveConnection @myConn
#VARIABLE myRs.CursorLocation 3 // adUseClient
#VARIABLE myRs.CursorType 3 // adOpenStatic
#VARIABLE myRs.LockType 3 // adLockOptimistic
}
#ALIAS myClose {
#CALL myConn.Close
#CALL myRs.Close
#VARIABLE myConn ""
#VARIABLE myRs ""
}
#ALIAS myExec {
#CALL @myRs.Open(@mySQL)
}
#ALIAS myAddNew {
#CALL @myRs.AddNew
#VARIABLE myRs("ObjName").Value "the shield of Adumbration"
#VARIABLE myRs("ObjType").Value "Armor"
#VARIABLE myRs("ObjLoc").Value "Shield"
#VARIABLE myRs("ObjWeight").Value "7"
#VARIABLE myRs("ObjValue").Value "11500"
#VARIABLE myRs("ObjLvl").Value "25"
#CALL @myRs.Update
}
#VAR myConnStr "Provider=MSDASQL.1;DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=eqdb;UID=root;PWD=password;OPTION=3"
#VAR mySQL "SELECT * FROM eqbase WHERE ObjLvl > 25"
#VAR myConn ""
#VAR myRs "" |
|
|
|
|
icedsun
Novice
Joined: 20 Jan 2006
Posts: 45
Location: Minnesota
|
Posted: Tue Jan 31, 2006 11:18 pm
|
Has anybody gotten this to work yet with Microsoft SQL? Or, more specifically, with MS SQL Express? What values do I need to change as I know little about connecting to it yet...
|
|
_________________
"Build a man a fire and he's warm for a night. Light a man on fire and he's warm for the rest of his life." --My Mom |
 |
|
|
icedsun
Novice
Joined: 20 Jan 2006
Posts: 45
Location: Minnesota
|
Posted: Thu Feb 02, 2006 3:13 am
|
I've been trying to get a basic connection to a Microsoft SQL database working to no avail. The connection string I've been using in the above-example is:
"Driver={SQL Server};Server=ICEDSUN\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\App_Data\ASPNETDB.MDF;Integrated Security=True"
But when I try myInit, zMud comes up with the error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work done."
I'm not sure how to check OLE DB status values or if i'm even trying to do this the correct way. Any help would be appreciated. |
|
_________________
"Build a man a fire and he's warm for a night. Light a man on fire and he's warm for the rest of his life." --My Mom |
|
|
|
Kuga
Beginner
Joined: 21 May 2005
Posts: 24
|
Posted: Tue Mar 14, 2006 11:33 pm
|
I attempted to convert the following with no avail. I suppose it is mostly due to me having issues with the help files in zmud.
| Code: |
Dim strConn As String = "File Name=F:\Zmud\test.udl"
Dim strSql As String = "exec sp_test"
Dim objConn = New OleDb.OleDbConnection(strConn)
Dim objComm = New OleDb.OleDbCommand(strSql, objConn) 'SqlCommand
objComm.CommandType = CommandType.StoredProcedure
objComm.commandtext = "sp_test"
objComm.Parameters.Add("@vcrText", "TEST")
objConn.open()
objComm.ExecuteNonQuery()
objComm.Dispose()
objConn.Close()
|
|
|
|
|
|
Guinn
Wizard
Joined: 03 Mar 2001
Posts: 1127
Location: London
|
|
|
|
|
Kuga
Beginner
Joined: 21 May 2005
Posts: 24
|
Posted: Wed Mar 15, 2006 8:57 pm
|
Thank you for the link, I was hoping I could get a stored procedure to work with little issue but this doesn't seem to be the case. |
|
|
|
|
Rainchild
Wizard
Joined: 10 Oct 2000
Posts: 1551
Location: Australia
|
Posted: Wed Mar 15, 2006 10:54 pm
|
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 |
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. |
|
|
|
|
Kuga
Beginner
Joined: 21 May 2005
Posts: 24
|
Posted: Thu Mar 16, 2006 1:09 pm
|
Thanks for the examples/code Rainchild, I hope to get some time to mess with this on Friday.
|
|
|
|
|
Kuga
Beginner
Joined: 21 May 2005
Posts: 24
|
Posted: Fri Mar 17, 2006 5:57 pm
|
I was messing around with sending variables with an insert or stored procedure, currently not working 100% where I want it. I am probably missing the syntax required for it.
Insert into [test] (vhrText) VALUES (@test) is the basic text string, @test being a variable with single 's around it when declared, so I tried this
Insert into [test] (vhrText) VALUES ("@test") which didn't work and led me to this option, after noticing the entire statement is within quotation marks, so if I broke them and then put in a variable, it made sense it would pick up the value.
Insert into [test] (vhrText) VALUES ("+@test+") Nope, didn't work. I continued thinking vbwise, which led me to this option as if I were in an actual VB environment. This once again did not work.
The example code given was this:
| Code: |
| #CALL @ADOConnection.Execute( "INSERT INTO my_table( my_id, my_column ) VALUES( 123, 'testing' )" ) |
modified where it works is:
| Code: |
| #show #CALL @ADOConnection.Execute( "Insert into [test] (vhrText) VALUES ('bluto')") |
Basically just asking if anyone had any success or knows the proper way of adding a variable to the line in zmud. |
|
|
|
|
Rainchild
Wizard
Joined: 10 Oct 2000
Posts: 1551
Location: Australia
|
Posted: Sun Mar 19, 2006 10:17 pm
|
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" ) |
|
|
|
|
|
Kuga
Beginner
Joined: 21 May 2005
Posts: 24
|
Posted: Sun Mar 19, 2006 11:36 pm
|
I did try something similar to your above idea, but I had issues since I did not set the execute to a string. Thus the @varName would work, but what I had the most success with was this. It also took up less lines.
| Code: |
#var sqlStr INSERT INTO [test] (text) VALUES ('
#var testVar TESTWORKED')
#exec #CALL @ADOConnection.Execute(%concat(@testStr, @testvar))
|
This worked fine and dandy, a friend helped me with it. Once again, thanks for the support and examples, it is greatly appreciated. |
|
|
|
|
|
|
|
