What is the equivalent code lines to interbase jdbc url?

xeoshow

Hello,

I am new in the forum, and now met an issue when using the database connection for interbase regarding chracter set and encoding in qt.

Previously I have a java project which also connects to interbase database via below code:

        // JDBC driver name and database URL
	public static String jdbcDriver = "org.firebirdsql.jdbc.FBDriver";
	public static String jdbcUrl = "jdbc:firebirdsql://localhost/c:/FOODBEV.GDB?charSet=GBK&encoding=NONE";
 
                        Class.forName(jdbcDriver);
			if (conn == null) {
				conn = DriverManager.getConnection(jdbcUrl, dbUser, dbPassword);
			}
 
			JSONObject updateObject = new JSONObject();
			updateObject.put("cmd", "updatecloudmenu");
 
			JSONArray categoryObjectArray = new JSONArray();
			updateObject.put("data", categoryObjectArray);
 
 
			num = 0;
			if (stmt == null) {
				stmt = conn.createStatement();
			}
			sql = "SELECT ID, CODE, NAME, CATEGORY, PRICE, PRICE2, PRICE3, UNIT, UNIT2, UNIT3, OPERATION FROM XC_ITEM";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				num++;
                        ......
                        ......

It can get the rows successfully when having Chinese characters.

And now I want to achieve the same in qt with this same database, my qt code (source code file is UTF-8, and using QTCreator 5.5.1 mingw version) is as below:

    QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE", "ib");
    db.setHostName("127.0.0.1");
    db.setDatabaseName("C:/FOODBEV.GDB");//"C:/TEST.GDB"
    db.setUserName("SYSDBA");
    db.setPassword("masterkey");
    db.setConnectOptions("ISC_DPB_LC_CTYPE=gb2312");        // 对中文的支持
    bool bopen = false;
    bopen = db.open();
    LOG(TRACE) << "bopen=" << bopen;
 
    QSqlTableModel tableModel(0, db);
    tableModel.setTable("XC_ITEM");
    tableModel.select();
    for (int i = 0; i < tableModel.rowCount(); i++) {
        LOG(TRACE) << "tableModel.row=" << i << ", item=" << tableModel.record(i).value("ITEM").toString() << ", des=" << tableModel.record(i).value("DES").toString();
    }
......
......

When there is no Chinese characters, it works well, but if there is any Chinese characters, then just can not return the rows with Chinese characters.

Anything I did wrong? How to resolve this problem?

Any help is highly appreciated!

Another strange thing is: I found using above code, I can successfully insert into tables with rows having Chinese characters without any garbage characters, but just could not query with the correct result with regard to those rows having Chinese characters.

m.sue

Hi,
did you try:
db.setConnectOptions(QString::fromUtf8("your utf8-string"));
-Michael.

VRonin

could you try using QSqlQuery directly instead of QSqlTableModel?

xeoshow

Thanks for kind help!
I had below code line in my program:

db.setConnectOptions("ISC_DPB_LC_CTYPE=gb2312");

Do you mean I should modify above as below? I just tried and the same issue. Or is it misunderstood?

db.setConnectOptions(QString::fromUtf8("ISC_DPB_LC_CTYPE=gb2312"));

@m.sue said in What is the equivalent code lines to interbase jdbc url?:

Hi,
did you try:
db.setConnectOptions(QString::fromUtf8("your utf8-string"));
-Michael.

xeoshow

Thanks for help!

I have tried below code via QSqlQuery directly, and got the same problem, really strange problem. :-(

    QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE", "ib");
    db.setHostName("127.0.0.1");
    db.setDatabaseName("C:/FOODBEV.GDB");//"C:/TEST.GDB"
    db.setUserName("SYSDBA");
    db.setPassword("masterkey");
//    db.setConnectOptions("ISC_DPB_LC_CTYPE=GB2312");        // 对中文的支持
    db.setConnectOptions(QString::fromUtf8("ISC_DPB_LC_CTYPE=gb2312"));
    bool bopen = false;
    bopen = db.open();
    LOG(TRACE) << "bopen=" << bopen;


    QString sql = "";
    QSqlQuery sqlQuery(db);

    sql = QString("select CODE, NAME from XC_ITEM");
    bool result = sqlQuery.exec(sql);
    int numRows = 0;
    if (db.driver()->hasFeature(QSqlDriver::QuerySize)) {
        numRows = sqlQuery.size();
        LOG(TRACE) << "db.driver()->hasFeature(QSqlDriver::QuerySize), numRows=" << numRows;
    } else {
        // this can be very slow
        sqlQuery.last();
        numRows = sqlQuery.at() + 1;
        LOG(TRACE) << "db.driver()->NOT.hasFeature(QSqlDriver::QuerySize), numRows=" << numRows;
    }
    LOG(TRACE) << "sqlQuery.result=" << result;
    while (sqlQuery.next()) {
        LOG(TRACE) << "sqlQuery.next";
    }

@VRonin said in What is the equivalent code lines to interbase jdbc url?:

could you try using QSqlQuery directly instead of QSqlTableModel?

xeoshow

My previous jave project, it got 2 parameters in the jdbc url and works with no any problem:

charSet=GBK
encoding=NONE

But in the QT, there seems only ISC_DPB_LC_CTYPE parameter for Character set, how about the encoding in QT?

xeoshow

Any one could kindly help? Thanks so much in advance!

m.sue

@xeoshow said in What is the equivalent code lines to interbase jdbc url?:

Do you mean I should modify above as below? I just tried and the same issue. Or is it misunderstood?
db.setConnectOptions(QString::fromUtf8("ISC_DPB_LC_CTYPE=gb2312"));

I thought you used some "real" UTF-8 string in the call to db.setConnectOptions(QString::fromUtf8("...")). Sorry, with an ascii string argument "ISC_DPB_LC_CTYPE=gb2312" it does not make a difference anyway.

Beware, though: Writing this db.setConnectOptions("some string");is the same as this db.setConnectOptions(QString::fromLatin1("some string"));. You see: this does not make sense with "real" UTF-8 string arguments.
-Michael.

xeoshow

Michael,

Thanks for kind help!

Just still confused where the problem is ...

Does the charset and encoding the same thing in qt for interbase?

VRonin

@xeoshow said in What is the equivalent code lines to interbase jdbc url?:

My previous jave project, it got 2 parameters in the jdbc url and works with no any problem:
charSet=GBK
encoding=NONE
But in the QT, there seems only ISC_DPB_LC_CTYPE parameter for Character set, how about the encoding in QT?

Can you specify them in the connection string instead of just the name in setDatabaseName()? https://www.connectionstrings.com/interbase/

xeoshow

I checked the link you provided, but what is the value for provider in my case? I just used the default interbase driver in QT.

Thanks!

VRonin

I think it's either sibprovider or SIBPROvider.2

xeoshow

I just tried to modify code as below:

    QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE", "ib");
    db.setHostName("127.0.0.1");
    QString connectionStr = "provider=SIBPROvider.2;location=localhost:;data source=c:\FOODBEV.GDB;user id=SYSDBA;Password=masterkey;character set=GB2312;";
//    db.setDatabaseName("C:/FOODBEV.GDB");//"C:/TEST.GDB"
    db.setDatabaseName(connectionStr);//"C:/TEST.GDB"
    db.setUserName("SYSDBA");
    db.setPassword("masterkey");
    db.setConnectOptions("ISC_DPB_LC_CTYPE=GB2312");        // 对中文的支持
    bool bopen = false;
    bopen = db.open();
    LOG(TRACE) << "bopen=" << bopen;

And got below result output:

...
bopen=0
QSqlQuery::exec: database not open
...

Using sibprovider or SIBPROvider.2 got the same result, which seems got open db failed issue. Or is there anything I did wrong regarding connectionStr ?

Thanks for kind help!

xeoshow

Looks like the connection string from https://www.connectionstrings.com/ is for ODBC driver connection? Not sure if the QT default Interbase driver is ODBC type?

Thanks again for kind help!

jsulm

@xeoshow Use http://doc.qt.io/qt-5/qsqldatabase.html#lastError to print out the error after open().
And putting DB file in c:\ like c:\FOODBEV.GDB isn't a good idea as non-administrator users normally do not have write access there.

xeoshow

@jsulm Thanks v much for help!
I moved the gdb database file to D:, and also used the lastError() method, now code as below:

    QSqlDatabase db = QSqlDatabase::addDatabase("QIBASE", "ib");
    db.setHostName("127.0.0.1");
    QString connectionStr = "provider=SIBPROvider.2;data source=D:/FOODBEV.GDB;character set=GB2312;";
    db.setDatabaseName(connectionStr);//"C:/TEST.GDB"
    db.setUserName("SYSDBA");
    db.setPassword("masterkey");
    db.setConnectOptions("ISC_DPB_LC_CTYPE=GB2312;");        // 对中文的支持
    bool bopen = false;
    bopen = db.open();
    LOG(TRACE) << "bopen=" << bopen << ", lastError=" << db.lastError().text();

And still the same issue, got below LOG info, seems strange ...

bopen=0, lastError=Unable to complete network request to host "provider=SIBPROvider.2;data source=D". - Failed to locate host machine. - The specified name was not found in the hosts file or Domain Name Services. Error opening database
QSqlQuery::exec: database not open

jsulm

@xeoshow I never used Interbase but are you sure that Interbase uses a DB file like SQLite?
The error message tells you that it tries to connect to a computer over network.
You probably need to connect to an Interbase SERVER not file.

jsulm

@xeoshow Connection strings used here http://docwiki.embarcadero.com/InterBase/XE7/en/Connecting_to_InterBase_from_Visual_Studio look different to what you do.

xeoshow

BTW, using "D:/FOODBEV.GDB" or "D:\FOODBEV.GDB" got the same result ...

xeoshow

@jsulm Actually used my original code at the first post, I can insert data into the interbase, just can not select data with Chinese characters correctly...
The connection string is just got from https://www.connectionstrings.com/ , I will now try the one you provided.