Table with keyword as its name

Use this forum to request new features or suggest modifications to existing features

Table with keyword as its name

Postby JonCat » Tue May 29, 2018 8:28 am

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).
JonCat
 
Posts: 9
Joined: Tue May 29, 2018 7:31 am

Re: Table with keyword as its name

Postby qxorm » Tue May 29, 2018 3:22 pm

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 ?
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Table with keyword as its name

Postby JonCat » Wed May 30, 2018 6:50 am

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.
JonCat
 
Posts: 9
Joined: Tue May 29, 2018 7:31 am

Re: Table with keyword as its name

Postby qxorm » Mon Jun 04, 2018 7:56 pm

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)
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Table with keyword as its name

Postby JonCat » Tue Jun 05, 2018 12:10 pm

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?
JonCat
 
Posts: 9
Joined: Tue May 29, 2018 7:31 am

Re: Table with keyword as its name

Postby qxorm » Tue Jun 05, 2018 3:58 pm

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) ?
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Table with keyword as its name

Postby qxorm » Tue Jun 05, 2018 7:47 pm

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 ?
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Table with keyword as its name

Postby JonCat » Tue Jun 05, 2018 8:44 pm

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!
JonCat
 
Posts: 9
Joined: Tue May 29, 2018 7:31 am

Re: Table with keyword as its name

Postby qxorm » Wed Jun 06, 2018 3:24 pm

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
qxorm
Site Admin
 
Posts: 481
Joined: Mon Apr 12, 2010 7:45 am

Re: Table with keyword as its name

Postby JonCat » Wed Jun 06, 2018 3:55 pm

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?
JonCat
 
Posts: 9
Joined: Tue May 29, 2018 7:31 am

Next

Return to QxOrm - Feature request

Who is online

Users browsing this forum: No registered users and 3 guests

cron