I am using PHP PDO to access sqlite and have reached a peculiar error
situation. Basically its saying I can't have parameters in a CREATE
VIEW sql statement when I am preparing it.
I can't find any reference to this restriction or the error message in
the SQLite documentation, so I am wondering is it a SQLite restriction
or something PDO is forcing upon me?
This is the prepare statement followed by a var dump to check the error
code.
$vstmt = $db->prepare("
CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst,
dstcode,t.description, rno, repeat,
CASE
WHEN t.currency = ? THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = ? THEN
t.srcamount
WHEN t.dstamount IS NOT NULL AND da .currency = ? THEN
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS
INTEGER)
END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != ? AND
(t.srcamount IS NULL OR sa.currency != ? ) AND
(t.dstamount IS NULL OR da.currency != ?) AND
t.currency = currency.name;
");
var_dump($db->errorInfo());
and this gives the following error info
array(3) {
[0]=>
string(5) "HY000"
[1]=>
int(1)
[2]=>
string(35) "parameters are not allowed in views"
}
All the parameters are to be bound to the same value, the 3 letter name
of a default currency which will be passed in via a $_POST variable for
this particular Ajax called routine.
I could just use PHP to merge in the variable into the string, but I
have been using prepare and bindValue to prevent SQL injection attacks
so I am a little nervous of breaking this rule.
Is there a particular reason for this restriction (if it is indeed an
SQLite one).
--
Alan Chandler
http://www.chandlerfamily.org.uk
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
