Problem with naming convention

PARTHIBAN GANESAN

I have issues in field names. Suppose I have put:
Field::inst( 'Customer')
it will change as CUSTOMER on query execution....then it will throw INVALID IDENTIFIER error
Seems like i have problem with Oracle naming convention.

allan

If you are using Oracle, then yes, stick with uppercase characters. You could use:

Field::inst( 'CUSTOMER', 'Customer')

The first is the db field name and the second is the HTTP / JSON name. Its a little redundant though.

Allan

Mariusz Glugla

Hi Allan.
I think there is a problem anyway with handling camel names. In Oracle such names must be wrapped with double quotation character when referenced in query.
Fields we have as key ones, are also camel names (i.e. containst lower and upper case letters) and we can not force DataTables force to work with this.
Example:
CREATE TABLE "TestTable" (
"COL1" varchar2(5),
"Col2" varchar2(5))
;

php and html to work with above table:

--PHP--

Editor::inst( $db, '"PatchMgmt"."TestTable"', 'COL1' )
    ->fields(
        Field::inst( 'COL1', 'Col1' )
        #Field::inst( $ip ),
        #Field::inst( $sg )
    )
    ->debug(true)
    ->process( $_POST )
    ->json();

--HTML jscript--

$('#example').DataTable( {
        dom: 'Bfrtip',
        ajax: 'mg2.php',
        columns: [
            { data: 'COL1', className: 'editable' }
         //   { data: 'RAA_Comment', className: 'editable' }
        ]

debug:
[{query: "SELECT COL1 as "COL1" FROM "PatchMgmt"."TestTable" ", bindings: []}]

Above works fine.
But we can not make it working with camel named column Col2.
With following php:

Editor::inst( $db, '"PatchMgmt"."TestTable"', 'COL2' )
    ->fields(
        Field::inst( 'COL2' )
        #Field::inst( $ip ),
        #Field::inst( $sg )
    )

it gives sql error:
debug:
[{query: "SELECT COL2 as "COL2" FROM "PatchMgmt"."TestTable" ", bindings: []}]
error:
"Oracle SQL error: ORA-00904: "COL2": invalid identifier"

This is because valid SQL statement should be: SELECT "Col2" as "COL2" ...

If in .php I specify column name as '"Col2"' (i.e. wrapped with double quot mark) then other error occurs because DataTable framework adds its own double quote characters around column name and SQL ends up with empty name:

<b>Warning</b>:  oci_error() expects parameter 1 to be resource, boolean given in <b>/var/www/html/Editor-PHP-1.7.2/php/Database/Driver/Oracle/Query.php</b> on line <b>163</b><br />
{"fieldErrors":[],"error":"Oracle SQL error: ","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT  \"Col2\" as \"\"Col2\"\", COL2 as \"COL2\" FROM  \"PatchMgmt\".\"TestTable\" ","bindings":[]}]}

So it seems that DataTables have issues with handling camel named columns as key fields and also as data columns, because even when I sued COL1 as key field, I was not able to retrieve data for Col2 following your suggestion for Parthiban's question.

Mariusz Glugla

I made following test:
Having COL1 as key field:

Editor::inst( $db, '"PatchMgmt"."TestTable"', 'COL1' )

because such name is handled properly and in order to have problem of referencing to name with mixed case letters used for data separated from problem of reference used for key field, I checked following configurations, but still failed:
Field::inst( 'COL2' )
Field::inst( 'Col2' )
Field::inst( 'Col2 as COL2' )
Field::inst( 'Col2', 'COL2' )

{"fieldErrors":[],"error":"Oracle SQL error: ORA-00904: \"COL2\": invalid identifier","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT COL1 as \"COL1\", COL2 as \"COL2\" FROM \"PatchMgmt\".\"TestTable\" ","bindings":[]}]}

Field::inst( '"Col2"' )
Field::inst( '"Col2" as COL2' )
Field::inst( '"Col2"', 'COL2' )

_Warning: oci_parse(): ORA-01741: illegal zero-length identifier in /var/www/html/Editor-PHP-1.7.2/php/Database/Driver/Oracle/Query.php on line 133

Warning: oci_error() expects parameter 1 to be resource, boolean given in /var/www/html/Editor-PHP-1.7.2/php/Database/Driver/Oracle/Query.php on line 163
{"fieldErrors":[],"error":"Oracle SQL error: ","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT COL1 as \"COL1\", \"Col2\" as \"\"Col2\"\" FROM \"PatchMgmt\".\"TestTable\" ","bindings":[]}]}_

Now about second problem i.e. camel field name used as key field.
I manged to have working SQL statement (i.e. SQL is executed succesfully) when used alias for key:

Editor::inst( $db, '"PatchMgmt"."TestTable"', '"Col2" as COL2' )
    ->debug(true)
    ->process( $_POST )
    ->json();

but other error is raised by other part of DT framework:
{"fieldErrors":[],"error":"Primary key element is not available in data set.","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT \"Col2\" as \"COL2\", COL1 as \"COL1\" FROM \"PatchMgmt\".\"TestTable\" ","bindings":[]}]}

Let's add another column which is not a problem for framework:
ALTER TABLE "TestTable" ADD ("COL3" varchar2(2))

And now try this:

Editor::inst( $db, '"PatchMgmt"."TestTable"', 'COL1' )
    ->fields(
        Field::inst( 'COL3'  )
    )

Working fine!
{"data":[{"DT_RowId":"row_1","COL3":"ss"},{"DT_RowId":"row_2","COL3":"b"},{"DT_RowId":"row_4","COL3":"c"},{"DT_RowId":"row_3","COL3":"d"}],"options":[],"files":[],"debug":[{"query":"SELECT COL1 as \"COL1\", COL3 as \"COL3\" FROM \"PatchMgmt\".\"TestTable\" ","bindings":[]}]}

But following don't:

Editor::inst( $db, '"PatchMgmt"."TestTable"', 'COL1 as COL1' )
    ->fields(
        Field::inst( 'COL3'  )
    )
    ->debug(true)
    ->process( $_POST )
    ->json();

{"fieldErrors":[],"error":"Primary key element is not available in data set.","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT COL1 as \"COL1\", COL3 as \"COL3\" FROM \"PatchMgmt\".\"TestTable\" ","bindings":[]}]}

So it seems that when alias is used in key field, we have other issue.

allan

'"PatchMgmt"."TestTable"'

That isn't going to work at all in Editor's PHP libraries I'm afraid. If you have a look in the php/Database/Driver/Oracle/Query.php file there should be:

protected $_field_quote = '"';

which will be used as the quote character value.

It should be that all you need is Field::inst( 'CUSTOMER', 'Customer') and don't attempt to do any of the quoting yourself in the table name. What is the result if you do that?

Allan

Mariusz Glugla

As per first part of answer- there is no problem with quotation in schema and table names- works properly.
Problem is with handling column names defined in Field object
What you proposed does not work, ORA throws error:

{"fieldErrors":[],"error":"Oracle SQL error: ORA-00904: \"CUSTOMER\": invalid identifier","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT COL1 as \"COL1\", CUSTOMER as \"CUSTOMER\" FROM \"PatchMgmt\".\"TestTable\" ","bindings":[]}]}

It is strange, seems that alias name is wrapped with quotas but source name not.
In other words I would expect:
Field::inst('NAME1', 'ALIASNAME')
to be translated in sql to:
SELECT "NAME1" as "ALIASNAME",
however from what I see Editor always translates it to:
SELECT NAME1 as "NAME1"
i.e. source name is not quoted at all and then even used as alias.
I made this test:

Editor::inst( $db, '"PatchMgmt"."TestTable"', 'COL1' )
    ->fields(
        Field::inst( 'SRC_NAME', 'ALIAS_NAME' )
    )

Ends up with error:

{"fieldErrors":[],"error":"Oracle SQL error: ORA-00904: \"SRC_NAME\": invalid identifier","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"SELECT COL1 as \"COL1\", SRC_NAME as \"SRC_NAME\" FROM \"PatchMgmt\".\"TestTable\" ","bindings":[]}]}

allan

SELECT NAME1 as "NAME1"

That is correct. Then the Editor libraries will convert it from NAME1 to be ALIASNAME in the JSON that is sent to the client-side, and also what it expects to read from the client-side when the data is submitted. The "alias" isn't an SQL alias!

In the Query.php file I mentioned before, could you replace:

protected $_identifier_limiter = null;

with:

protected $_identifier_limiter = [ '"', '"' ];

Again, don't attempt to add the quotes into the strings you pass in. That will just confuse things I think.

Allan

Mariusz Glugla

Hi, thank you. Works better, but ...
We have now problem with column which is choosen as Key Field when creating row:
Oracle SQL error: ORA-00904: "COL2": invalid identifier

Editor::inst( $db, 'PatchMgmt.TestTable', 'Col2' )
    ->fields(
        Field::inst( 'Col2' ),
        Field::inst( 'COL1' )
    )
    ->process( $_POST )
    ->json();

Judging from debug, quiting is not done when column is referenced in RETURNING:

{"fieldErrors":[],"error":"Oracle SQL error: ORA-00904: \"COL2\": invalid identifier","data":[],"ipOpts":[],"cancelled":[],"debug":[{"query":"INSERT INTO  \"PatchMgmt\".\"TestTable\"  ( \"Col2\", \"COL1\" ) VALUES (  :Col2,  :COL1 ) RETURNING Col2 INTO :editor_pkey_value","bindings":[{"name":":Col2","value":"2","type":null},{"name":":COL1","value":"1","type":null}]}]}

allan

In php/Database/Drivers/Oracle/Query.php have a look for the string editor_pkey_value and replace it with a capitalised version which should address that.

Allan

Mariusz Glugla

Hi.
If you have meant change from editor_pkey_value to EDITOR_PKEY_VALUE, then it doesn't work.

But this was helpfull:
line 129 from:

$sql .= ' RETURNING '.(is_array($pkey) ? $pkey[0] : $pkey).' INTO :editor_pkey_value';

to:

$sql .= ' RETURNING '.(is_array($pkey) ? $this->_field_quote.$pkey[0].$this->_field_quote : $pkey).' INTO :editor_pkey_value';

Please confirm if this is correct bugfix.
Thank you.

allan

Yes that looks valid.

Thanks,
Allan

Mariusz Glugla

Thank you Allan.
Will these two fixes be included in official release?

allan

I'm not certain yet. It isn't an issue that I've run into with Oracle myself, and I've not had other reports of it, so I'm not certain why it is required for yourself but not else where.

If I find this to be required else where with no negative side effects, then yes it will be included.

Allan

PARTHIBAN GANESAN

Hi Allan,

Now this Solution for Camelcase letters works fine but now i'm dealing with another issue:

we have a dot in-between the column name 'C.L3Support' where C is not a table name or table alias name. I have mentioned the name like follow:
Field::inst( 'C.L3Support' )

Now the issue was when i run the server side script, i got an error like,
"error":"Oracle SQL error: ORA-00904: \"C\".\"L3Support\": invalid identifier",
it separate C and L3Support with that dot. I want to convert these as "C.L3Support"

Can you please advise how i can proceed further with this issue?

allan

The Editor PHP libraries will always treat the . as a separator I'm afraid - that is fairly ingrained into them and there isn't a way around that.

Are you able to create a VIEW of your table without the .?

Thanks,
Allan

Mariusz Glugla

Regarding you answer about including fixes in official release- I would say camel naming is not common that's probably why others did not report these issues.
As per latest problem with dots in names- it comes due to when name is quoted, Oracle allows characters which are forbidden when identifier is not quoted.
Point 7 from:
https://docs.oracle.com/database/121/SQLRF/sql_elements008.htm#SQLRF51130

"Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@).

Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0)."

So if DataTables is to be fully compliant with Oracle, then it should follow this rule in my opinion. But yes, I agree it's bad habit to use dot in identifier.

allan

Thanks for the feedback!

Allan

Mariusz Glugla

Hi Allan. Fix for issue with RETURNING for create action was not perfect as we suffered problem when reference to returned column was TABLE.FIELD which is required in solution with joined tables where in two tables there are columns with same name- then we need use table name in order to prevent Oracle error.
So when fix surrounded reference with quotation marks there was RETURNING "TABLE.FIELD" what for Oracle was single name of non existing column. To resolve this, dot is now replaced with "." what will result in RETURNING TABLE"."FIELD" as expected.
Sure this fix will not work with column name containing dot, but this is wider problem for DataTables which anyway does not handle it right now as we reported above.