A common task for scripters is working with databases. Typically VBScript coders use ADO to access database tables. However, creating code to access, modify or otherwise interact with databases can be arcane and mystifying. The Professional and Enterprise editions of PrimalScript 2007 include an ADO Wizard that generates VBScript code.
Using a database connection from the Database Browser, right-click the table you want to work with and select Generate VBScript Code.
The Wizard automatically generates the connection string. You can adjust the settings to control what type of code is generated.
Here’s my wizard generated code that only took me a few mouse clicks:
On Error Resume Next
‘Database connection
Dim objCn
Set objCn = CreateObject(“ADODB.Connection”)
objCn.Open “Provider=SQLOLEDB.1;Integrated Security=SSPI;” &_
”Persist Security Info=False;Initial Catalog=JDHDB;” &_
“Data Source=godot\sqlexpress”
‘Query data
Const ADO_Dynamic = 2
Const ADO_FwdOnly = 0
Const ADO_Keyset = 1
Const ADO_Static = 3
Dim rs_Computers, strSQL
strSQL = “Select * from Computers”
Set rs_Computers = CreateObject(“ADODB.RecordSet”)
rs_Computers.Open strSQL, objCn, ADO_FwdOnly
‘Go through data
Do Until rs_Computers.EOF
WScript.Echo rs_Computers(“Name”)
WScript.Echo rs_Computers(“AssetTag”)
WScript.Echo rs_Computers(“Updated”)
rs_Computers.MoveNext
Loop
‘Close database connections
rs_Computers.Close
objCn.Close
On Error GoTo 0
I can insert this into my open VBScript, and I’m done! Now you can create database scripts in fraction of the time. The wizard will also let you create custom class objects to make it easier to update or insert new data. It will even generate some sample code to give you a guideline on how to use the classes.
You can try out the ADO wizard and other new features by downloading a 45 day evaluation copy of PrimalScript 2007.
this is very cool. but what if you create a bunch of these scripts and then the database information changes (e.g. valid user/password credentials). you have to go in and modify all of the scripts where you copy/pasted the primal script code. right? is there a way to store the connection string in a central place.
That’s really cool, but when will this type of functionality be available for PowerShell?
If the database SCHEMA changes, you’d need to overhaul your script, of course. For a little username/password change, it’s just one line you’d normally change. And yes, you could use an external connection file instead of a connection string, that’s an option too.
Our dev team thought about making this available for PowerShell but the code is pretty significantly different, so there just wasn’t time. I’m pretty sure it’s on the list, but why take a chance? Ask for it in the Wish List forums at http://www.sapien.com/forum2. That way they’ll know you want it. However, PowerShell won’t be able to duplicate everything VBScript can. While PrimalScript can generate the simpler boilerplate database code in PowerShell, PowerShell doesn’t support the creation of classes in script code, so PrimalScript can’t create those PrimalSense-friendly database classes that it can for VBScript.