Using Macro Scheduler’s Database Functions
Macro Scheduler includes four functions for connecting to databases, querying and modifying data:
- DBConnect
- DBQuery
- DBExec
- DBClose
Connecting to a Database
Before you can connect to a database you'll need to make sure you have the required OLE DB/ODBC drivers installed. You can see what drivers are already installed under Control Panel > Administrative Tools > Data Sources (ODBC). If you have Microsoft Office installed you'll already have the standard Microsoft ones for Microsoft Access, Excel, dBase, Paradox etc. You may also already have the driver for Microsoft SQL Server installed.
If you want to connect to a third party database such as Oracle, Sybase, MySQL etc, then you may need to install the required drivers. Although if your computer is running other software which already accesses these databases, you probably already have the drivers installed. If not, visit your provider's web site to find the required drivers, or dig out those install disks, or contact your system/database administrator!
DBConnect is used to create a connection to a database. It requires an ADO, OLE DB, or ODBC Connection String, and returns a handle to the database, which is used in the other database functions.
What is a Connection String?
A Connection String is just a string containing database connection information, telling Macro Scheduler how to connect to the database in question. The string contains a number of arguments and values separated by semicolons:
argument1=value1; argument2=value2;
What these arguments and values should be depends on the database you are using and the method of connection. More on that in a moment.
It is possible to create DSN (Data Source Name) connections in your Control Panel. All this does really is help you build a connection string and store it in your registry. Then the Connection String in DBConnect just has to be the DSN name you defined in Control Panel. While this method makes it easier to create the connection, it is obviously less portable. If you want to use this method to create a system DSN go to Control Panel > Administrative Tools > Data Sources (ODBC). You can then choose from the installed providers and the dialogs will ask for the connection information needed.
How do I Construct a Connection String?
The correct answer is to read the documentation for the database you want to connect to! But as people have been using connection strings to connect to databases since I still had a full head of hair, the web abounds with useful information. Google is your friend. Look what comes up top with this Google Search:
Position 1: http://www.connectionstrings.com
Why, some web site called nothing other than connectionstrings.com, created by some kind soul who clearly read your mind.
Click on the database type you want to connect to and you'll be shown everything you need. For example, if you want to connect to Microsoft SQL Server you'd probably need:
Driver={SQL Server}; Server=myServerAddress; Database=myDataBase; Uid=myUsername; Pwd=myPassword;
You'll note there are other options depending on what kind of security is required and how the server is configured and so on. In most cases the basic string is probably all you need, but if in doubt, or if it fails to work, contact your database administrator. Yes, contact your database administrator, not me. Seriously. Whoever set your database up will have more of a clue than I.
So, anyway, let's put the above into a DBConnect call in Macro Scheduler. We'd do something like:
Let>connstr=Driver={SQL Server}; Server=myServerAddress; Database=myDataBase; Uid=myUsername; Pwd=myPassword;
DBConnect>connstr,dbH
Obviously, you'll need to repace myServerAddress with the name or address of the server, myDataBase with the name of the database, myUsername with a valid username with permissions to do whatever you plan to do with database, and myPassword with your password.
Want to connect to a different type of database? First, make sure you have the right ODBC/OLE DB drivers installed. Second, read the documentation and if possible speak to your database administrator (if that's not you!) and if still unsure try Googling "Connection Strings".
Security Issues
Your administrator may have locked down the port that the ODBC driver connects through. Make sure your IP address can connect through that port. Some database servers require the database user to have special privileges to be able to connect remotely. Again, these are all things for your system administrator to help you with. You don't have a system administrator? You're the one who sets it all up? Well, I guess you'll have to read the docs again then. Sorry :-)
Retrieving Data
To retrieve data from the database we use the DBQuery command. This accepts a valid SQL statement which returns a recordset, e.g. a SELECT statement. Now, I am not going to try to teach SQL here. There are heaps of resources out there that do that already. A quick Google search reveals this tutorial. Also, most databases provide utilities which help you build queries graphically and chuck out the SQL for you.
The most basic SELECT statement is: "SELECT * FROM TABLENAME", e.g.:
SELECT * FROM CUSTOMERS
Which just says "select all records from the CUSTOMERS table".
As well as a SQL statement DBQuery needs a database reference returned by our previous DBConnect call, so that it knows which database you want to perform the SELECT on. We also give DBQuery an array variable to store the returned data in, a variable to store the number of records returned and a variable to store the number of fields per record. So:
Let>SQL=SELECT * FROM CUSTOMERS DBQuery>dbH,SQL,rsCustomers,numRecs,numFields
Here, rsCustomers is the array in which the data should be stored. The array takes the format:
rsCustomers_RECNUM_FIELDNUM
So, let's say the above DBQuery returns a recordset containing two records, each with three fields, we'd end up with:
rsCustomers_1_1 rsCustomers_1_2 rsCustomers_1_3 rsCustomers_2_1 rsCustomers_2_2 rsCustomers_2_3
numRecs tells us the number of records returned and numFields tells us how many fields there are.
We could loop through every field with:
Let>r=0
Repeat>r
Let>r=r+1
Let>f=0
Repeat>f
Let>f=f+1
Let>this_field=rsCustomers_%r%_%f%
Message>this_field
Until>f=numFields
Until>r=numRecs
Modifying Data
To perform any SQL statement that does not return data use the DBExec command. E.g. DBExec can be used for a DELETE, INSERT or UPDATE query. DBExec again takes a database reference returned by DBConnect, the SQL statement, and returns the number of rows affected:
Let>SQL=DELETE FROM CUSTOMERS WHERE CUSTID=1532 DBExec>dbH,SQL,rowsAffected
In this example rowsAffected will contain the number of rows that were deleted.
If it doesn't work it could be that your lovely database administrator may not have given you DELETE privileges. He's probably worried you're going to try something like this:
Let>SQL=DELETE * FROM CUSTOMERS DBExec>dbH,SQL,rowsAffected
Closing the Database
Just as you shouldn't leave doors open after you, you really ought to close any connection to the database also. Do this with DBClose which just wants the database reference returned by DBConnect:
DBClose>dbH
And that's pretty much it.