PostgreSQL JDBC - UPDATE with database name alias

Author

UPDATE with database name alias

andres@sayago.info

2005-09-19, 8:25 pm

Hello friends.

I'm new in this list, but not in the database world. I have a question and
I need yout colaboration.

I am programming with JDeveloper 10g, and I want to use PostgreSQL
database. I'm using Oracle but I wanna do the migration. But, I have a big
problem: I use PostgreSQL with ADF and ADF uses aliasses for the database
name when it need to do a UPDATE. Example:

UPDATE conference_romms ConferenceRooms
SET ConferenceRooms.room_name = 'Golden'
WHERE ConferenceRooms.room_id = 57;

That sentence fail because the UPDATE clause don't support aliasses.

I'm thinking right? Or I am mistaked.

PostgreSQL don't support database name aliasses for the UPDATE option??

How can I do it??

Thanks,

Andrés Säyago
Bogotá D.C., Colombia

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dave Cramer

2005-09-19, 8:25 pm

Don't use aliases ?

Dave
On 19-Sep-05, at 2:08 PM, andres@sayago.info wrote:

> Hello friends.
>
> I'm new in this list, but not in the database world. I have a
> question and
> I need yout colaboration.
>
> I am programming with JDeveloper 10g, and I want to use PostgreSQL
> database. I'm using Oracle but I wanna do the migration. But, I
> have a big
> problem: I use PostgreSQL with ADF and ADF uses aliasses for the
> database
> name when it need to do a UPDATE. Example:
>
> UPDATE conference_romms ConferenceRooms
> SET ConferenceRooms.room_name = 'Golden'
> WHERE ConferenceRooms.room_id = 57;
>
> That sentence fail because the UPDATE clause don't support aliasses.
>
> I'm thinking right? Or I am mistaked.
>
> PostgreSQL don't support database name aliasses for the UPDATE
> option??
>
> How can I do it??
>
> Thanks,
>
> Andrés Säyago
> Bogotá D.C., Colombia
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

andres@sayago.info

2005-09-19, 8:25 pm

It's a perfect idea, but I don't use alias, ADF use them.

With JDeveloper+ADF, ADF is who connect to the database, I dont do it.

JDeveloper use alias to UPDATE, an dthen, what can I do?

PostgreSQL don't use alias to UPATE? Never?

Thanks,

Andrés

> Don't use aliases ?
>
> Dave
> On 19-Sep-05, at 2:08 PM, andres@sayago.info wrote:
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Kris Jurka

2005-09-19, 8:25 pm

On Mon, 19 Sep 2005 andres@sayago.info wrote:

> JDeveloper use alias to UPDATE, an dthen, what can I do?
>

Your only option would be to ajdust the postgresql backend grammar to
allow an alias.

> PostgreSQL don't use alias to UPATE? Never?
>

Right, the SQL standard does not allow aliases on UPDATEs and neither does
postgresql.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

John R Pierce

2005-09-19, 8:25 pm

> Right, the SQL standard does not allow aliases on UPDATEs and neither
> does postgresql.

amusing aside, a bit of googling (since I didn't know what ADF was) has
some suit from Oracle Inc. boasting how ADF works with any "SQL
Compatible" database. Last Q&A on this page:
http://www.itwriting.com/jdev1.php

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Vadim Nasardinov

2005-09-20, 9:24 am

On Monday 19 September 2005 18:46, Kris Jurka wrote:
>
> Your only option would be to ajdust the postgresql backend grammar
> to allow an alias.

In practical terms, you're right.

In theory, this may also be addressed by using a wrapper JDBC driver
like P6Spy (but not as buggy) that would sit between the application
and the real PostgreSQL JDBC driver. The wrapper driver would have
the opportunity to rewrite UPDATE statements in a spec-compliant
manner before passing them on to the real driver.

http://sourceforge.net/projects/p6spy/
http://www.google.com/search? q=ant...+grammar

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links