![\"ODBC](\"https:\/\/i0.wp.com\/thepizzy.net\/blog\/wp-content\/uploads\/2011\/02\/8787717937219497341-150x150.png?resize=150%2C150\")
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<\/strong>. I had to find that out the hardway, via Microsoft’s convoluted documentation on using ODBC<\/a>.<\/p>\nHere’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):<\/p>\n
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.<\/p>\n 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.<\/p>\n When running odbc_connect()<\/a><\/strong> it takes 3 parameters:<\/p>\n When connecting using Windows Authentication & a DSN (as this example is about), there are some caveats and things to remember:<\/p>\n This creates an active link via ODBC to the SQL Server…supposedly<\/p>\n 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…<\/p>\n Now that we have a valid link to the database, we’ll send a request for data<\/strong> using the odbc_exec()<\/a><\/strong> function. This function sends the connection resource ($link<\/strong>) and the SQL Query we want run ($sql<\/strong>). It will return a “ODBC result identifier” or false<\/strong><\/em>.<\/p>\n Since a result identifier could, I assume, be the number 0 (zero) I want to ensure that $data<\/strong> is actually false<\/em><\/strong> 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<\/em> = false<\/em><\/strong> and anything else<\/em> = true<\/em><\/strong>. When you use === you test for an actual boolean value, meaning anything including zero<\/em> = true<\/em><\/strong> and false = false<\/em><\/strong>.<\/p>\n If the query failed, and resulted in a false<\/em><\/strong> 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…<\/p>\n 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.<\/p>\n Then we’ll run odbc_num_fields()<\/a><\/strong> over the $data<\/strong> 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.<\/p>\n Iterate through each column name and add it to an array, called $fields<\/strong>:<\/p>\n Then we run odbc_num_rows()<\/a><\/strong> 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.<\/p>\n Iterate through each record row and insert it to a temporary array $row<\/strong> using odbc_fetch_into()<\/a><\/strong>. Then take $row<\/strong> and put it into an array of records, $records<\/strong> giving you something like this:<\/p>\n 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<\/p>\n or we can list every column as an array of record id’s as keys and values.<\/p>\n 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.<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/thepizzy.net\/blog\/wp-content\/uploads\/2011\/02\/2011-02-06_13131.png?resize=580%2C724\" "\\"function")
<\/a><\/p>\nCode Breakdown<\/h1>\n
Function call<\/h2>\n
function odbcQuery($sql, $attempt=\"\") {<\/pre>\nDatabase Connection<\/h2>\n
\/\/ Establish an odbc connection with the database\r\n$link = odbc_connect(\"My_DSN_Name\", \"\", \"\");<\/pre>\n
\n
\n
\n
Database Connection Checking & Error Handling<\/h2>\n
if (!$link) {\r\n\tdie('Could not connect: '.odbc_error().': '.odbc_errormsg());\r\n} else {<\/pre>\nSending the SQL Query & Checking Response<\/h2>\n
$data = odbc_exec($link, $sql);\r\nif($data === false) {\r\n\techo \"ODBC Query: \".$sql.\"\r\n\r\n\";\r\n\tdie(\"ODBC Query failed: \".$attempt.\"\r\nError: \".odbc_error());\r\n} else {<\/pre>\nParsing the Query Results – Column Headers<\/h2>\n
\/\/ Initialization\r\n$row = $fields = $records = $result = array(); \t\r\n\r\n\/\/ Get the result's column names\r\n$count = odbc_num_fields($data);\r\nfor($x=1;$x<=$count;$x++) {\r\n\t$fields[] = odbc_field_name($data, $x);\r\n}<\/pre>\nArray (\r\n\t[0] => field_name_1\r\n\t[1] => field_name_2\r\n\t[2] => field_name_3\r\n)<\/pre>\n
Parsing the Query Results – Records<\/h2>\n
\/\/ Get the result's data: array[record#][column#] = value\r\n$count = odbc_num_rows($data);\r\nfor($x=0;$x<$count;$x++) {\r\n\todbc_fetch_into($data, $row, $x);\r\n\tarray_push($records,$row);\r\n}<\/pre>\nArray (\r\n\t[0] =>; Array (\r\n\t\t[0] =>; record_1_column_1\r\n\t\t[1] =>; record_1_column_2\r\n\t\t[2] =>; record_1_column_3\r\n\t)\r\n\t[1] =>; Array (\r\n\t\t[0] =>; record_2_column_1\r\n \t\t[1] =>; record_2_column_2\r\n \t\t[2] =>; record_2_column_3\r\n \t)\r\n\t[2] =>; Array (\r\n \t\t[0] =>; record_3_column_1\r\n \t\t[1] =>; record_3_column_2\r\n \t\t[2] =>; record_3_column_3\r\n \t)\r\n )<\/pre>\n
Making the data useable<\/h2>\n
\/\/ Return data in the format: array[record_id][column_name] = value\r\nforeach($records as $rid =>; $record) {\r\n\tforeach($fields as $key =>; $name) {\r\n\t\t$result[$rid][$name] = $record[$key];\r\n\t}\r\n}<\/pre>\n\/\/ Return the data in the format: array[column_name][record_id] = value\r\nforeach($fields as $key =>; $name) {\r\n\tforeach($records as $record) {\r\n\t\t$result[$name][] = $record[$key];\r\n\t}\r\n}<\/pre>\n