Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
QUERY ERROR
  • atabakatabak
    hi,
    problem with query builder
    fuel version : 1.9 (updated)
    query :SELECT id
    FROM users
    WHERE (data->>'access')::jsonb ? 'text'
    compiled query : SELECT id
    FROM users
    WHERE (data->>'access')::jsonb ? 'text'
    error : error : syntax error at or near "$1"
    LINE 1: ...ROM "users" AS "t0" WHERE (data->'access')::jsonb $1 'accoun...

    why query builder change '?' to '$1' ?
    (i use DB::exp and direct query and results are same)




  • HarroHarro
    This is PostgreSQL?

    Afaik there is no support at all for this syntax, so that needs looking at.

    You might want to check if DB::expr() is supported:

    ... ->where(\DB::expr('your expression here'))-> ...

    If you use DB::query() and you still have the problem, Fuel is not involved. It could be PDO sees it as a parameter placeholder?
  • atabakatabak
    hi Harro,
    yes i use PostgreSQL,
    DB::expr and direct query DB::query() have same problem.
    yes exactly PDO sees for parameter placeholder.
    but its new!
    old version of my application (about couple month ago) work fine.
    I'm afraid to update previous versions (my application) and it will be a problem
    the problem is currently solved by writing the function on the database side.

  • HarroHarro
    Accepted Answer
    I have no recent experience with PostgreSQL, so json syntax this is new to me.

    It might be time to have a specific driver for PostgreSQL so you don't need to use the generic PDO driver...
  • atabakatabak
    Excellent, can I help too?
  • HarroHarro
    Accepted Answer
    To create a new driver, you need to create a directory in fuel/core/database (I suggest to use "postgres") which will become the driver name, and create a connection.php class in there that extends the PDO one.

    You can look at the sqlite driver how it is done, and how it uses the extensions of query builder classes to compile non-ANSI-standard SQL.

    then in your apps' db.php config, change the driver from "pdo" to "postgres".
  • atabakatabak
    its done but need more test for security
  • HarroHarro
    Cool !
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion