How to create postgresql subscription with qsqlquery?

[August Wiener]

I am trying to create postgres subscription using qsqlquery, but getting :

"LINE 1:EXCECUTE ('zumbeispiel_slot', TRUE)

42601 QPSQL: Unable to create query"
here is the code

local_query.prepare("CREATE SUBSCRIPTION :sub_name"
                    "CONNECTION 'postgresql://postgres:root@64.196.144.30:5432/Realdent atlas'"
                    "PUBLICATION public_realdent_db"
                    "WITH"
                    "slot_name = :slot_name,"
                    "create_slot = :create_slot,"
                    "synchronous_commit = on");
local_query.bindValue(":sub_name", subscription);
local_query.bindValue(":create_slot", create_slot);
local_query.bindValue(":slot_name", slot_name);
slots_connected = local_query.exec();


if (!slots_connected)
{
    emit database_failed(QUERY_ERROR_HEADER, QUERY_ERROR_PRETEXT + 
                         local_query.lastError().text());

    return slots_connected;
}

What I am doing wrong?
Connection string seems to be correct, many other queries to this database works fine.

[JonB]

@August-Wiener
I'm not a practicing C++-er. But isn't your multiline string literal going to join without any whitespace character? Isn't

            "CREATE SUBSCRIPTION :sub_name"
            "CONNECTION 'postgresql://postgres:root@64.196.144.30:5432/Realdent atlas'"
            "..."

going to generate:

"CREATE SUBSCRIPTION :sub_nameCONNECTION 'postgresql://postgres:root@64.196.144.30:5432/Realdent atlas'..."

i.e. you need a space at the end/start of each of your literals (inside the quotes), so at present your query won't parse??

[August Wiener]

@JonB
Yeah, it is even funny how I missed that. Now my code looks like

local_query.prepare("CREATE SUBSCRIPTION :sub_name "
                        "CONNECTION 'postgresql://postgres:root@64.196.144.30:5432/Realdent atlas'"
                        "PUBLICATION public_dealmed_db "
                        "WITH "
                        "(slot_name = :slot_name, "
                        "create_slot = :create_slot, "
                        "synchronous_commit = on)");
    local_query.bindValue(":sub_name", subscription);
    local_query.bindValue(":create_slot", create_slot);
    local_query.bindValue(":slot_name", slot_name);
    slots_connected = local_query.exec();

Unfortunately, I keep getting just the same error :(

[SGaist]

Hi and welcome to devnet,

Did you check the content of QSqlQuery::executedQuery to see if there's anything wrong in there ?

[JonB]

@August-Wiener
If it's as you've pasted it, you still have

                        "CONNECTION 'postgresql://postgres:root@64.196.144.30:5432/Realdent atlas'"
                        "PUBLICATION public_dealmed_db "

->

                        "CONNECTION 'postgresql://postgres:root@64.196.144.30:5432/Realdent atlas'PUBLICATION public_dealmed_db "

?

[August Wiener]

@SGaist
It is just the same but with '?' symbols instead of parameter values.

@JonB
No, things not that bad. I failed last space when editing, in code it is with white spaces at the ends of every row except th last one.

[SGaist]

I'm not sure whether bindValue works with that kind of query.

Does it also fail if you use QString to build the query using arg method ?

[August Wiener]

This post is deleted!