Oracle DB: Error: Escaping Quotes in Query (\")


#1

Hello Folks at Agile Toolkit,

I am investigating if Agile Data can be used to connect to an Oracle-DB to display the data in a table (or grid).

The model so far:

class Inventory extends \atk4\data\Model {

public $table = "household.inventory";

function init(){
	parent::init();
	$this->id_field = 'I_ASSETID';
	$this->addField('I_SERIALNR');
            $this->addField('I_CATEGORYID');
}

}

The call in PHP looks like this:

$db = new \atk4\data\Persistence_SQL(‘oci:dbname=database’, ‘user’, ‘password’);
$users = $db->add(‘Inventory’);
foreach($users as $user){
echo "Test: " . $user[‘I_SERIALNR’] . “\n”;
}

Calling the script in a browser gives me the following error message:

 error: "SQLSTATE[HY000]: General error: 942 OCIStmtExecute: ORA-00942: table or view does not exist\n (\/usr\/src\/packages\/BUILD\/php-5.6.36\/ext\/pdo_oci\/oci_statement.c:148)"

query: "select \"id\",\"I_SERIALNR\",\"I_CATEGORYID\" from \"household\".\"inventory\""

Why are the quotes escaped? Is there a way to prevent this from happening? And why is an id added to the select clause although I defined an id_field?

Kind regards,

Christian


#2

They are escaped because “query” variable is passed through json_encode. That’s the default behaviour of error handler, since it does not know what type arguments it will receive.

You can get your query like this:

$query = $users->action('select');
echo $query->getDebugQuery();

See doc here: http://dsql.readthedocs.io/en/develop/expressions.html?highlight=getdebugquery#Expression::getDebugQuery

EDIT:

id_field is cosumed inside parent::init(), so either set it before calling it or , better yet, use the property:

public $id_field = 'I_ASSETID';

#3

I had to go so far as to make some modifications to my agiletoolkit source files, but was able to get it working 100% with Oracle. The escaping quotes were driving me nuts until I began to understand how to work with them. I don’t have access to github here at work otherwise I would post as possible changes. If you are still experiencing problems let me know I can point out what I modified, and maybe someone else can show me a better way also. :slight_smile: I’m glad it’s at least working for me now since Agile Toolkit is definitely worth using! :slight_smile:


#4

We would surely love to see your changes!


#5

@gcooka: I would like some pointers what I have to do to get Agile Toolkit working with Oracle

Thanks,

Christian


#6

I don’t think that issue is in escaping. We have some clients which successfully use Oracle.

  1. set $id_field as property not in init method, like this:
public $id_field = 'I_ASSETID';
  1. most likely your Oracle table also is in uppercase (default for Oracle even if it works with lowercase in some Oracle clients). So try setting:
public $table = 'HOUSEHOLD.INVENTORY';

#7

Also if you’re using Oracle 12, then set your connect string with oci12:... instead of oci:....

And if you set dbname=household or dbname=HOUSEHOLD, then most likely in your $table property you only need $table = 'INVENTORY' without db schema name.

And one more thing - I see you’re using PHP 5.6. If at all possible, then try to use at least PHP 7.1 or even better - PHP 7.2. Some things can be unsupported in PHP 5.6 already or in near future.


#8

I changed the table-name to all uppercase and it is working now.

Thanks to all of you for your help. Very much appreciated.


#9

Sorry for the delay! I made several modifications to get agiletoolkit to work with Oracle. I am using an Oracle 11 database, some of these might not be necessary for Oracle 12 but I don’t know. Here are the changes…

Changed lines 28-31 in agiletoolkit\vendor\atk4\dsql\src\Connection_Oracle.php to this:
$this->expr(‘ALTER SESSION SET NLS_TIMESTAMP_FORMAT={format} NLS_DATE_FORMAT={format} NLS_NUMERIC_CHARACTERS={dec_char}’, [
‘format’ => ‘YYYY-MM-DD HH24:MI:SS’, // datetime format
‘dec_char’ => '. ', // decimal separator, no thousands separator
])->execute();
Also changed line 58 to this:
return $this->expr(‘SELECT max(’ . $m->id_field . ') FROM ’ . $m->table)->getOne();

Since Oracle does not accept ‘as’ or leading underscores, before line 490 in agiletoolkit\vendor\atk4\dsql\src\Query.php I added…
$jj = preg_replace(’/"_/’,’"’,$jj);
$jj = str_replace(’ as ‘,’ ',$jj);

I also changed line 23 in agiletoolkit\vendor\atk4\dsql\src\Query_Oracle.php to this:
protected $template_select_limit = ‘select * from (select rownum “dsql_rownum”,“t”.* [from] (select[option] [field] [from] [table][join][where][group][having][order]) “t”) where “dsql_rownum”>[limit_start][and_limit_end]’;
…and line 64 to:
return ’ and “dsql_rownum”<=’.


#10

could you fork the repo and then edit the files you have changed? you can do that through www.github.com even, forum mushes up the code.


#11

@gcooka Thanks for finding a bug in lines 28-31. Will fix that in a minute.

Line 58 will not be fixed like you wrote here, because it’s insecure and also you can see my comment few lines above that with slightly more secure expression. But what’s more important - it’s very very bad for performance and no serious database use select max(id) from table solution because of that. You should use sequences in Oracle for this task and Agile Data Models support sequences too. See $model->sequence property.

Oracle supports column_name AS alias syntax and also column_name alias syntax (which we use in Agile). So not sure what’s not working for you in this case. Can you please give me more precise info how to reproduce this issue? Also please use github issues tab to create issue. That way we can fix it sooner.

About leading underscores - Oracle supports them and basically any character if it’s quoted. And it is (at least should be) quoted in Agile Data queries.
From Oracle documentation:
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

Update: fixed timestamp_format issue https://github.com/atk4/dsql/commit/76b9b16369faa377ce31b4aed268066b7cfa2905


#12

It might be that column names were being interpreted as table names, which do not allow AS. Column names with leading underscores might not have been quoted. I don’t really remember now since I modified the codes months ago and everything is working since then. I did now define a sequence for my tables and the original code works; thank you for that tip.