hex syntax support

[e-belair]

Hi, I'm using uuid field and I try to use the hex syntax in a doctrine query to obtain something like this:

SELECT * FROM table WHERE id = x'64f8d6f011163f48b4cb8325773d13ff'

https://firebirdsql.org/refdocs/langrefupd25-hexbinstrings.html

Do you have an idea of how I could implement it in doctrine?

[kafoso]

You will probably need to do a native query for that to work out, and not parameterizing that specific value. Just make absolutely sure that the string is on the correct format to avoid SQL vulnerabilities. E.g. through a regular expression such as:

/^x'[0-9a-f]{32}'$/

[e-belair]

Finally, I found another way to use it by sending binary as parameter I'm using https://github.com/ramsey/uuid-doctrine

[e-belair]

I created a user defined function to use the x'' syntax. I don't use it anymore but maybe it could be usefull:

namespace Application\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\InputParameter;
use Doctrine\ORM\Query\Lexer;

/**
 * Class XExtension
 * Usage in query: $qb->where('e.id = X(:id)')->setParameter('id', $value)
 * @package Application\Doctrine
 */
class XExtension extends FunctionNode
{
    /** @var InputParameter */
    private $stringPrimary = null;

    /**
     * @param \Doctrine\ORM\Query\SqlWalker $sqlWalker
     *
     * @return string
     * @throws \Doctrine\ORM\Query\AST\ASTException
     */
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        $this->stringPrimary->dispatch($sqlWalker);
        return "x'".$sqlWalker->getQuery()->getParameter($this->stringPrimary->name)->getValue()."'";
    }

    /**
     * @param \Doctrine\ORM\Query\Parser $parser
     *
     * @return void
     * @throws \Doctrine\ORM\Query\QueryException
     */
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $lexer = $parser->getLexer();
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->stringPrimary = $parser->InputParameter();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

If you think it's a good idea, let me know and I'll make a PR. If not, close the ticket.

[kafoso]

It seems to me that ramsey/uuid and ramsey/uuid-doctrine provide much of the answer and solution to your inquiry. You can convert the string to and from binary using hex2bin and unpack. For cohesion, you may even use \Ramsey\Uuid\Codec\StringCodec-> encodeBinary (...) and \Ramsey\Uuid\Codec\StringCodec->decodeBytes(...).

Your second reply with the code snippet is nice, but it does not belong in this library. The goal with this library is to tackle only the absolute fundamentals of the DBAL.