|
Fetching data using ODBC Now that we have listed the use of various methods to fetch data from another database using ODBC, let's explore some examples of how to actually go about doing this. We'll start with the simpler methods and then move on to code-based examples.
Let us get data from the Pubs database and Authors table into Excel. Pubs is a sample database which contains information about books, authors, booksellers, the actual sale and other related information.
Getting SQL Server data into Excel
Start Excel and create a new workbook. From the Data menu choose the Get External Data option. External data refers to data that is not primarily in Excel. Excel will get it and make a copy of the data. This can be manipulated but it will not update the base data. This is a read-only copy.
Choose Create New Query. From this dialog, choose the mytest datasource.
Now you need to connect to the SQL Server Pubs database. To do this, you have to specify your login name and password.
Now you will see a list of all tables that are available in the PUBS database. For this example, we will choose the Authors table.
Click on Next and specify that the data should be opened in Excel and not in MS Query.
And that's it! You get all the data from Authors table into Excel at a row-column position specified by you.
Once you have this data, you can format it, print it or manipulate it. Keep in mind that this data is a snapshot copy of the actual Authors table. If more records are added in the PUBS database, this data will not be refreshed automatically. If you want to get the latest version of the data, you can choose Refresh data option from the Data menu. Only, when you refresh data, the data is rewritten and therefore will overwrite your formatting and other changes.
Using MS Query
If you want to add or edit data quickly, you should use MS Query or a similar tool that allows you to manipulate the information retrieved. Here is how to use it:
Use Excel and follow the same steps till you reach the dialog box that asks you where to store the data. Choose to use MS Query and also save the query. Now MS Query will open with this data.
To view the data, just scroll through. If you want to edit the data, choose the Allow Editing option from the Records menu. Now you can edit existing values. If you change a value that is invalid as per the rules defined in the Pubs table, for example, if the AU_ID data is not in a particular format, your changes will not be saved to SQL Server.
If you want to add more rows, scroll to the end of the table till you see an empty row with an asterisk in the first column. You can enter valid new data here. When you move away from the record, the changes will be updated in the Authors table.
Using ODBC, you can fetch data from any type of database as long as the database drivers are properly configured and a data source is configured.
Accessing data programmatically
Data from a remote DBMS (DataBase Management System) is accessed using SQL commands.
The data is returned in the form of a collection of rows and columns called a recordset. Let us see how we can access the same table in VB.
Visual Basic Code:
We will use RDO (Remote Data Objects) for accessing data. RDO internally uses ODBC API but provides a simpler object model for programmers. API programming is much more cryptic and difficult than programming the object model. You need to choose Remote Data Object from the Project - References dialog before you can use RDO.
The RDO object has the following hierarchy:
rdoEngine: rdoEngine is a system object that functions as the gateway to remote data.
rdoEnvironments: rdoEnvironments objects are used to control transactions. All the data manipulated in one rdoEnvironment can be committed or rolled back with one command-providing that the back-end database supports transactions, including transactions across multiple servers, if that is what you are doing.
This contains the connections object collection. Each environment can hold a set of connections to different ODBC data sources, or it can hold a set of connections all to the same data source.
The one thing common to all rdoConnections in one rdoEnvironment is that they share the same user name and password.
rdoConnections: An rdoConnection is a connection to one ODBC data source that uses the user name and password of the rdoEnvironment in which the rdoConnection resides. You can set properties on an rdoConnection to specify what kind of cursor it uses (server-side, client-side, or none) and also the Login and Query time-outs. The cursor type specifies where rows of data should be buffered to allow the user to scroll forward and backward through the resultset.
A server-side cursor puts more of the burden on the server and the network. Only the data the client requires at any one time is transferred across the network; generally, this means just enough data to present on one screen. Using a server-side cursor usually will reduce network traffic, but it can also increase network traffic under certain circumstances, such as when data is passed from the server to the client many times as the user scrolls up and down the resultset.
Using a client-side cursor means that the complete resultset is transferred to the client at one time and then is held in memory or on disk. A client-side cursor provides a more predictable load on the whole system, but it can slow everything down as large amounts of data are transferred across the network to be stored temporarily on the client PC. This time is wasted if all the user then does is pick the first record.
Using no cursor is a very quick method of fetching rows, but it means the client program can read the resultset only once and can't move backward through the data. It's therefore a useful method when populating static list boxes and combo boxes and when generating reports. It's also useful in three-tier applications when the middle ier makes a quick connection to the back-end data server to retrieve information in one large chunk, which it then passes on to the front-end presentation application in smaller chunks.
rdoQueries: This object is used to create queries with or without parameters, as also for calling stored procedures. Stored procedures can return values as well as recordsets.
rdoQuery objects have events that fire at various times. The WillExecute event is fired before the query is executed, which permits you to make last-minute changes to the SQL or to prevent the query from executing. The QueryComplete event fires when a query has finished executing. The QueryTimeout event fires when the QueryTimeout period (a property of the query) has elapsed and the query hasn't yet begun to return rows.
rdoResultsets: rdoResultsets are rows of data contained in columns. They are like DAO recordsets, but with some significant differences. rdoResultsets can contain more than one set of records with different columns in each set. Updates to the resultset can be batched together and sent back to the server in one operation.
rdoResultsets can be opened asynchronously, which allows the client application to get on with something else while the server is busy. There are several events that fire when data is ready or when a result-set is associated with (or dissociated from) an rdoConnection.
Dim en As rdoEnvironment
Dim cn As rdoConnection
Dim rs As rdoRecordset
Set en = rdoEngine.rdoEnvironments(0)
Set cn = en.OpenConnection( _
dsname:="mytest", _
Prompt:=rdDriverNoPrompt, _
Connect:="UID=sa;PWD=;")
sSQL = "Select * from pubs..authors"
Set rs = cn.OpenResultset(sSQL, _
rdOpenForwardOnly, rdConcurReadOnly)
To view the data you have to specify the column:
Debug.print rs.rdoColumns(1).value
Visual FoxPro Code:
m.handle = sqlconnect("mytest", 'sa', '')
&& dsnname, userid, password
m.retval = sqlexecute(m.handle, ;
"select * from pubs..authors")
* The resulting records are available in a local FoxPro
* cursor called sqlresult
browse && Display the data in a browse window
|
