Bug in Jet/Sql?

Question

I'm using Jet in PHP. Querying a text field is easy, but my primary key is an integer, and I can't get the correct string. I've tried the fallowing:

SELECT * FROM Table1 WHERE 'FullName' = 'ABNER AVERY' Failed!

SELECT * FROM Table1 WHERE FullName = 'ABNER AVERY' Success, correct display:

(ÀoRows returned: -1
Key = INDI_ID Value = 64
Key = Given Value = ABNER
Key = Surname Value = AVERY
Key = Birth Value = 28 MAY 1712
Key = BirthPlace Value = Groton, New London, Connecticut, USA
Key = Death Value = 13 AUG 1771
Key = DeathPlace Value = Montville, New London, Connecticut, USA
Key = Mother Value = ELIZABETH BILL
Key = Father Value = JONATHAN AVERY
Key = Spouse1 Value = AMY FOX
Key = Marriage1 Value = 22 MAY 1740

SELECT * FROM Table1 WHERE INDI_ID =64 / "SELECT * FROM Table1 WHERE \"INDI_ID\"=64" No Error, no display

SELECT * FROM Table1 WHERE INDI_ID = 64 / SELECT * FROM Table1 WHERE INDI_ID=64 Error "(Ào" no display

SELECT * FROM Table1 WHERE INDI_ID='64' Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression., SQL state 22005 in SQLExecDirect in C:\xampp\htdocs\averykin\testdb.php on line 10 22005

SELECT * FROM Table1 WHERE INDI_ID=\"64\" Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in C:\xampp\htdocs\averykin\testdb.php on line 10 07001

Column INDI_ID is type INTEGER Column FullName is type VARCHAR Column Surname is type VARCHAR Column Given is type VARCHAR Column Sex is type VARCHAR Column Birth is type VARCHAR Here's the PHP:

     <?php
$conn=odbc_connect('genealogy','','');//dsn
$id=2;
$n=(int)$id;
ini_set ( 'odbc.defaultlrl' , '65536' );
$sql="SELECT * FROM Table1 WHERE INDI_ID='64'";echo $sql."</br>";
//
//FullName = 'ABNER AVERY'
//=#27-Sep-50# finds dates equal to 27 September 1950
$result=odbc_exec($conn,$sql);echo odbc_error($conn);
if (odbc_fetch_row($result))
{
    $assoc=array();
   while($user_detail = odbc_fetch_array($result) ) {
        $assoc = array_push_assoc($assoc, 'INDI_ID', $user_detail["INDI_ID"]);
        $assoc = array_push_assoc($assoc, 'Given', $user_detail["Given"]);

possible duplicate of [Jet/Sql not returning INTEGER](http://stackoverflow.com/questions/17573922/jet-sql-not-returning-integer) — andrewsi, Jul 11 '13 at 16:25
Done. Just trying to improve my question on a new thread. Excuse me. — Lynn, Jul 11 '13 at 16:39

score 1 · Answer 1 · answered Jul 11 '13 at 17:22

1

It appears from your code that you're trying to "escape" the table name, or wrap it so that the query engine will recognize your field.

In Access/JET this is typically done using square brackets. I'm not familiar with the ODBC driver you're using so I don't know how closely it relates to pure JET.

$sql="SELECT * FROM Table1 WHERE [INDI_ID] = 64";echo $sql."</br>";

Don't wrap your number 64 in quotes of any kind unless the field data type you're using is text.

Here's some recommendations to try to narrow down to the real problem:

Try retrieving only a single field.
Try changing your Primary Key field name to something even simpler, with no symbols in it.

$sql="SELECT ID FROM Table1 WHERE ID = 64";echo $sql."";

answered Jul 11 '13 at 17:22

HK1

11,941
14
64
99

The brackets and single field didn't work, I'll try renaming the field. – Lynn Jul 11 '13 at 17:32
HK1 is showing the correct syntax. If you still are not getting results it almost certainly means that you do not have data that matches the criteria you are using. I would double-check what you've actually got in your table. – mwolfe02 Jul 11 '13 at 17:43
Column INDI_ID is type INTEGER Column FullName is type VARCHAR Column Surname is type VARCHAR ........ – Lynn Jul 11 '13 at 18:26
I've added a successful display, and duplicated the INDI_ID column to INDID... STILL no luck retrieving data by integer. – Lynn Jul 11 '13 at 21:23
Screw Access, I'll use XML, Thanks for your suggestions. – Lynn Jul 14 '13 at 23:28

Bug in Jet/Sql?

1 Answers1