For about the last year, I’ve been creating a CMS (content management system) named Jada, for the automatic management and maintenance of my company’s eCommerce site, on the Yahoo! Store platform. The software imports the entire store automatically, runs a series of cleanup processes with about a dozen different criteria, saves the changes it has made, exports the modified pages, and makes them available to download from Jada’s interface. This automation cuts the need for about 3-4 people doing a weeks’ worth of work, and does it all automatically in 10-30 minutes. The one thing it doesn’t do is the one cleanup process that takes the longest, and requires the most human thought: comparing every product’s available options on the site, and checking them against actual inventory in the order management software.
Until now.
Our current order management software runs as a MS Access front-end to a MSSQL 2005 server backend through an ODBC DSN connection. This connection has been limited to MS Access and MS Excel application/queries, and thus, was the limiting factor to writing this most-complex cleanup process into Jada. The most difficult part in my development was finding an understandable article describing how to make an ODBC call, and then actually get the data back, in the same simple manner that one makes a MySQL query. The real issue has been once the connection is closed, the result cannot be accessed. I had to find that out the hardway, via Microsoft’s convoluted documentation on using ODBC.
Here’s the code I’ve used to make the ODBC connection in PHP (unfortunately my blog’s template can’t handle actual code right now):
Code Breakdown
We’ll start creating a function that makes an ODBC connection, passes it an SQL query, then parses the data into a table/array and return the array.
Function call
function odbcQuery($sql, $attempt="") {
When we call the function, we’ll provide the SQL Query we want executed, and an optional description of what we’re trying to do with the query. In this way, if it errors, an semantic error will be displayed along with the technical one to help locate the code easily.
Database Connection
// Establish an odbc connection with the database
$link = odbc_connect("My_DSN_Name", "", "");
When running odbc_connect() it takes 3 parameters:
- the DSN; Server,Port; or Server/SQLInstance
- username
- password
When connecting using Windows Authentication & a DSN (as this example is about), there are some caveats and things to remember:
- On the web server, the User running the Web Service process needs to be a User with permission to access the SQL Server.
- In my case, the user running the web server is SYSTEM, and so the user trying to access the SQL server is “DOMAIN\COMPUTERNAME$“.
- There is no password for a SYSTEM account, and so on the SQL Server needs to have a user created named “DOMAIN\COMPUTERNAME$“.
- Due to some security concerns, I’ve decided to give the account read-only access to the database I want to access. You’ll need to consult your own IT Administrator or security advisor for your security concerns.
- In the odbc_connect() statement, you then only need the name of the DSN (which I assume has already been configured on the Web Server you’re using) , followed by two null-quotes: “”.
This creates an active link via ODBC to the SQL Server…supposedly
Database Connection Checking & Error Handling
if (!$link) {
die('Could not connect: '.odbc_error().': '.odbc_errormsg());
} else {
Next, we check the link . If it just flat-out doesn’t exist, then we kill the program, throw an error message that will read: “Could not connect: <;insert odbc error code>;: <;insert odbc error text>;”. Otherwise, we move on…
Sending the SQL Query & Checking Response
$data = odbc_exec($link, $sql);
if($data === false) {
echo "ODBC Query: ".$sql."
";
die("ODBC Query failed: ".$attempt."
Error: ".odbc_error());
} else {
Now that we have a valid link to the database, we’ll send a request for data using the odbc_exec() function. This function sends the connection resource ($link) and the SQL Query we want run ($sql). It will return a “ODBC result identifier” or false.
Since a result identifier could, I assume, be the number 0 (zero) I want to ensure that $data is actually false and not just zero. That’s where the triple === comes in. When doing conditional statements, using == will convert the data being compared into a true/false value, where zero or nothing = false and anything else = true. When you use === you test for an actual boolean value, meaning anything including zero = true and false = false.
If the query failed, and resulted in a false result, we’ll display an error message: “ODBC Query: <;insert actual SQL Query>; // ODBC Query failed: <;insert query description>; // Error: <;insert ODBC error code>;”. Otherwise, we’ll move on…
Parsing the Query Results – Column Headers
// Initialization
$row = $fields = $records = $result = array();
// Get the result's column names
$count = odbc_num_fields($data);
for($x=1;$x<=$count;$x++) {
$fields[] = odbc_field_name($data, $x);
}
We start off by initializing all the variables we’re going to use in the next bit of code, to make sure they’re empty.
Then we’ll run odbc_num_fields() over the $data to get the number of columns we need to iterate through. For columns, the counting starts at 1, so the for-loop starts at 1.
Iterate through each column name and add it to an array, called $fields:
Array ( [0] => field_name_1 [1] => field_name_2 [2] => field_name_3 )
Parsing the Query Results – Records
// Get the result's data: array[record#][column#] = value
$count = odbc_num_rows($data);
for($x=0;$x<$count;$x++) {
odbc_fetch_into($data, $row, $x);
array_push($records,$row);
}
Then we run odbc_num_rows() over the $data to get the number of rows we need to iterate through. For rows, the counting starts at 0, so the for-loop starts at 0.
Iterate through each record row and insert it to a temporary array $row using odbc_fetch_into(). Then take $row and put it into an array of records, $records giving you something like this:
Array ( [0] =>; Array ( [0] =>; record_1_column_1 [1] =>; record_1_column_2 [2] =>; record_1_column_3 ) [1] =>; Array ( [0] =>; record_2_column_1 [1] =>; record_2_column_2 [2] =>; record_2_column_3 ) [2] =>; Array ( [0] =>; record_3_column_1 [1] =>; record_3_column_2 [2] =>; record_3_column_3 ) )
Making the data useable
Now that we’ve got two tables/arrays of data – the field/column names, and each record’s array of data – it’s time to make it usable in a format that we can consistently expect to be returned. There are two ways to do this. We can create an array listind every record as an array with column_name keys and values
// Return data in the format: array[record_id][column_name] = value
foreach($records as $rid =>; $record) {
foreach($fields as $key =>; $name) {
$result[$rid][$name] = $record[$key];
}
}
or we can list every column as an array of record id’s as keys and values.
// Return the data in the format: array[column_name][record_id] = value
foreach($fields as $key =>; $name) {
foreach($records as $record) {
$result[$name][] = $record[$key];
}
}
Personally I find the first option to be more consistent with my results when calling a 2-dimensional result from MySQL queries, so it is the one I have gone with in my example at the start of the post, and in this description.
The foreach() statements describe a compilation of a $result array in this manner:
- For each item in the $records array, store the record_id as $rid, and the record array as $record.
- Then for each item in the $fields array, store its cardinality as $key and it’s value/name as $name.
- Then compile an array, iterating through each of the fields, storing this $record‘s associated cardinality $key‘s value into the $result array’s storage for this record’s id ($rid) under the appropriate field name.
It’s a lot easier to grasp than it sounds. Basically, take array from the Query Results – Records section, and replace the # with the column name in each: [#] =>; record_y_column_x, but store it as a different array, called $results. The resulting array would look something like this:
Array ( [0] =>; Array ( [column1] =>; record_1_column_1_data [column2] =>; record_1_column_2_data [column3] =>; record_1_column_3_data ) [1] =>; Array ( [column1] =>; record_2_column_1_data [column2] =>; record_2_column_2_data [column3] =>; record_2_column_3_data ) [2] =>; Array ( [column1] =>; record_3_column_1_data [column2] =>; record_3_column_2_data [column3] =>; record_3_column_3_data ) )
Close the connection, Return the result
odbc_close($link); return $result; } }
Now that we’ve stored the data we need from the volatile $data variable returned from the SQL Query into $result, we can close the connection to $link using odbc_close(), and then return $result for the program to do with it what it will.
Conclusion
This is just an example code that explains one way of many to extract a variable 1-2 dimension array of data from your SQL Query, using a Windows-Authenticated ODBC DSN connection. There are many other methods to do this, as well as security fixes, data scrubbing, and other modifications that one would probably want to do.
This is the first function I’ve written in any language to access an SQL Server via ODBC. This is also a function that has worked in tests, but that I have not yet put into production. I encourage you to take this bit of explanation and massage it into something that fits your needs in the code that you’re writing, and don’t rely on what I’ve got here as a written-in-stone example of good production-level code. This sample will change many times before I actually implement it.
Happy Hacking!
