Can't update when using sql server with odbc

[magicu]

My class is:

#pragma once

#include "MyType.h"

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

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

QX_REGISTER_PRIMARY_KEY(User, int)
QX_REGISTER_HPP_CPPSERVER(User, qx::trait::no_base_class_defined, 0)



#include "StdAfx.h"
#include "User.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::is_frozen, "is_frozen");
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");

}
}


My code is:

UserPtr user = qx::dao::single_or_default<User>(qx_query().where("user_name").isEqualTo(QString::fromStdWString(userName)));
user->valid_code = QString::fromStdWString(validCode);
daoError = qx::dao::update(user); // Error



Error info:

[QxOrm] qx::QxSqlDatabase : create new database connection in thread '4552' with
key '{aaba7a93-03a5-449d-83f2-050a8e094270}'
[QxOrm] sql query (1531 ms) : 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].is_frozen AS Use
r_is_frozen_0, [User].unbind_date AS User_unbind_date_0, [User].terminate_date A
S User_terminate_date_0, [User].create_date AS User_create_date_0, [User].heart_
date AS User_heart_date_0 FROM [User] WHERE user_name = :user_name_1_0
[QxOrm] sql query (0 ms) : SELECT COUNT(*) FROM [User] WHERE valid_code = :valid
_code_1_0 AND is_frozen = :is_frozen_3_0
QODBCResult::exec: Unable to execute statement: "[Microsoft][ODBC SQL Server Dri
ver][SQL Server]Cannot update identity column 'id'. [Microsoft][ODBC SQL Server
Driver][SQL Server]Statement(s) could not be prepared."
[QxOrm] execute sql query failed : UPDATE [User] SET id = :id, user_name = :user
_name, password = :password, valid_code = :valid_code, plugin_name = :plugin_nam
e, is_frozen = :is_frozen, unbind_date = :unbind_date, terminate_date = :termina
te_date, create_date = :create_date, heart_date = :heart_date WHERE id = :id_bis

QODBC3: Unable to execute statement
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'id
'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prep
ared.
QODBCResult::exec: Unable to execute statement: "[Microsoft][ODBC SQL Server Dri
ver][SQL Server]Cannot update identity column 'id'. [Microsoft][ODBC SQL Server
Driver][SQL Server]Statement(s) could not be prepared."
[QxOrm] execute sql query failed : UPDATE [User] SET id = :id, user_name = :user
_name, password = :password, valid_code = :valid_code, plugin_name = :plugin_nam
e, is_frozen = :is_frozen, unbind_date = :unbind_date, terminate_date = :termina
te_date, create_date = :create_date, heart_date = :heart_date WHERE id = :id_bis

QODBC3: Unable to execute statement
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update identity column 'id
'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prep
ared.



I think id column shouldn't update by QxOrm, how to fix this?

[QxOrm admin]

Hi,

I think id column shouldn't update by QxOrm, how to fix this ?

Yes, you're right !
There is no problem with other databases (MySQL, PostgreSQL, Oracle, SQLite, etc...) but MSSqlServer doesn't support this.

If you search on Google ""Cannot update identity column"", you will find a lot of answers...

So, I see 2 solutions :

1- If you don't want to modify QxOrm library source code, you have to modify your SQL schema (so on MSSqlServer side) to not declare your 'id' column as an identify column (just create an index on this column and I think it will work).

2- You can also modify QxOrm source code to not update the ID column, I think I will add a flag using IxSqlGenerator interface (don't forget to add qx::QxSqlDatabase::getSingleton()->setSqlGenerator(new qx::dao::detail::QxSqlGenerator_MSSQLServer() when you intialize your connection to database), here is a patch for "./QxOrm/inl/QxDao/QxSqlQueryHelper_Update.inl" file :

Code: Select all

/****************************************************************************
**
** http://www.qxorm.com/
** http://sourceforge.net/projects/qxorm/
** Original file by Lionel Marty
**
** This file is part of the QxOrm library
**
** This software is provided 'as-is', without any express or implied
** warranty. In no event will the authors be held liable for any
** damages arising from the use of this software.
**
** GNU Lesser General Public License Usage
** This file must be used under the terms of the GNU Lesser
** General Public License version 2.1 as published by the Free Software
** Foundation and appearing in the file 'license.lgpl.txt' included in the
** packaging of this file.  Please review the following information to
** ensure the GNU Lesser General Public License version 2.1 requirements
** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
**
** If you have questions regarding the use of this file, please contact :
** contact@qxorm.com
**
****************************************************************************/

namespace qx {
namespace dao {
namespace detail {

template <class T>
struct QxSqlQueryHelper_Update
{

   static void sql(QString & sql, qx::IxSqlQueryBuilder & builder)
   {
      BOOST_STATIC_ASSERT(qx::trait::is_qx_registered<T>::value);
      long l1(0), l2(0);
      qx::IxDataMember * p = NULL;
      qx::IxDataMember * pId = builder.getDataId(); qAssert(pId);
      qx::IxSqlRelation * pRelation = NULL;
      qx::QxSqlRelationParams params(0, 0, (& sql), (& builder), NULL, NULL);
      QString table = builder.table();
      sql = "UPDATE " + table + " SET ";
      //sql += pId->getSqlNameEqualToPlaceHolder("", ", ") + ", ";
      while ((p = builder.nextData(l1))) { sql += p->getSqlNameEqualToPlaceHolder("", ", ") + ", "; }
      while ((pRelation = builder.nextRelation(l2))) { params.setIndex(l2); pRelation->lazyUpdate(params); }
      sql = sql.left(sql.count() - 2); // Remove last ", "
      sql += " WHERE " + pId->getSqlNameEqualToPlaceHolder("_bis", " AND ");
   }

   static void resolveInput(T & t, QSqlQuery & query, qx::IxSqlQueryBuilder & builder)
   {
      BOOST_STATIC_ASSERT(qx::trait::is_qx_registered<T>::value);
      long l1(0), l2(0);
      qx::IxDataMember * p = NULL;
      qx::IxDataMember * pId = builder.getDataId(); qAssert(pId);
      qx::IxSqlRelation * pRelation = NULL;
      qx::QxSqlRelationParams params(0, 0, NULL, (& builder), (& query), (& t));
      //pId->setSqlPlaceHolder(query, (& t));
      while ((p = builder.nextData(l1))) { p->setSqlPlaceHolder(query, (& t)); }
      while ((pRelation = builder.nextRelation(l2))) { params.setIndex(l2); pRelation->lazyUpdate_ResolveInput(params); }
      pId->setSqlPlaceHolder(query, (& t), "_bis");
   }

   static void resolveOutput(T & t, QSqlQuery & query, qx::IxSqlQueryBuilder & builder)
   { Q_UNUSED(t); Q_UNUSED(query); Q_UNUSED(builder); }

   static void sql(QString & sql, qx::IxSqlQueryBuilder & builder, const QStringList & columns)
   {
      if ((columns.count() <= 0) || (columns.at(0) == "*")) { QxSqlQueryHelper_Update<T>::sql(sql, builder); return; }
      BOOST_STATIC_ASSERT(qx::trait::is_qx_registered<T>::value);
      qx::IxDataMember * p = NULL;
      qx::IxDataMember * pId = builder.getDataId(); qAssert(pId);
      qx::IxDataMemberX * pDataMemberX = builder.getDataMemberX(); qAssert(pDataMemberX);
      QString table = builder.table();
      sql = "UPDATE " + table + " SET ";
      //sql += pId->getSqlNameEqualToPlaceHolder("", ", ") + ", ";
      for (int i = 0; i < columns.count(); i++)
      { p = pDataMemberX->get_WithDaoStrategy(columns.at(i)); if (p && (p != pId)) { sql += p->getSqlNameEqualToPlaceHolder("", ", ") + ", "; } }
      sql = sql.left(sql.count() - 2); // Remove last ", "
      sql += " WHERE " + pId->getSqlNameEqualToPlaceHolder("_bis", " AND ");
   }

   static void resolveInput(T & t, QSqlQuery & query, qx::IxSqlQueryBuilder & builder, const QStringList & columns)
   {
      if ((columns.count() <= 0) || (columns.at(0) == "*")) { QxSqlQueryHelper_Update<T>::resolveInput(t, query, builder); return; }
      BOOST_STATIC_ASSERT(qx::trait::is_qx_registered<T>::value);
      qx::IxDataMember * p = NULL;
      qx::IxDataMember * pId = builder.getDataId(); qAssert(pId);
      qx::IxDataMemberX * pDataMemberX = builder.getDataMemberX(); qAssert(pDataMemberX);
      //pId->setSqlPlaceHolder(query, (& t));
      for (int i = 0; i < columns.count(); i++)
      { p = pDataMemberX->get_WithDaoStrategy(columns.at(i)); if (p && (p != pId)) { p->setSqlPlaceHolder(query, (& t)); } }
      pId->setSqlPlaceHolder(query, (& t), "_bis");
   }

   static void resolveOutput(T & t, QSqlQuery & query, qx::IxSqlQueryBuilder & builder, const QStringList & columns)
   { if ((columns.count() <= 0) || (columns.at(0) == "*")) { QxSqlQueryHelper_Update<T>::resolveOutput(t, query, builder); return; } }

};

} // namespace detail
} // namespace dao
} // namespace qx

[magicu]

Hi,

I think id column shouldn't update by QxOrm, how to fix this ?

Yes, you're right !
There is no problem with other databases (MySQL, PostgreSQL, Oracle, SQLite, etc...) but MSSqlServer doesn't support this.

If you search on Google ""Cannot update identity column"", you will find a lot of answers...

So, I see 2 solutions :

1- If you don't want to modify QxOrm library source code, you have to modify your SQL schema (so on MSSqlServer side) to not declare your 'id' column as an identify column (just create an index on this column and I think it will work).

2- You can also modify QxOrm source code to not update the ID column, I think I will add a flag using IxSqlGenerator interface (don't forget to add qx::QxSqlDatabase::getSingleton()->setSqlGenerator(new qx::dao::detail::QxSqlGenerator_MSSQLServer() when you intialize your connection to database), here is a patch for "./QxOrm/inl/QxDao/QxSqlQueryHelper_Update.inl" file :

Code: Select all

/****************************************************************************
**
** http://www.qxorm.com/
** http://sourceforge.net/projects/qxorm/
** Original file by Lionel Marty
**
** This file is part of the QxOrm library
**
** This software is provided 'as-is', without any express or implied
** warranty. In no event will the authors be held liable for any
** damages arising from the use of this software.
**
** GNU Lesser General Public License Usage
** This file must be used under the terms of the GNU Lesser
** General Public License version 2.1 as published by the Free Software
** Foundation and appearing in the file 'license.lgpl.txt' included in the
** packaging of this file.  Please review the following information to
** ensure the GNU Lesser General Public License version 2.1 requirements
** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
**
** If you have questions regarding the use of this file, please contact :
** contact@qxorm.com
**
****************************************************************************/

namespace qx {
namespace dao {
namespace detail {

template <class T>
struct QxSqlQueryHelper_Update
{

   static void sql(QString & sql, qx::IxSqlQueryBuilder & builder)
   {
      BOOST_STATIC_ASSERT(qx::trait::is_qx_registered<T>::value);
      long l1(0), l2(0);
      qx::IxDataMember * p = NULL;
      qx::IxDataMember * pId = builder.getDataId(); qAssert(pId);
      qx::IxSqlRelation * pRelation = NULL;
      qx::QxSqlRelationParams params(0, 0, (& sql), (& builder), NULL, NULL);
      QString table = builder.table();
      sql = "UPDATE " + table + " SET ";
      //sql += pId->getSqlNameEqualToPlaceHolder("", ", ") + ", ";
      while ((p = builder.nextData(l1))) { sql += p->getSqlNameEqualToPlaceHolder("", ", ") + ", "; }
      while ((pRelation = builder.nextRelation(l2))) { params.setIndex(l2); pRelation->lazyUpdate(params); }
      sql = sql.left(sql.count() - 2); // Remove last ", "
      sql += " WHERE " + pId->getSqlNameEqualToPlaceHolder("_bis", " AND ");
   }

   static void resolveInput(T & t, QSqlQuery & query, qx::IxSqlQueryBuilder & builder)
   {
      BOOST_STATIC_ASSERT(qx::trait::is_qx_registered<T>::value);
      long l1(0), l2(0);
      qx::IxDataMember * p = NULL;
      qx::IxDataMember * pId = builder.getDataId(); qAssert(pId);
      qx::IxSqlRelation * pRelation = NULL;
      qx::QxSqlRelationParams params(0, 0, NULL, (& builder), (& query), (& t));
      //pId->setSqlPlaceHolder(query, (& t));
      while ((p = builder.nextData(l1))) { p->setSqlPlaceHolder(query, (& t)); }
      while ((pRelation = builder.nextRelation(l2))) { params.setIndex(l2); pRelation->lazyUpdate_ResolveInput(params); }
      pId->setSqlPlaceHolder(query, (& t), "_bis");
   }

   static void resolveOutput(T & t, QSqlQuery & query, qx::IxSqlQueryBuilder & builder)
   { Q_UNUSED(t); Q_UNUSED(query); Q_UNUSED(builder); }

   static void sql(QString & sql, qx::IxSqlQueryBuilder & builder, const QStringList & columns)
   {
      if ((columns.count() <= 0) || (columns.at(0) == "*")) { QxSqlQueryHelper_Update<T>::sql(sql, builder); return; }
      BOOST_STATIC_ASSERT(qx::trait::is_qx_registered<T>::value);
      qx::IxDataMember * p = NULL;
      qx::IxDataMember * pId = builder.getDataId(); qAssert(pId);
      qx::IxDataMemberX * pDataMemberX = builder.getDataMemberX(); qAssert(pDataMemberX);
      QString table = builder.table();
      sql = "UPDATE " + table + " SET ";
      //sql += pId->getSqlNameEqualToPlaceHolder("", ", ") + ", ";
      for (int i = 0; i < columns.count(); i++)
      { p = pDataMemberX->get_WithDaoStrategy(columns.at(i)); if (p && (p != pId)) { sql += p->getSqlNameEqualToPlaceHolder("", ", ") + ", "; } }
      sql = sql.left(sql.count() - 2); // Remove last ", "
      sql += " WHERE " + pId->getSqlNameEqualToPlaceHolder("_bis", " AND ");
   }

   static void resolveInput(T & t, QSqlQuery & query, qx::IxSqlQueryBuilder & builder, const QStringList & columns)
   {
      if ((columns.count() <= 0) || (columns.at(0) == "*")) { QxSqlQueryHelper_Update<T>::resolveInput(t, query, builder); return; }
      BOOST_STATIC_ASSERT(qx::trait::is_qx_registered<T>::value);
      qx::IxDataMember * p = NULL;
      qx::IxDataMember * pId = builder.getDataId(); qAssert(pId);
      qx::IxDataMemberX * pDataMemberX = builder.getDataMemberX(); qAssert(pDataMemberX);
      //pId->setSqlPlaceHolder(query, (& t));
      for (int i = 0; i < columns.count(); i++)
      { p = pDataMemberX->get_WithDaoStrategy(columns.at(i)); if (p && (p != pId)) { p->setSqlPlaceHolder(query, (& t)); } }
      pId->setSqlPlaceHolder(query, (& t), "_bis");
   }

   static void resolveOutput(T & t, QSqlQuery & query, qx::IxSqlQueryBuilder & builder, const QStringList & columns)
   { if ((columns.count() <= 0) || (columns.at(0) == "*")) { QxSqlQueryHelper_Update<T>::resolveOutput(t, query, builder); return; } }

};

} // namespace detail
} // namespace dao
} // namespace qx

For your solutions:

1:
How to implement autoincrement id if I don't use identify column. If I must give the id value on ervery insert action?


2:
qx::QxSqlDatabase::getSingleton()->setSqlGenerator(new qx::dao::detail::QxSqlGenerator_MSSQLServer());

I got an error:
error C2664: 'qx::QxSqlDatabase::setSqlGenerator' : cannot convert parameter 1 from 'qx::dao::detail::QxSqlGenerator_MSSQLServer *' to 'qx::dao::detail::IxSqlGenerator_ptr'

[QxOrm admin]

2:
qx::QxSqlDatabase::getSingleton()->setSqlGenerator(new qx::dao::detail::QxSqlGenerator_MSSQLServer());

I got an error:
error C2664: 'qx::QxSqlDatabase::setSqlGenerator' : cannot convert parameter 1 from 'qx::dao::detail::QxSqlGenerator_MSSQLServer *' to 'qx::dao::detail::IxSqlGenerator_ptr'

Try this :

Code: Select all

qx::dao::detail::IxSqlGenerator_ptr sqlGen(new qx::dao::detail::QxSqlGenerator_MSSQLServer());
qx::QxSqlDatabase::getSingleton()->setSqlGenerator(sqlGen);

[magicu]

2:
qx::QxSqlDatabase::getSingleton()->setSqlGenerator(new qx::dao::detail::QxSqlGenerator_MSSQLServer());

I got an error:
error C2664: 'qx::QxSqlDatabase::setSqlGenerator' : cannot convert parameter 1 from 'qx::dao::detail::QxSqlGenerator_MSSQLServer *' to 'qx::dao::detail::IxSqlGenerator_ptr'

Try this :

Code: Select all

qx::dao::detail::IxSqlGenerator_ptr sqlGen(new qx::dao::detail::QxSqlGenerator_MSSQLServer());
qx::QxSqlDatabase::getSingleton()->setSqlGenerator(sqlGen);

If I don't want modify QxOrm, how to implement autoincrement id? Thanks.

[QxOrm admin]

If I don't want modify QxOrm, how to implement autoincrement id? Thanks.

Sorry, I don't know MSSqlServer database, so I can't help you on this point.
I will provide a BETA version of QxOrm library 1.2.5 where ID is not inserted into the SQL when UPDATE query is built, so it will work for you with MSSqlServer.
As soon as this BETA version is ready, I will give you a link into this topic...

[magicu]

If I don't want modify QxOrm, how to implement autoincrement id? Thanks.

Sorry, I don't know MSSqlServer database, so I can't help you on this point.
I will provide a BETA version of QxOrm library 1.2.5 where ID is not inserted into the SQL when UPDATE query is built, so it will work for you with MSSqlServer.
As soon as this BETA version is ready, I will give you a link into this topic...

Well, thanks.

[QxOrm admin]

Here is a BETA version of QxOrm 1.2.5 to download : http://www.qxorm.com/version/QxOrm_1.2.5_BETA_02.zip
There is an option to add or not auto-increment ID to update SQL queries, and the default behaviour is the same as previous QxOrm versions.
If you specify a MSSQLServer database SQL generator, then update SQL queries should work fine.
So you just have to initialize your database parameters like this :

Code: Select all

qx::dao::detail::IxSqlGenerator_ptr pSqlGen(new qx::dao::detail::QxSqlGenerator_MSSQLServer());
qx::QxSqlDatabase::getSingleton()->setSqlGenerator(pSqlGen);

Please, let me know if it's working fine for you !