SEO

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

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

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:
>
>> 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
>


---------------------------(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

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

> 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

Kris Jurka <books@ejurka.com> writes:
> 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.

Isn't it an option to point out to the JDeveloper authors that they are
emitting SQL that is illegal per spec?

regards, tom lane

---------------------------(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

It's an option, but it's an Oracle product, chances are they would
ignore it.

Dave
On 19-Sep-05, at 10:53 PM, Tom Lane wrote:

> Kris Jurka <books@ejurka.com> writes:
>
>> 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.
>>
>
> Isn't it an option to point out to the JDeveloper authors that they
> are
> emitting SQL that is illegal per spec?
>
> regards, tom lane
>
>


---------------------------(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

Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
>
>>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.
>
>
> Isn't it an option to point out to the JDeveloper authors that they are
> emitting SQL that is illegal per spec?
>

JDeveloper and ADF are from Oracle. you expected something different?

---------------------------(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

On Monday 19 September 2005 18:46, Kris Jurka 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.

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=antlr+SQL+grammar
http://www.google.com/search?q=JavaCC+SQL+grammar

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

Thanks Vadim, but I have another problem.

I can't install P6Spy on JDeveloper. I tried using the instructions, but
in the P6Spy READ file don't exist about JDeveloper.

I tried replacing, in the JDeveloper connections, the PostgreSQL driver
"org.postgresql.Driver" by "com.p6spy.engine.spy.P6SpyDriver", and then
configuring the "spy.properties" file with the correct realdriver, but I
get the following message:

-----
oracle.jdeveloper.cm.CMException: Unable to instantiate driver:
com.p6spy.engine.spy.P6SpyDriver
at
oracle.jdeveloper.cm.JdbcConnectionWrapper.createC onnection(JdbcConnectionWrapper.java:130)
at
oracle.jdeveloper.cm.JdbcConnectionWrapper.getJdbc Connection(JdbcConnectionWrapper.java:181)
at
oracle.jdeveloper.cm.JdbcConnectionWrapper.getPres entation(JdbcConnectionWrapper.java:168)
at
oracle.jdevimpl.cm.dt.browser.jdbc.JdbcBrowser.ope nConnectionBrowser(JdbcBrowser.java:208)
at
oracle.jdeveloper.cm.dt.ConnectionNode$NodeOpen.do Work(ConnectionNode.java:210)
at oracle.jdeveloper.cm.dt.ProgressRunnable.run(Progr essRunnable.java:165)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.jav a:540)
at java.lang.Thread.run(Thread.java:534)
-----

Anyone know this?

Andrés


> On Monday 19 September 2005 18:46, Kris Jurka 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.
>
> 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=antlr+SQL+grammar
> http://www.google.com/search?q=JavaCC+SQL+grammar
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend