Site Tools


Accessing Databases from RhinoScript

Developer: RhinoScript
Summary: Demonstrates how to access databases from VBScript using RhinoScript.

Probably the most popular use for VBScript is connecting to databases. It's incredibly useful and surprisingly easy to do.

The first thing you need is the database, of course. A variety of programs can be used to create it, but probably the most popular is Microsoft Access. You can also use FoxPro or create it directly in an SQL Server using the utilities supplied with the server.

In this example, we connect to a simple Microsoft Access database. You can download the database used in this demonstration here:

http://en.wiki.mcneel.com/content/upload/files/test_access_mdb.zip

Most VBScript developers use Microsoft's ADO (ActiveX database objects) to get data from the database. ADODB is comprised of three main objects: Connection, RecordSet, and Command. We will demonstrate the first two objects.

Connecting to a database

The datasource is essentially a connection from the server or workstation to a database, which can either be on a dedicated machine running a SQL server or a database file sitting somewhere on the web server.

To specify what database you would like to use, you need to add a Data Source Name (DSN). DSN provides connectivity to a database through an ODBC driver. The DSN contains the database name, directory, database driver, UserID, password, and other information. Once you create a DSN for a particular database, you can use the DSN in an application to call information from the database.

There are essentially two types of datasources:

  1. System DSN - A datasource created on the web server by the server administrator. The most popular type of DSN. Generally the most reliable of the two.
  2. File DSN - A connection that your script makes each time access to the database is required, specifying the path to and name of the database. For this to work the database must reside on the server in a directory that your script can access.

The code below is designed around a System DSN named test that points to the above database. You can create System DSNs using the Data Sources (OBDC) applet found in Control Panel. In Windows, the shortcut to the ODBC control panel can be found in the following location: Start > Control Panels > Administrative Tools > Data Sources (ODBC).

Working with Recordsets

To read information from a Datasource, you need to open a Recordset, a set of database records based on some type of criteria, either all the records in a table or those matching some condition or set of conditions.

Example

The following example RhinoScript code demonstrates how to connect to a system DSN named test and read point coordinate records from a table named points.

 Sub Test
   Const adOpenStatic = 3
   Const adLockOptimistic = 3
   Const adUseClient = 3
 
   Dim objConnection, objRecordset
   Set objConnection = CreateObject("ADODB.Connection")
   Set objRecordset = CreateObject("ADODB.Recordset")
 
   objConnection.Open "DSN=test;"
   objRecordset.CursorLocation = adUseClient
   objRecordset.Open "SELECT * FROM points" , objConnection, adOpenStatic, adLockOptimistic
 
   objRecordSet.MoveFirst
 
   Dim x, y, z
   Do Until objRecordset.EOF
    x = objRecordset.Fields.Item("x")
    y = objRecordset.Fields.Item("y")
    z = objRecordset.Fields.Item("z")
    Rhino.AddPoint Array(x,y,z)
    objRecordset.MoveNext
   Loop
 
   objRecordset.Close
   objConnection.Close
 
 End Sub


developer/scriptsamples/database.txt ยท Last modified: 2015/11/11 by sandy