Page 1 of 2

Table with keyword as its name

PostPosted: Tue May 29, 2018 8:28 am
by JonCat
Where a table is named, for example, 'Group', QxOrm is not able to create a query for this table as it will get rejected by MSSQL Server as it doesn't understand the table name isn't a keyword.

Can we have an option in QxEntityEditor to force creation of the table name with square brackets, and corresponding modifications in QxOrm to handle them as appropriate?

A temporary workaround is to use synonyms in Sql Server (or not to use keywords in the first place).

Re: Table with keyword as its name

PostPosted: Tue May 29, 2018 3:22 pm
by qxorm
Hello,

If we add an option in QxOrm library to add brackets to all tables (whatever is defined in QxEntityEditor) : does it solve your issue ?

Re: Table with keyword as its name

PostPosted: Wed May 30, 2018 6:50 am
by JonCat
Yes that would certainly work. It might be worth also square bracketing schemas / column names too just for consistency, as they could have similar problems with reserved keywords / spaces / hyphens.

Re: Table with keyword as its name

PostPosted: Mon Jun 04, 2018 7:56 pm
by qxorm
Hello,

Here is a version which adds an option to insert square brackets to tables/columns in SQL queries : https://www.qxorm.com/version/QxOrm_1.4.5_BETA_14.zip

Code: Select all
qx::QxSqlDatabase::getSingleton()->setAddSqlSquareBracketsToTableName(true);
qx::QxSqlDatabase::getSingleton()->setAddSqlSquareBracketsToColumnName(true);


Please let me know if it works as expected.

Here is the changes log of this version :
- Fix an issue in qx::IxSqlQueryBuilder class when QxOrm library is used in a multi-thread environment
- Support latest version of boost (1.66)
- Update boost portable binary serialization classes to version 5.1 (provided by https://archive.codeplex.com/?p=epa)
- Fix an issue building SQL query for Oracle database (doesn't support AS keyword for table alias)
- Improve qx::QxClassX::registerAllClasses() function : possibility to initialize all relations (useful to work with introspection engine)
- Improve qx::IxPersistable interface : provide new methods toJson() / fromJson()
- Improve documentation/website : change http://www.qxorm.com by https://www.qxorm.com everywhere
- Fix fetching relations with soft delete putting SQL condition in the JOIN part instead of WHERE part
- Fix SQL generator for Oracle database : use new limit/pagination syntax (version Oracle > 12.1)
- Improve SQL generator interface : add 'onBeforeSqlPrepare()' method to modify/log SQL queries in custom classes
- Add an option in qx::QxSqlDatabase class to format SQL query (pretty-printing) before logging it (can be customized creating a qx::dao::detail::IxSqlGenerator sub-class)
- Fix an issue with boost/std::optional (to manage NULL database values) and some databases : if optional is empty, then create a NULL QVariant based on QVariant::Type
- Add an option in qx::QxSqlDatabase class to insert square brackets in SQL queries for table name and/or column name (to support specific database keywords)

Re: Table with keyword as its name

PostPosted: Tue Jun 05, 2018 12:10 pm
by JonCat
No luck I'm afraid :(

It looks like the code for wrapping in square brackets isn't taking into account schema / table being different entities, so I'm getting error messages back from SQL server of the format:
Code: Select all
Invalid object name 'schemaname.tablename'


I'm also getting query failures without either of the new flags set so I'm wondering if another modification might be causing the problem?

Re: Table with keyword as its name

PostPosted: Tue Jun 05, 2018 3:58 pm
by qxorm
It looks like the code for wrapping in square brackets isn't taking into account schema / table being different entities

Could you please copy/past an example of generated SQL queries ?

I'm also getting query failures without either of the new flags set so I'm wondering if another modification might be causing the problem?

Strange, I don't see any other modif which could generate a regression.
Could you please provide more details (error message, SQL query comparaison previous/new version) ?

Re: Table with keyword as its name

PostPosted: Tue Jun 05, 2018 7:47 pm
by qxorm
Could you please try this new version : https://www.qxorm.com/version/QxOrm_1.4.5_BETA_15.zip
I think I found where the regression was.
Is it better now ?

Re: Table with keyword as its name

PostPosted: Tue Jun 05, 2018 8:44 pm
by JonCat
Fixed for MSSQL - thanks!

MySQL is causing problems, though. I believe MySQL uses backticks (`) rather than square brackets to enclose reserved keywords in table/column names.

I'm unsure if other databases might have issues also... might be worth limiting square brackets only for MSSQL and backticks for MySQL.

Sorry to complicate matters!

Re: Table with keyword as its name

PostPosted: Wed Jun 06, 2018 3:24 pm
by qxorm
Fixed for MSSQL - thanks!

Great !

MySQL is causing problems, though. I believe MySQL uses backticks (`) rather than square brackets

Here is another version : https://www.qxorm.com/version/QxOrm_1.4.5_BETA_17.zip

For MySQL, you can write :
Code: Select all
   qx::QxSqlDatabase::getSingleton()->setSqlDelimiterForTableName(QStringList() << "`");
   qx::QxSqlDatabase::getSingleton()->setSqlDelimiterForColumnName(QStringList() << "`");

For MSSQL Server, you can write (or still use the previous method setAddSqlSquareBracketsToTableName) :
Code: Select all
   qx::QxSqlDatabase::getSingleton()->setSqlDelimiterForTableName(QStringList() << "[" << "]");
   qx::QxSqlDatabase::getSingleton()->setSqlDelimiterForColumnName(QStringList() << "[" << "]");


Please let me know if it works as expected.

The changes log has changed a bit :
- Fix an issue in qx::IxSqlQueryBuilder class when QxOrm library is used in a multi-thread environment
- Support latest version of boost (1.66)
- Update boost portable binary serialization classes to version 5.1 (provided by https://archive.codeplex.com/?p=epa)
- Fix an issue building SQL query for Oracle database (doesn't support AS keyword for table alias)
- Improve qx::QxClassX::registerAllClasses() function : possibility to initialize all relations (useful to work with introspection engine)
- Improve qx::IxPersistable interface : provide new methods toJson() / fromJson()
- Improve documentation/website : change http://www.qxorm.com by https://www.qxorm.com everywhere
- Fix fetching relations with soft delete putting SQL condition in the JOIN part instead of WHERE part
- Fix SQL generator for Oracle database : use new limit/pagination syntax (version Oracle > 12.1)
- Improve SQL generator interface : add 'onBeforeSqlPrepare()' method to modify/log SQL queries in custom classes
- Add an option in qx::QxSqlDatabase class to format SQL query (pretty-printing) before logging it (can be customized creating a qx::dao::detail::IxSqlGenerator sub-class)
- Fix an issue with boost/std::optional (to manage NULL database values) and some databases : if optional is empty, then create a NULL QVariant based on QVariant::Type
- Add an option in qx::QxSqlDatabase class to insert square brackets (or any other delimiters) in SQL queries for table name and/or column name (to support specific database keywords)
- Improve introspection engine : add getType() method in qx::IxDataMember interface to get C++ type of a property dynamically

Re: Table with keyword as its name

PostPosted: Wed Jun 06, 2018 3:55 pm
by JonCat
The singleton approach for this could be a little problematic, as I'm concurrently connecting to MSSQL and MySQL databases on various threads.

Would it be an invasive change to associate these settings per sql generator rather than globally?