SQLite query gives an error

Question

I am running PHP PDO 5.6.29. Here is my code:

$QsoId = $SQLiteData["QsoId"];
$SQLiteData["MyAntenna"] = $ODBCAnt;
$query = sprintf("INSERT INTO Log (QsoId, MyAntenna) VALUES (%s, '%s')",$QsoId, $ODBCAnt);

$qry = $SQLite["connection"]->prepare($query);
/* bind params */
$qry -> bindParam(':QsoId', $QsoId, PDO::PARAM_INT);
$qry -> bindParam(':MyAntenna', $ODBCAnt, PDO::PARAM_STR);
$res = $qry->execute();

I get a PDOException with message "SQLSTATE[HY000]:General error: 25 bind or column index out of range"

I am trying to update the MyAntenna field, but I'm using QSOId as a unique record locator. I know ahead of time this record exists and can be found. I don't want to add a new record. There are 138 fields in the record. Am I exceeding some limit?

When you say you don't want to add a new record, you mean you want to update an existing record(s)? If so then you want to do an UPDATE statement, e.g `UPDATE Log SET MyAntenna=$SQLiteData["MyAntenna"] WHERE QsoId=$QsoId` code example is non PDO and just for example purposes btw — mrjamesmyers, Jan 21 '17 at 00:52
PDO Update example http://stackoverflow.com/questions/18323065/update-query-with-pdo-and-mysql#answer-18323170 — mrjamesmyers, Jan 21 '17 at 00:53
There's nowhere to bind any values, because you've already injected them directly into the query in the sprintf() statement — Mark Baker, Jan 21 '17 at 01:07

score 0 · Accepted Answer · answered Jan 21 '17 at 01:09

0

From what you have said it looks like you're wanting to update existing record not Insert. The error you're geting is probably because the QsoId field is a primary key field that only allows unique ID's (doing an insert with the same ID would mean two rows with same ID)

To do you would do an UPDATE statement, you would do something like below (untested):

$QsoId = $SQLiteData["QsoId"];
$SQLiteData["MyAntenna"] = $ODBCAnt;
$query = " 
           UPDATE `Log`   
              SET `MyAntenna` = :MyAntenna
           WHERE `QsoId` = :QsoId ");

$qry = $SQLite["connection"]->prepare($query);
/* bind params */
$qry -> bindParam(':QsoId', $QsoId, PDO::PARAM_INT);
$qry -> bindParam(':MyAntenna', $ODBCAnt, PDO::PARAM_STR);
$res = $qry->execute();

answered Jan 21 '17 at 01:09

mrjamesmyers

454
4
13

Yes UPDATE. Everything I found on SQLite said to use INSERT and it would update an existing record or INSERT a new records if and existing record did not exist. Nothing I read mentioned UPDATE, but I probably didn't do the correct Google search. I'll try this. Thanks. – Pilot Jan 21 '17 at 04:46
1

That did the trick. FWIW "QsoId" is an unformatted DTG and in this database is guaranteed to be unique. Thanks all. – Pilot Jan 21 '17 at 05:02
Can you mark as correct answer if it fixed your issue please? – mrjamesmyers Jan 21 '17 at 11:21

SQLite query gives an error

1 Answers1