SQL errors with ODBC access link.

Hi Guy's

I'm having trouble linking to ODBC / Access via PHP

I'm using PHP 5.0.4, Apache 2.0.49, & WindowsXP

I have the same script working on 3 other access tables no problems.
But on this particular DSN / Table I get ODBC/SQL errors.

Here is a section of the PHP code in question;

$ODBCdsn = "swNotes";
$theTable = "Desc";
$cx=odbc_connect($ODBCdsn,"","","");
$ODBCquery = "UPDATE $theTable SET UniqueId = '$link_unq' WHERE Company_ID = '$link_company';";
$ODBCPrep = odbc_prepare($cx,$ODBCquery);

I get the following error message.

Warning: odbc_prepare() [function.odbc-prepare]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement., SQL state 37000 in SQLPrepare in C:\datamanager\schwartz_add_notes.php on line 92

The SQL string looks like this.
UPDATE Desc SET UniqueId = 'JvC3faiEeKmc' WHERE Company_ID = '106005';

Any Idea's ?

Matt
Matthew_WayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I assume Desc Is a reserved word, thus the table name needs to be enclosed:

$ODBCquery = "UPDATE [$theTable] SET UniqueId = '$link_unq' WHERE Company_ID = '$link_company';";

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Matthew_WayAuthor Commented:
That seemed to work on my laptop (winXp).

But when I try and run the same script on the production server I get the following;

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in C:\datamanager\schwartz_add_notes.php on line 91

( I changed the prepare to an exec as well )
Strange both machines are PHP 5.0.4 / apache 2
The server OS is Windows 2003 server.

Matt
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Could it be that the $variables are not properly replaced...

what about these two suggestions:
$ODBCquery = "UPDATE \[$theTable\] SET UniqueId = '$link_unq' WHERE Company_ID = '$link_company';";
$ODBCquery = "UPDATE [{$theTable}] SET UniqueId = '{$link_unq}' WHERE Company_ID = '{$link_company}';";

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Matthew_WayAuthor Commented:
Still the same problem.

The script runs okay on one machine but not the other.
I even tried this;

 $ODBCquery = 'UPDATE ['.$theTable.'] SET UniqueId = \''.$link_unq.'\' WHERE Company_ID = \''.$link_company.'\';';
 $cur=odbc_exec($cx, $ODBCquery);

I got the script to output the SQL to the browser, here is the output;
UPDATE [Desc] SET UniqueId = 'b6E1faBHeKmc' WHERE Company_ID = '115027';
UPDATE [Desc] SET UniqueId = 'c_Xnfa3LeKmc' WHERE Company_ID = '113216';
UPDATE [Desc] SET UniqueId = 'c5sqgaNF)Kmc' WHERE Company_ID = '108301';
UPDATE [Desc] SET UniqueId = 'mfpnfa3LeKmc' WHERE Company_ID = '113414';

Here is the error message again;
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in C:\datamanager\schwartz_add_notes.php on line 90

akshah123Commented:
Sounds like the extension fiiles on your other machine are corrupted, try reinstalling the php server.
Matthew_WayAuthor Commented:
Okay,

I deleted all the files in c:\php and extracted the files from the binary I downloaded.
I used the same php.ini file as my development machine.

I still get the same error;
Here is the error message again;
Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in C:\datamanager\schwartz_add_notes.php on line 90


Any further Idea's ??
Matthew_WayAuthor Commented:
I tried a few more things.

like installing the new jet drivers.

But I think the problem is the database. because when I copy a fresh version of the access database the error goes away.

Thanks for you help.

Regards

Matt
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.