Prepared query

[marcosh]

From the documentation of Orient Db I see that it supports Prepared queries.

In PhpOrient I can't see any reference to prepared queries.

Is it possible to use them or are they not supported with the current version of the driver?

[Ostico]

Sorry, PhpOrient uses the OrientDB binary protocol, in that protocol there are no support for prepared statements.

I'm planning to implement something similar to PDO Prepared statement in the future, but escape and sanitize functions will not be made by OrientDB server ( like PDO and MySQL for example ) but only in the client side.

[andreyvk]

+1 for parameterized queries.

[marcosh]

Thanks for your answer. Can I be of any help with the implementation of this?

[Ostico]

Sure you can, my idea is to implement an interface to the object Statement like http://php.net/manual/en/intro.pdo.php

If you want to collaborate send me an email and we will talk about this work :)

https://it.linkedin.com/in/domenicolupinetti

[andreyvk]

@marcosh Any progress on that one? Would be great to have this feature!

[marcosh]

@andreyvk we started working on it, but now it's kind of stuck. You can find the project here: https://github.com/Ostico/PhpOrientStatements and contribute to it if you want

[andreyvk]

@marcosh What's the problem (what are the problems) at the moment?

[Ostico]

In my case i lack time to develop it at moment

[marcosh]

same here... the last thing I did was starting to work on the binding of parameters, I even left a non passing test... if you want you could start from there

[andreyvk]

@Ostico, @marcosh If I have time I'll take a look.

@marcosh Can you give me a starting point from where I can start looking at the code, which does the binding of parameters?

[mihai-stancu]

@Ostico doesn't v2.1.x have support for prepared queries already? I'm referring to this document which specifies that you can serialize a Map with the values of the parameters (either as numerically indexed array for positionals or as string indexed array for named parameters).

Also i've tested v2.2-alpha which introduced prepared queries for the HTTP API as described here and it works nicely.

@andreyvk I'd follow the stack like this:

PhpOrient\PhpOrient::command($query)
PhpOrient\Protocols\Binary\SocketTransport::execute('command', $params)
PhpOrient\Protocols\Binary\SocketTransport::operationFactory() // returns a PhpOrient\Protocols\Binary\Operations\Command
PhpOrient\Protocols\Binary\Abstracts\Operation::prepare()
PhpOrient\Protocols\Binary\Abstracts\Operation::send()
PhpOrient\Protocols\Binary\Operations\Command::_write()

...I think introducing another parameter PhpOrient\PhpOrient::command($query, $params) and propagating it down the call stack until you reach the _write method where you'll have to serialize the array.

[andreyvk]

@mihai-stancu Thanks. Let me take a look. But im also having some problems with time now )) need to finish a few things before I can do anything else

[smolinari]

:+1: To seeing this get done! :smile:

Scott

[Krienas]

hey @Ostico. I just took a look into http://orientdb.com/docs/2.2/Network-Binary-Protocol-Commands.html#sql-select-query . It looks like prepared queries are already supported and exposed in current binary protocol. Query command payload:

(text:string)(non-text-limit:int)[(fetch-plan:string)](serialized-params:bytes[])

where

serialized-params the byte[] result of the serialization of a ODocument.

and

Serialized Parameters ODocument content

The ODocument have to contain a field called "params" of type Map. the Map should have as key, in case of positional perameters the numeric position of the parameter, in case of named parameters the name of the parameter and as value the value of the parameter.

There are params for SQL commands and Script commands also.

For sake of experimentation I had tried to hack in support params in phpOrient's query() method:

1. Calling with $phpOrient->query('select from OUser where name = :name');
2. After vendor/ostico/phporient/src/PhpOrient/Protocols/Binary/Operations/Command.php:176 had added $_payload[] = Writer::packBytes( CSV::serialize($this->params) );
3. Then in vendor/ostico/phporient/src/PhpOrient/PhpOrient.php:297 (after $params[ 'fetch_plan' ] initialization had tried different ways to add initialization of $params[ 'params' ]. Thing I tried: $params[ 'params' ] = ['params' => ['name'=>'admin']];; just $params[ 'params' ] = ['name'=>'admin'];; also `$params[ 'params' ] = ['parameters' => ['name'=>'admin']];

When I run this I get an exception:

[PhpOrient\Exceptions\PhpOrientException]
com.orientechnologies.orient.core.exception.OSerializationException: Error on unmarshalling content. Class: com.orientechnologies.orient.core.sql.query.OSQLSynchQuery
DB name="GratefulDeadConcerts"

Exception trace: () at /code/hm/vendor/ostico/phporient/src/PhpOrient/Protocols/Binary/Abstracts/Operation.php:218 PhpOrient\Protocols\Binary\Abstracts\Operation->_readError() at /code/hm/vendor/ostico/phporient/src/PhpOrient/Protocols/Binary/Abstracts/Operation.php:145 PhpOrient\Protocols\Binary\Abstracts\Operation->_readHeader() at /code/hm/vendor/ostico/phporient/src/PhpOrient/Protocols/Binary/Abstracts/Operation.php:286 PhpOrient\Protocols\Binary\Abstracts\Operation->getResponse() at /code/hm/vendor/ostico/phporient/src/PhpOrient/Protocols/Binary/SocketTransport.php:163 PhpOrient\Protocols\Binary\SocketTransport->execute() at /code/hm/vendor/ostico/phporient/src/PhpOrient/PhpOrient.php:302 PhpOrient\PhpOrient->query()

in logs I find:

2016-11-19 15:29:06:581 INFO {db=GratefulDeadConcerts} /127.0.0.1:38392 - Read 148 bytes: :com.orientechnologies.orient.core.sql.query.OSQLSynchQuery$select from OUser where name = :name*:0"params":{"name":"admin"}} [OChannelBinaryServer] 2016-11-19 15:29:06:582 WARNI {db=GratefulDeadConcerts} Error deserializing record with id #-1:-1 send this data for debugging: eyJwYXJhbXMiOnsibmFtZSI6ImFkbWluIn19 [ORecordSerializerBinary]$ANSI{green {db=GratefulDeadConcerts}} Error on unmarshalling content. Class: com.orientechnologies.orient.core.sql.query.OSQLSynchQuery java.lang.ArrayIndexOutOfBoundsException: 123 at com.orientechnologies.orient.core.serialization.serializer.record.binary.ORecordSerializerBinary.fromStream(ORecordSerializerBinary.java:78) at com.orientechnologies.orient.core.record.impl.ODocument.deserializeFields(ODocument.java:1851) at com.orientechnologies.orient.core.record.impl.ODocument.checkForFields(ODocument.java:2625) at com.orientechnologies.orient.core.record.impl.ODocument.setFieldType(ODocument.java:1773) at com.orientechnologies.orient.core.sql.query.OSQLQuery.deserializeQueryParameters(OSQLQuery.java:148) at com.orientechnologies.orient.core.sql.query.OSQLQuery.queryFromStream(OSQLQuery.java:139) at com.orientechnologies.orient.core.sql.query.OSQLSynchQuery.queryFromStream(OSQLSynchQuery.java:150) at com.orientechnologies.orient.core.sql.query.OSQLQuery.fromStream(OSQLQuery.java:111) at com.orientechnologies.orient.core.serialization.serializer.stream.OStreamSerializerAnyStreamable.fromStream(OStreamSerializerAnyStreamable.java:84) at com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary.command(ONetworkProtocolBinary.java:1379) at com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary.executeRequest(ONetworkProtocolBinary.java:576) at com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary.sessionRequest(ONetworkProtocolBinary.java:321) at com.orientechnologies.orient.server.network.protocol.binary.ONetworkProtocolBinary.execute(ONetworkProtocolBinary.java:197) at com.orientechnologies.common.thread.OSoftThread.run(OSoftThread.java:77)

Maybe you have an idea what I am doing wrong? This mention of ODocument in specs confuses me, what is that from phpOrient perspective? Simple array map, or maybe I need to serialize some instance of Record? If I get this working, then I could try to contribute to proper parameter support implementation.