Geek Answers Handbook

Input exception in SQL queries when using ODBC + Access

I tried odbc_prepare() + odbc_execute() to update the entry in the access file, but I always get the SQL state 07001 error message about incorrect column counting (in fact, the message is in Spanglish and doesn't make much sense):

 <?php $items = array(); $items[100] = 'Foo'; $items[200] = 'Bar'; $sql = 'UPDATE street SET name=? WHERE street_id=?'; $stmt = odbc_prepare($conection, $sql); if( $stmt===FALSE ){ die(odbc_errormsg()); } foreach($items as $cod => $name){ if( !odbc_execute($stmt, array($name, $cod)) ){ die(odbc_errormsg()); } }

User comments on the odbc_execute page indicate that Microsoft Access ODBC drivers do not support parameterized queries. However, I did not find the odbc_ * function to delete data.

So ... How can I avoid data entry?

+2
source share
2 answers

I tried random things. odbc_prepare() seems to detect parameters if you use one of these syntaxes (or you even mix them):

  • INSERT INTO foo (bar) VALUES (:param)
  • INSERT INTO foo (bar) VALUES ([param])

However, odbc_execute() will complain about the lack of parameters no matter what you feed it with (numerical array, associative array ...). And he will know the exact number of parameters that cannot be found. This makes the whole mechanism completely pointless.

Let's say my best solution so far is this:

 /** * Escape a string to be inserted into Access via ODBC */ function odbc_escape_string_access($value){ $replacements= array( "'" => "''", ); return strtr($value, $replacements); }

This is terrible, but I could not find anything better.

+1
source

Usually in MS Access you define parameters by placing them in brackets

 $sql = 'UPDATE street SET name=[myname] WHERE street_id=[mystreet]';

How it will be with a grid with php, I do not know.

+1
source

More articles:


All Articles