At a convention of relational databases, ODBC stands up. As if led by inspiration, he begins to speak in tongues
MySQL: I understand
Microsoft SQL Server: I do too
IBM DB2: I do three
PostgreSQL: How is it you all understand?
SQLite: It’s like he is speaking my mother tongue.
Microsoft Office Access: Then we must all have the same mother, because I understand him too. How is it we all understand?
CSVs: He must have trained to speak all our languages
Microsoft Office Excel: Magnificent
The inspiration for the plot above is from the story of the apostle’s speaking in tongues as found in the Bible in the Book Acts of the Apostles Chapter 2 verse 1 to 31. This is to demonstrate what ODBC can do, especially, when it comes to data stores. I am sure you are all very used to connecting to a database.
- However how comfortable are you connecting all languages or frameworks to MSSQL?
- How about connecting to Oracle?
- How about the other databases?
Usually every database management system works best with a certain kind of language. For example, MSSQL is used very commonly for .Net languages, whilst the bulk of PHP applications are used with MySQL. However PHP has no problem connecting, reading and executing on all of these different database as well.
There are a few approaches you could use in having this achieved. Personally, I prefer to use the ODBC approach. ODBC came about to help solve peculiar problems like this.
What is ODBC you say? ODBC stands for Open Database Connectivity. Instead of worrying about specific connections to all the different kinds of data stores, worry only about what you want to retrieve.
Notice that I did not mention databases but rather data stores. This is because ODBC speaks to more than just databases. It can also connect to files (such as comma delimited files and Excel spread sheets) to retrieve information.
All you need is the right setup to make this all happen. What do you need then?
- Install the driver
Depending on what you would like to do, or the language and Operating system you are using you would have to install the correct driver to aid in that work.
- Create the connection
This configuration has to be done on the computer where your web site is located. Thus, if your web site is located on a remote host, then you will need to have physical access to that machine.
- Useful functions
Since the following functions below might come up very often. You might as well be introduced to them right now;
- odbc_connect: connects to the ODBC data source.
- odbc_exec: executes the SQL statement passed to it.
- odbc_fetch_row: returns records from a result. It function returns true if it is able to return rows, otherwise false.
- odbc_result: reads fields from a record
- odbc_close: closes the connection
This ends the brief introduction to ODBC. I hope to follow this blog post with a future one illustrating a few examples.
Until then, take care, keep learning and cheers.