QODBC for Postgresql on Windows

[AlbertoN]

Hi all,

I tried to run an app with QPSQL, but I found that it doesn't support UNICODE:

qt1.png

and from psql shell:

postgresql2.png

I inserted first row with psql shell therefore you can see unicode is supported inside postgresql.
As postgresql docs says (link):

"Programs using libpq directly need to be aware of this, but not much else does."

So I'm trying to run a test app with QODBC, even if qt docs says that it's better to use specific driver (QPSQL) link:

Note: You should use native drivers in preference to the ODBC driver where they are available. ODBC support can be used as a fallback for compliant databases if no native drivers are available.

I installed driver manager too (following this thread instructions):
posgresql1.png

then compiled and installed qt odbc wrapper:
qt0.jpg

this is my code:

//main.cpp:
#include <QCoreApplication>
#include <QSqlRecord>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDriver>
#include <QDebug>
 
class Database 
{
	public:
		explicit Database();
		~Database();
		bool connection();
	private:
		QString driverName;
		QSqlDatabase db;
};
 
Database::~Database()
{
    db.close();
}
 
Database::Database() :
	db(QSqlDatabase::addDatabase("QODBC"))
        //db(QSqlDatabase::addDatabase("QODBC3"))//tried even with this
{
	driverName = QString("QODBC");
	db.setDatabaseName("Driver={PostgreSQL};Server=localhost;Port=5432;Database=prova;Uid=postgres;Pwd=*******;");
        //db.setConnectOptions("SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3");//tried even with this
}
 
bool Database::connection()
{
    if (!db.open()) {
		qDebug() << QString("Unable to establish a database connection.\n"
                     "This example needs %1 support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.\n\n"
                     "Click Cancel to exit.").arg(driverName);
        return false;
    }
    return true;
}
 
bool insertTest()
{
	QSqlQuery q;
	q.prepare("INSERT INTO p(d) VALUES(:d)");
	q.bindValue(":d",QString("Questa è un altra prova"));
	return q.exec();
}
 
int main(int argc, char **argv)
{
    QCoreApplication app(argc,argv);
    const QScopedPointer<Database> database(new Database());
    database->connection();
    if(insertTest())
    {
        qDebug() << "query executed successfully";	
    }else{
        qDebug() << "insert query error";
    }
    QSqlQuery q;
    q.exec("SELECT * FROM p");
    while(q.next())
    {
       QSqlRecord r = q.record();
       qDebug() << "id=" << r.value("id").toInt();
       qDebug() << "d=" << r.value("d").toString() << '\n';
    }
    return app.exec(); 
}

//main.cpp:
#include <QCoreApplication>
#include <QSqlRecord>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlDriver>
#include <QDebug>

class Database 
{
	public:
		explicit Database();
		~Database();
		bool connection();
	private:
		QString driverName;
		QSqlDatabase db;
};

Database::~Database()
{
    db.close();
}

Database::Database() :
	db(QSqlDatabase::addDatabase("QODBC"))
        //db(QSqlDatabase::addDatabase("QODBC3"))//tried even with this
{
	driverName = QString("QODBC");
	db.setDatabaseName("Driver={PostgreSQL};Server=localhost;Port=5432;Database=prova;Uid=postgres;Pwd=*******;");
        //db.setConnectOptions("SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3");//tried even with this
}

bool Database::connection()
{
    if (!db.open()) {
		qDebug() << QString("Unable to establish a database connection.\n"
                     "This example needs %1 support. Please read "
                     "the Qt SQL driver documentation for information how "
                     "to build it.\n\n"
                     "Click Cancel to exit.").arg(driverName);
        return false;
    }
    return true;
}

bool insertTest()
{
	QSqlQuery q;
	q.prepare("INSERT INTO p(d) VALUES(:d)");
	q.bindValue(":d",QString("Questa è un altra prova"));
	return q.exec();
}

int main(int argc, char **argv)
{
    QCoreApplication app(argc,argv);
    const QScopedPointer<Database> database(new Database());
    database->connection();
    if(insertTest())
    {
        qDebug() << "query executed successfully";	
    }else{
        qDebug() << "insert query error";
    }
    QSqlQuery q;
    q.exec("SELECT * FROM p");
    while(q.next())
    {
       QSqlRecord r = q.record();
       qDebug() << "id=" << r.value("id").toInt();
       qDebug() << "d=" << r.value("d").toString() << '\n';
    }
    return app.exec(); 
}

To copy to clipboard, switch view to plain text mode 

and this is my .pro file:

QT         += core gui sql widgets
TARGET      = ScopedPointer
CONFIG     += console
TEMPLATE    = app
 
sql-plugins += odbc
 
SOURCES    += main3.cpp 
 
OBJECTS_DIR = build/o
MOC_DIR     = build/moc
UI_DIR      = build/ui
RCC_DIR     = build/rcc
DESTDIR     = bin

QT         += core gui sql widgets
TARGET      = ScopedPointer
CONFIG     += console
TEMPLATE    = app

sql-plugins += odbc

SOURCES    += main3.cpp 

OBJECTS_DIR = build/o
MOC_DIR     = build/moc
UI_DIR      = build/ui
RCC_DIR     = build/rcc
DESTDIR     = bin

To copy to clipboard, switch view to plain text mode 

I added sql-plugins directive following this thread

Anyway i get this error message:

"QODBCResult::exec: No statement handle available" Error: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

"QODBCResult::exec: No statement handle available" Error: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

To copy to clipboard, switch view to plain text mode 

as you can see here:
error.png

I'm using Qt 5.2.0. If you need any other information please ask.
Any help would be appreciated, thanks!
Alberto

[Lesiok]

Hi all,
I tried to run an app with QPSQL, but I found that it doesn't support UNICODE:

Nonsense.
The problem is that qDebug converts Unicode to the local code page. Often, Windows uses a different code page for character session and another code page for GUI session. For applications always be notified the GUI code page.
PS.
psql warns You about this, look at second picture.

[AlbertoN]

Nonsense.
The problem is that qDebug converts Unicode to the local code page. Often, Windows uses a different code page for character session and another code page for GUI session. For applications always be notified the GUI code page.
PS.
psql warns You about this, look at second picture.

yes, you're right. After a whole day spent fighting against Windows I found how to fix that.

I'm going to write it down here for other users with same issue:

1. change windows' console codepage:
you can set it up with prompt command: "chcp 1252" but it works just for current session. To make it default codepage, you have to open regedit, locate AutoRun in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor\ subdirectory. If it doesn't exists, as in my case, you'll create an entry as "string" and put "chcp 1252" as value in it.

2. after previous change you shouldn't be able to see any accents in console due to the fact that windows default font is a "Raster Font". To change it, you have to open a psql shell-> right click -> properties -> Fonts and switch to "Lucida Console". Do the same on a common console (I don't know why. They should be the same... maybe my mistake i'm a bit tired).

3. after that you have to set up postgresql shell config to WIN1252 encoding. To do that open as admin your runpsql.bat file. In my case there's a psql shell link on desktop -> right click->Shortcut->"open file location" button. It opens a path like this: C:\Program Files (x86)\PostgreSQL\9.3\scripts\runpsql.bat. And change it as follows (to save changes you have to open it as Admin):

[....]
for /f "delims=" %%a in ('chcp ^|find /c "932"') do @ SET CLIENTENCODING_JP=%%a
SET PGCLIENTENCODING=WIN1252
if "%CLIENTENCODING_JP%"=="1" SET /P PGCLIENTENCODING="Client Encoding [%PGCLIENTENCODING%]: "
[...]

[....]
for /f "delims=" %%a in ('chcp ^|find /c "932"') do @ SET CLIENTENCODING_JP=%%a
SET PGCLIENTENCODING=WIN1252
if "%CLIENTENCODING_JP%"=="1" SET /P PGCLIENTENCODING="Client Encoding [%PGCLIENTENCODING%]: "
[...]

To copy to clipboard, switch view to plain text mode 

4. create a db with right encoding inside psql shell: create database test with encoding 'UTF8';

That's all.

I hope this could be useful to someone else.
Thanks Lesiok for your support.
Alberto

P.S.: Anyway I can't run that code with QODBC driver and I can't understand why...