Hi,
first of all the short answer: no.
Tntdb do not replace the values in the sql at all. Postgresql does that.
There is a prepared statement api where placeholders are used. In
postgresql the placeholders are $1, $2, and so on. And that is what you
can see in the logs. There are separate statements, which send the
values to postgresql. Therefore it makes sense to reuse prepared
statements since they are then parsed just once by postgresql and can be
executed multiple times with different parameters.
I looked at your query and I must confess, that I am not familar with
upsert in postgresql. But I took the statement and replaced the
placeholders with postgresql syntax and executing that with psql gives
me the same error. Here is my sql:
prepare foo as
with newValues (id_tes, tes_textid, number) as
( values
($1, $2, $3),
($4, $5, $6)
),
upsert as
(
update tbl_test lm
set tes_textid = nv.tes_textid
from newValues nv
where lm.id_tes = nv.id_tes returning lm.id_tes
) INSERT INTO tbl_test (tes_textid, number)
select tes_textid, number
from newValues where not exists (select id_tes from upsert
up where up.id_tes = newValues.id_tes );
So it really does not look like a tntdb problem.
As a side note I have some comments though:
* Do not end the sql statement with a semicolon in tntdb. The semicolon
is just needed by psql but do not belong to the actual statement.
Postgresql do not complain that but it just do not belong there.
* Instead of setInt, setString et al you should use the overloaded set.
And for syntactic sugar you can chain the calls:
query.set("a", 0)
.set("d", 0)
.set("b", "es")
.set("e", "en")
.set("c", 1)
.set("f", 2);
* if you use localhost, you should not specify the host in the dburl.
Postgresql uses named pipes and can even use your unix login for
authentication. You don't even need to specify the username and
password. By default postgresql do not even enable tcp/ip. And it is not
needed when using named pipes.
Tommi
Am 17.12.2014 um 00:11 schrieb Jouven:
> Greetings,
>
> Is there a way to output a prepared statement with the values already
> replaced? (as in just before being sent to the DB)
> Setting the logging to cxxtools::Logger::DEBUG prints the query but the
> parameters change to something like "$1", "$2"...
>
> Anyway I think found a bug, or a lack of a feature, I'm trying to launch
> an "upsert" using a CTE (common table expression) against a postgres
> 9.4rc1 database.
> If I write the values on the string the query works but If I use
> substitution I keep getting errors, like this one:
> Postgresql-Error 42883: operator does not exist: integer = text at 209
> in PQprepare
> Removing substitution step by step and using direct values moves the
> error to the next field that still uses substitution, until there is
> none left and it works.
>
> code: http://pastebin.com/xVxcqEHU (includes the sql for the table
> creation and the same query to use directly on a client like psql)
>
> I'm using the latest tntdb code pulled from github.
>
> Regards,
> Joan
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
> _______________________________________________
> Tntnet-general mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/tntnet-general
------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Tntnet-general mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/tntnet-general
