Hello,
The answer to your queries are:
1. But how do you migrate your database from MySql to Postgres ?
We create and sql dump using mysql dump utility and then convert it to postgres using a script.
Then we load that dump into postgres database.
2. Why did you create them exactly like MySql ?
The table is used by multiple application that is why we need to keep the table name and column name as same.
Plus we want to keep the old data in the database.
I tried the patch provided by you still i am facing the same issue and getting the error below:
Database error number '0' : QPSQL: Unable to prepare statement
QPSQL: Unable to create query
ERROR: missing FROM-clause entry for table "AccessLevel_1"
LINE 1: ...r" LEFT OUTER JOIN "AccessLevel" AccessLevel_1 ON "AccessLev...
Apart from that i can also see that the column name after FROM clause is not getting quoted.
Below is the query created by QxOrm:
SELECT "Usr"."User_Id" AS Usr_User_Id_0, "Usr"."Username" AS Usr_Username_0, "Usr"."Password" AS Usr_Password_0, "Usr"."PasswordExpiryTime" AS Usr_PasswordExpiryTime_0, "Usr"."Description" AS Usr_Description_0, "Usr"."FirstName" AS Usr_FirstName_0, "Usr"."LastName" AS Usr_LastName_0, "Usr"."PhoneNumber" AS Usr_PhoneNumber_0, "Usr"."EmailAddress" AS Usr_EmailAddress_0, "Usr"."CompanyName" AS Usr_CompanyName_0, "Usr"."CompanyAddress" AS Usr_CompanyAddress_0, "Usr"."CreatedTime" AS Usr_CreatedTime_0, "Usr"."ModifiedTime" AS Usr_ModifiedTime_0, "Usr"."IsDeleted" AS Usr_IsDeleted_0, "Usr"."UserFailedLogonAttempts" AS Usr_UserFailedLogonAttempts_0, "Usr"."UserLastLogonTime" AS Usr_UserLastLogonTime_0, "Usr"."LockStatus" AS Usr_LockStatus_0, "Usr"."AutoLockTime" AS Usr_AutoLockTime_0, "Usr"."Language" AS Usr_Language_0, "Usr"."CreatedBy" AS Usr_CreatedBy_0, "Usr"."AccessLevelId" AS Usr_AccessLevelId_0, "AccessLevel_1"."AccessLevelId" AS AccessLevel_1_AccessLevelId_0, "AccessLevel_1"."LevelNumber" AS AccessLevel_1_LevelNumber_0, "AccessLevel_1"."Description" AS AccessLevel_1_Description_0 FROM "Usr" LEFT OUTER JOIN "AccessLevel" AccessLevel_1 ON "AccessLevel_1"."AccessLevelId" = "Usr"."AccessLevelId" WHERE IsDeleted = 0 AND UPPER(Usr.Username) = :name
I tried going through the code. I am not sure if it is the correct flow but i checked the QxSqlQuery.cpp file in that i can see that the column name is not getting quoted:
- Code: Select all
QxSqlQuery & QxSqlQuery::where(const QString & column)
{
return addSqlExpression(column, qx::dao::detail::QxSqlExpression::_where);
}
QxSqlQuery & QxSqlQuery::addSqlExpression(const QString & column, qx::dao::detail::QxSqlExpression::type type)
{
qx::dao::detail::QxSqlExpression_ptr p;
p = std::make_shared<qx::dao::detail::QxSqlExpression>(m_iSqlElementIndex++, type);
m_lstSqlElement.append(p);
m_pSqlElementTemp = std::make_shared<qx::dao::detail::QxSqlElementTemp>();
m_pSqlElementTemp->setColumn(column);
return (* this);
}
From the above line of code we can see that "QxSqlQuery::addSqlExpression" adds column using the class "qx::dao::detail::QxSqlElementTemp" which doesn't check for delimiters for column.
Not sure if I am checking the correct code.
I will also attach the changes I made as patch file.