This is an archived copy of the forum for reference purposes

%r prepared statement bug (with postgresql)

posted by theyouyou 8 years ago

The following SQL statement produces a SQL error:

$prepared = $this->db->prepare("UPDATE documents SET %r = %s WHERE document_id = %i");	
$this->db->query($prepared, 'author', 'theyouyou', 23);

SQL error (translated from french) :

PostgreSQL error (ERROR: bind message passes 3 parameters but the prepared statement fstm1 needs 0) in {false}

BTW the "normal" statement cause no error

fStatement object (in firephp):

bound_params =
database = fDatabase(...)
identifier = 'fstmt1'
placeholders = array(
   [0] => '%s'  // Where's the '%r' ?
   [1] => '%i'
)
statement = Resource id #41
sql =
untranslated_sql =
used =
)

The PHP Log:

{doc_root}\\slire3\\app\\model\\Document.php(126): fDatabase->prepare('UPDATE document...')
{doc_root}\\slire3\\lib\\flourish\\fDatabase.php(2663): fDatabase->prepareStatement('UPDATE document...')
{doc_root}\\slire3\\lib\\flourish\\fDatabase.php(2759): fStatement->__construct(Object(fDatabase), 'UPDATE document...', Array, NULL)
{doc_root}\\slire3\\lib\\flourish\\fStatement.php(101): vsprintf('UPDATE document...', Array)
[internal function]
vsprintf(): Too few arguments

What's wrong ?

I think it is likely an issue that identifiers (column and table names) can not be provided at run time, but must be provided when preparing the statement. Try this:

$prepared = $this->db->prepare("UPDATE documents SET %r = %s WHERE document_id = %i", 'author');    
$this->db->query($prepared, 'theyouyou', 23);

If you open a ticket about this, I can make sure future version have a friendlier error message.

posted by wbond 8 years ago

I tried your example, it didn't work: I have the same error message.

But I got the answer on fDatabase.php line 2670, you wrote:

Identifier placeholders (%r) are not supported with prepared statements

I think you should add this information in the fDatabase:Limitations documentation :)

posted by theyouyou 8 years ago