Before we get into the details of this step, let's discuss what a Data Source is. A Data Source is pretty much analagous to a database file since a Data Source contains one database file. But a Data Source also has some settings associated with it. Mostly, these settings concern whether the database file is located on a server or a local drive, and if it's on a server, what protocol is used to connect and transfer data, what database engine (ie, ODBC driver) is used to access this database file, etc.
An enduser creates a Data Source on his computer using the "Data Sources (ODBC)" utility in Control Panel. When he creates this Data Source, he gives it any name of his choosing. We'll call this the Data Source Name or DSN. He also enters information about what ODBC driver to use, where the database file is located (ie, what server, and what protocol is used to transmit data over the network), what the database file's name is, etc. All the information needed for you to communicate with the database is entered when a Data Source is created. The user needs to enter this information only once, and then it is permanently stored in the Windows registry under the Data Source Name that the user chose.
Note: Your script can also create a Data Source by calling the ODBC function SQLConfigDataSource(). This function is passed arguments that supply the same information that the user would enter, as well as a string that can contain driver-specific keywords to specify any additional information required by a particular driver. Your script will not normally create new Data Sources, but rather, use the ones that have already been setup on a computer.
To establish a connection to a particular Data Source, you need call only one ODBC function. You pass the name of the Data Source (ie, the name that the user gave it). The ODBC manager will retrieve all the information about what server to connect to, using what protocol, what database software (ie, driver) to use, and what the name of the actual database file is. And the ODBC Manager will actually establish the connection.
In other words, this is not like how you would directly manipulate some database file. If you wanted to directly manipulate some database file on your local drive, you'd have to open it and read data from it (perhaps using CHARIN), and you would probably have to know what database software created the file because different database engines use different file formats. And if the file was on some server, then you'd have to write your own functions to connect to the server and read data over a network.
Not so with RxOdbc. You don't open a particular database file. Rather, you "open" (ie, connect to) a Data Source. If you want to read data from the database, you send the ODBC Manager a command (by calling a function in RxOdbc) to tell it to return some data, and then you call another function in RxOdbc to retrieve that data. If you want to create new data (such as create a new table) or modify existing data, you send the ODBC Manager a command to tell it what operation you want performed, and perhaps call another function to supply your new data. The ODBC Manager works with the Data Source you have chosen (and its driver) to do all of the work on the actual database file. So you never directly touch the actual database file. And the database commands you code, as well as the format of the data, is always the same for every Data Source. It is up to the Data Source's driver (ie, database engine) to translate that data, if need be, to accomodate its own proprietary file format.
If your script happens to know the name of the desired Data Source, then you can simply pass it to the ODBC function to connect. But typically, your script won't know the names of the Data Sources on a given computer. There is an ODBC function named OdbcGetDSNList which can retrieve the names of all the Data Sources on a computer. You pass it the name of the stem variable where you want the list of Data Source Names stored.
OdbcGetDSNList will append a .0 to your stem name and store the count of how many Data Sources are on the computer. It will then store those names in your stem, where a .1 is appended to your stem name to store the first data source name, .2 is appended to your stem name to store the second data source name, etc.
If you'd also like the description for each Data Source, then you can pass a second arg to OdbcGetDSNList -- the name of the stem variable where you would like the descriptions stored. The descriptions are stored similiarly to the way the Data Source Names are stored. The description string tells what database software has been used for a given Data Source (for example, the description string may be "Microsoft Access Driver (.mdb)" to indicate that Microsoft Access is being used to manage that database, and the database file's name ends in .mdb).
OdbcGetDSNList returns an empty string if successful, or an error message. (Alternately, it may raise some condition if you've set OdbcErr to a condition name).
Here is an example of getting the data source names and descriptions, and displaying them. Note: We assume that ODBC functions are raising ERROR when they fail.
DO /* Retrieve data source names in the variable DsnNames. and * the descriptions in the variable DsnDescriptions. */ OdbcGetDSNList("DsnNames.", "DsnDescriptions.") /* Were there any Data Sources? This is not considered an error if there aren't */ IF DsnNames.0 == 0 THEN SAY "No data sources on this computer" /* Display them */ ELSE DO i = 1 TO DsnNames.0 DsnNames.i '(' || DsnDescriptions.i || ')' END CATCH ERROR /* Display an error message box, and end the script. */ CONDITION("M") ENDTypically, you'll display the names in a listbox, and let the user select which one he desires. Once you have the desired Data Source Name, then you're ready to connect to it.