Home » U++ Library support » U++ SQL » Postgres and CurrVal
| Postgres and CurrVal [message #23025] |
Tue, 08 September 2009 16:18  |
Zbych
Messages: 325
Registered: July 2009
|
Senior Member |
|
|
Hi,
CurrVal doesn't seem to work correctly with Postgres right now.
When I use it like this:
sql & Insert(TABLE1)(COLUMN1, "test")(COLUMN2, CurrVal(TABLE2_COLUMN1_SEQ));
Sql string looks like this:
insert into TABLE1 (COLUMN1, COLUMN2) values ('test',currval('TABLE2_COLUMN1_SEQ')TABLE2_COLUMN1_SEQ.CURRVAL))
It looks like SqlCase is not "compiled". Any ideas how to fix it?
|
|
|
|
| Re: Postgres and CurrVal [message #23043 is a reply to message #23025] |
Thu, 10 September 2009 12:01  |
 |
mirek
Messages: 13975
Registered: November 2005
|
Ultimate Member |
|
|
| Zbych wrote on Tue, 08 September 2009 10:18 |
Hi,
CurrVal doesn't seem to work correctly with Postgres right now.
When I use it like this:
sql & Insert(TABLE1)(COLUMN1, "test")(COLUMN2, CurrVal(TABLE2_COLUMN1_SEQ));
Sql string looks like this:
insert into TABLE1 (COLUMN1, COLUMN2) values ('test',currval('TABLE2_COLUMN1_SEQ')TABLE2_COLUMN1_SEQ.CURRVAL))
It looks like SqlCase is not "compiled". Any ideas how to fix it?
|
This is really strange, I have tried with PGSQL reference example:
and got a crash (correct one: uncaught exception) and this in log:
SQL* insert into TESTPARTNER(TESTPARTNER_NAME, TESTPARTNER_ADDRESS) values ('test', currval('TESTPARTNER'))
SQL* insert into TESTPARTNER(TESTPARTNER_NAME) values (currval('TESTPARTNER'))
which IMO is correct.
I would need a more complete testcase to investigate the issue...
Mirek
|
|
|
|
| Re: Postgres and CurrVal [message #23044 is a reply to message #23043] |
Thu, 10 September 2009 12:52 |
Zbych
Messages: 325
Registered: July 2009
|
Senior Member |
|
|
| luzr wrote on Thu, 10 September 2009 12:01 |
which IMO is correct.
I would need a more complete testcase to investigate the issue...
|
Sorry, my mistake. I had to replace upp's SqlInsert with "query in string" + ExecuteX to insert many rows at once, and I've used ~CurrVal(). That's why I had this funny version of currval.
BTW. Is there a way to force SqlInsert, to create single insert with many rows:
insert into testpartner (testpartner_name, testpartner_address)
values
('qweqwe','qweqweqwe'),
('qweqwe','qweqweqwe'),
('qweqwe','qweqweqwe'),
('qweqwe','qweqweqwe')
|
|
|
|
| Re: Postgres and CurrVal [message #23046 is a reply to message #23044] |
Thu, 10 September 2009 14:12 |
|
mirek
Messages: 13975
Registered: November 2005
|
Ultimate Member |
|
|
| Zbych wrote on Thu, 10 September 2009 06:52 |
| luzr wrote on Thu, 10 September 2009 12:01 |
which IMO is correct.
I would need a more complete testcase to investigate the issue...
|
Sorry, my mistake. I had to replace upp's SqlInsert with "query in string" + ExecuteX to insert many rows at once, and I've used ~CurrVal(). That's why I had this funny version of currval.
BTW. Is there a way to force SqlInsert, to create single insert with many rows:
insert into testpartner (testpartner_name, testpartner_address)
values
('qweqwe','qweqweqwe'),
('qweqwe','qweqweqwe'),
('qweqwe','qweqweqwe'),
('qweqwe','qweqweqwe')
|
Not yet. Maybe we could add Next() method:
Insert(TESTPARTNER)(NAME, "A").Next()(NAME, "B")
or maybe overload some operator
Insert(TESTPARTNER)(NAME, "A")/(NAME, "B")
Before we do, we should check if it is possible to do this in all supprted SQLs. E.g. this looks quite different:
http://www.techonthenet.com/sql/insert.php
Mirek
|
|
|
|
| Re: Postgres and CurrVal [message #23053 is a reply to message #23046] |
Fri, 11 September 2009 13:36 |
Zbych
Messages: 325
Registered: July 2009
|
Senior Member |
|
|
| luzr wrote on Thu, 10 September 2009 14:12 |
Before we do, we should check if it is possible to do this in all supprted SQLs
|
Postgres - supports,
MSSQL - supports (MSSQL 2008 and newer)
SqlLite - doesn't support (it could be emulated with many inserts)
MySQL - supports
Oracle - supports, different syntax
[Updated on: Fri, 11 September 2009 13:37] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Postgres and CurrVal [message #23185 is a reply to message #23138] |
Thu, 24 September 2009 10:42 |
|
mirek
Messages: 13975
Registered: November 2005
|
Ultimate Member |
|
|
| Zbych wrote on Fri, 18 September 2009 04:25 |
| luzr wrote on Thu, 17 September 2009 17:26 |
And your opinion about the 'syntax'? Any ideas?
|
Do you mean upp syntax or sql syntax?
Upp syntax (Next() and /) is ok for me. I've made some changes in SqlInsert to support Next() and it works with postgres.
I don't know if SqlInsert should be idiot proof and allow to mix order of columns?
Insert(TABLE)
(COL1, "aaa")(COL2, "bbb").Next()
(COL2, "ccc")(COL1, "ddd").Next() //switched columns
(COL1, "eee"); //one column is missing
|
Well... Maybe we should rather opt for special "MultiInsert" class for this, with different interface...
In any case, there is sort of problem with sql "compiler" internals. Those completely different syntaxes of Oracle and Sqlite3 would make internal representation (using SqlCase) too long, with all values repeated 3 times...
OTOH, maybe this could solve the problem of missing columns..
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 14:40:48 CEST 2024
Total time taken to generate the page: 0.05104 seconds
|
Members
Pages
Help
Register
Login
Home
