How to resolve the keyword conflict?

[magicu]

My table name is [User], and I define class like this:
class CPPSERVER_DLL_EXPORT User
{
public:
User() : id(0) {};
virtual ~User() {};

int id;
QString user_name;
QString password;
QString valid_code;
QString plugin_name;
QDateTime unbind_date;
QDateTime terminate_date;
QDateTime create_date;
QDateTime heart_date;
QVariant encrypt_key; // Use QVariant to decribe null able varchar(50), right?
};

QX_REGISTER_PRIMARY_KEY(User, int)


I come across an error when query form User table using following code:
qx::QxSqlQuery query("WHERE User.user_name = :user_name");
query.bind(":user_name", L"myname");

I noticed that QxOrm generate sql:
SELECT User.id AS User_id_0, User.user_name AS User_user_name_0, User.password AS User_password_0, User.valid_code AS User_valid_code_0, User.plugin_name AS User_plugin_name_0, User.unbind_date AS User_unbind_date_0, User.terminate_date AS User_terminate_date_0, User.create_date AS User_create_date_0, User.heart_date AS User_heart_date_0, User.encrypt_key AS User_encrypt_key_0 FROM User WHERE [User].user_name = :user_name

I think the problem caused by no "[]" generated, How to resolve the keyword conflict?

[QxOrm admin]

Hi,

I don't understand very well your problem : your table is named [User] and not User ?
It's really strange to add some brackets to a table name !

Could you provide your User.hpp and User.cpp files please ?

[magicu]

This is my table struct.

-----------sql----------------

create table "User" (
id integer identity(1,1),
user_name varchar(50) not null,
password varchar(50) not null,
valid_code varchar(50) not null,
plugin_name varchar(50) not null,
is_frozen bit not null,
unbind_date datetime not null,
terminate_date datetime not null,
create_date datetime not null,
heart_date datetime null,
encrypt_key varchar(50) null,
constraint PK_USER primary key (id),
constraint AK_KEY_2_USER unique (user_name)
)




-----------User.h----------------

#pragma once

#include "export.h"

class CPPSERVER_DLL_EXPORT User
{
public:
User() : id(0) {};
virtual ~User() {};

int id;
QString user_name;
QString password;
QString valid_code;
QString plugin_name;
QDateTime unbind_date;
QDateTime terminate_date;
QDateTime create_date;
QDateTime heart_date;
QVariant encrypt_key;
};

QX_REGISTER_PRIMARY_KEY(User, int)

// This macro is necessary to register 'drug' class in QxOrm context
// param 1 : the current class to register => 'drug'
// param 2 : the base class, if no base class, use the qx trait => 'qx::trait::no_base_class_defined'
// param 3 : the class version used by serialization to provide 'ascendant compatibility'
QX_REGISTER_HPP_CPPSERVER(User, qx::trait::no_base_class_defined, 0)




-----------User.cpp----------------

#include "StdAfx.h"
#include "User.h"

#include "QxMemLeak.h"

QX_REGISTER_CPP_CPPSERVER(User)

namespace qx
{
template <> void register_class(QxClass<User> & t)
{
//t.setName("[User]");
t.id(&User::id, "id");
t.data(&User::user_name, "user_name");
t.data(&User::password, "password");
t.data(&User::valid_code, "valid_code");
t.data(&User::plugin_name, "plugin_name");
t.data(&User::unbind_date, "unbind_date");
t.data(&User::terminate_date, "terminate_date");
t.data(&User::create_date, "create_date");
t.data(&User::heart_date, "heart_date");
t.data(&User::encrypt_key, "encrypt_key");
}
}




-----------Code----------------

QSqlError daoError;

// Create a query to fetch only female author : 'author_id_2' and 'author_id_3'
qx::QxSqlQuery query("WHERE [User].[user_name] = :user_name");
query.bind(":user_name", QString::fromStdWString(userName));

qx::QxCollection<QString, boost::shared_ptr<User> > userList;
daoError = qx::dao::fetch_by_query(query, userList);



The "user","user_name","password" is the keyword of SqlServer, so we must add bracket to use it.

[magicu]

My table name is "User", but it must be typed as "[User]" to use it in SQL Server Management Studio.

For example:

"select * from [User]"
This is a correct sql.

"select * from User"
This will got an error: Incorrect syntax near the keyword 'User'.

[QxOrm admin]

Ok, so from QxOrm library point of view, your table name is called "[User]" and not "User" : so you have to write this line into your qx::register_class function => t.setName("[User]");
And this is the same thing for all fields using a special keyword :
- t.data(&User::user_name, "[user_name]");
- t.data(&User::password, "[password]");

Another solution, maybe easier, would be to rename your table and fields to not use special keyword of SqlServer !
This way, you will not have to use brackets workaround

[magicu]

I tried your method, but the error still occur, because of the QxOrm generate sql like:

SELECT [User].id AS [User]_id_0, [User].[user_name] AS [User]_[user_name]_0, [User].[password] AS [User]_[password]_0, [User].valid_code AS [User]_valid_code_0, [User].plugin_name AS [User]_plugin_name_0,[User].unbind_date AS [User]_unbind_date_0, [User].terminate_date AS [User]_terminate_date_0, [User].create_date AS [User]_create_date_0, [User].heart_date AS [User]_heart_date_0, [User].encrypt_key AS [User]_encrypt_key_0 FROM [User] WHERE [User].[user_name] = :user_name

"[User]_id_0", "[User]_[user_name]_0" and so on is incorrect syntax in sql.

daoError will get an error:
daoError = {driverError="QODBC3: Unable to execute statement" databaseError="[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '_id_0'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared." errorType=StatementError ...}

SQL management will get an error:
Incorrect syntax near '_id_0'.

I think the sql generated should be this:

SELECT [User].id AS User_id_0, [User].[user_name] AS User_[user_name]_0, [User].[password] AS User_[password]_0, [User].valid_code AS User_valid_code_0, [User].plugin_name AS User_plugin_name_0,[User].unbind_date AS User_unbind_date_0, [User].terminate_date AS User_terminate_date_0, [User].create_date AS User_create_date_0, [User].heart_date AS User_heart_date_0, [User].encrypt_key AS User_encrypt_key_0 FROM [User] WHERE [User].[user_name] = :user_name

I guess it's a bug of QxOrm.

[QxOrm admin]

You can try to provide your own SQL alias for each property of your User class in the qx::register_class function, like this :

Code: Select all

namespace qx {
template <> void register_class(QxClass<User> & t)
{
   t.setName("[User]");
   IxDataMember * pData = t.id(&User::id, "id");
   pData->setSqlAlias("User_id_0");
   pData = t.data(&User::user_name, "[user_name]");
   pData->setSqlAlias("User_user_name_0");
   pData = t.data(&User::password, "[password]");
   pData->setSqlAlias("User_password_0");
   pData = t.data(&User::valid_code, "valid_code");
   pData->setSqlAlias("User_valid_code_0");
   // etc...
} }

I think it will work.
If it doesn't work, we will have to patch the source code of QxOrm library...

[magicu]

It works, many thanks~

Could I have your msn or any other IM number?

[QxOrm admin]

It works, many thanks~

cool !

Could I have your msn or any other IM number?

Each time you have a problem or a question, it's much better to create a new topic in this forum (or the french forum).
It could help other people asking the same question...

[QxOrm admin]

If you don't want to patch your current version of QxOrm library, you can use another syntax for SQL placeholder : '?' instead of ':column_name' or '@column_name'.
To change it, you just have to write once in your code :

Code: Select all

qx::QxSqlDatabase::getSingleton()->setSqlPlaceHolderStyle(qx::QxSqlDatabase::ph_style_question_mark);

More informations about it on this Q&R of the FAQ : http://www.qxorm.com/qxorm_en/faq.html#faq_210

Or another solution, try to patch the file './src/QxDataMember/IxDataMember.cpp', method 'QString IxDataMember::getSqlPlaceHolder', with this code :

Code: Select all

QString IxDataMember::getSqlPlaceHolder(const QString & sAppend /* = QString() */, int iIndexName /* = 0 */, const QString & sSep /* = QString(", ") */, const QString & sOtherName /* = QString() */) const
{
   QString sResult;
   if (iIndexName == -1)
   {
      for (int i = 0; i < m_lstNames.count(); i++)
      { sResult += getSqlPlaceHolder(sAppend, i, sSep, sOtherName); sResult += sSep; }
      sResult = sResult.left(sResult.count() - sSep.count()); // Remove last separator
      return sResult;
   }

   // Fix special keywords database
   QString sNamePH = getName(iIndexName, sOtherName);
   sNamePH.replace("[", "");
   sNamePH.replace("]", "");
   sNamePH.replace("\"", "");

   switch (QxSqlDatabase::getSingleton()->getSqlPlaceHolderStyle())
   {
      case QxSqlDatabase::ph_style_question_mark:  sResult = "?";                                                 break;
      case QxSqlDatabase::ph_style_2_point_name:   sResult = ":" + sNamePH + sAppend;     break;
      case QxSqlDatabase::ph_style_at_name:        sResult = "@" + sNamePH + sAppend;     break;
      default:                                     sResult = ":" + sNamePH + sAppend;     break;
   }

   return sResult;
}

If it works, I will put this patch for the next version of QxOrm library (released before end of january...).