Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Postgres and CurrVal
Postgres and CurrVal [message #23025] Tue, 08 September 2009 16:18 Go to next message
Zbych is currently offline  Zbych
Messages: 307
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 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11843
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 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 307
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 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11843
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 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 307
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 #23113 is a reply to message #23053] Thu, 17 September 2009 17:26 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 11843
Registered: November 2005
Ultimate Member
Zbych wrote on Fri, 11 September 2009 07:36

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







And your opinion about the 'syntax'? Any ideas?
Re: Postgres and CurrVal [message #23138 is a reply to message #23113] Fri, 18 September 2009 10:25 Go to previous messageGo to next message
Zbych is currently offline  Zbych
Messages: 307
Registered: July 2009
Senior Member
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 

Re: Postgres and CurrVal [message #23185 is a reply to message #23138] Thu, 24 September 2009 10:42 Go to previous message
mirek is currently offline  mirek
Messages: 11843
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..
Previous Topic: Acquiring large record
Next Topic: Can I use Upp::Oracle8 connect to Oracle10?
Goto Forum:
  


Current Time: Thu Apr 25 03:51:36 CEST 2019

Total time taken to generate the page: 0.02664 seconds