Attaching SQL statements to transactions

[electricjones]

I am using the PhpOrient driver and am trying to setup transactions. I have everything working fine, until I try to attach a sql command to the transaction.

I am not sure if this is a limitation of the PhpDriver or of Orient itself, or the binary protocol the driver is built upon.

Is it possible to attach sql commands to transactions?

My code:

//get the transaction and start it
$tx = $client->getTransactionStatement();

//BEGIN THE TRANSACTION
$tx = $tx->begin()

$create = $client->command("INSERT INTO V set test = 'a'");

$tx->attach($create);

$result = $tx->commit();

[smolinari]

From the docs, (I've never tried it myself), it looks like you are trying to do a command in the client instead of using the ORM query builder, which is shown in the docs. So I am not sure the command will work with a transaction.

Try this.

//get the transaction and start it $tx = $client->getTransactionStatement();

//BEGIN THE TRANSACTION $tx = $tx->begin()

$createCommand = $client->recordCreate( ( new Record() ) ->setOData( [ 'test' => 'a' ] ) ->setOClass( 'V' ) );

$tx->attach($createCommand);

$result = $tx->commit();

[electricjones]

Yes, that works fine, but I am actually abstracting even further. I only use PhpOrient to send SQL commands and queries through to Orient. I don't want to use recordCreate or anything like that.

I'm thinking SQL Batch may be my only option.

[smolinari]

The question to @Ostico is then, why aren't raw queries allowed to be attached to the transaction?

I have to ask, how can you abstract further than what the query builder offers?

Scott

[electricjones]

Myself and a couple others are working on http://github.com/spider/spider, which is a full OGM for graph database abstraction. It includes several drivers including OrientDB, Neo4j, and Gremlin Server at the moment. It will include Models, Schema Builders, and currently has working query and command builders, though limited. The idea is to create a simple, fluent, and beautiful abstraction api for working with graph data across datastores.

The work is in the develop branch. I'm using this repo as a dependency in the Orient Driver. I haven't forked it.

[smolinari]

Ah, thanks for the info. Your project sounds interesting and I am now following it.

Scott

[electricjones]

I've looked into the Orient Documentation, and I think this is actually a limitation of the binary protocol. I came across orient issue (orientechnologies/orientdb#4045) which led me to use sql batch until I find something better.

[Ostico]

Hi @chrismichaels84 ,

i'm happy to know about your project! I will provide you full support if you need.

Regarding the command/queries statement inside transactions, you're right, only CRUD method are allowed in transactions. It's a limitation of the binary protocol, and the only workaround is, as you already said, the SQL batch command.

[electricjones]

Awesome. I'll go ahead and close this issue.