Thread: UPDATE with database name alias
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
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 > >
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 >
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
> 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
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
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 > >
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?
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
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.createConnection(JdbcConnectionWrapper.java:130)
at
oracle.jdeveloper.cm.JdbcConnectionWrapper.getJdbcConnection(JdbcConnectionWrapper.java:181)
at
oracle.jdeveloper.cm.JdbcConnectionWrapper.getPresentation(JdbcConnectionWrapper.java:168)
at
oracle.jdevimpl.cm.dt.browser.jdbc.JdbcBrowser.openConnectionBrowser(JdbcBrowser.java:208)
at
oracle.jdeveloper.cm.dt.ConnectionNode$NodeOpen.doWork(ConnectionNode.java:210)
at oracle.jdeveloper.cm.dt.ProgressRunnable.run(ProgressRunnable.java:165)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java: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
>
On Tuesday 20 September 2005 10:48, andres@sayago.info wrote: > I can't install P6Spy on JDeveloper. I tried using the instructions, but > in the P6Spy READ file don't exist about JDeveloper. P6Spy is a waste of time. I only mentioned it as an example of an existing wrapper that kinda sorta does something similar to what you'd have to do if you chose to fix up Oracle's broken UPDATE statements without changing the backend SQL grammar on the PostgreSQL server side. If you are seriously considering going down this path, you might be better off starting from scratch. In any case, this is not the right list to ask about P6Spy. Try http://sourceforge.net/forum/?group_id=49288
Hello. Do you remember that I was trying to connect JDeveloper+ADF with Postgres but I got a SQL sintaxis error? Well, I have the answer. I rewrote the JDBC Java driver to allow that ADF can send UPDATE with aliases, and the driver can eliminate them. If you are interested in this subject, please visit: http://www.sayago.info/jdbc-pgsql/index_en.php and if you need the explanation in Spanish, visit: http://www.sayago.info/jdbc-pgsql/index.php Thanks to all people that sent me colaboration!! Andrés Sáyago
I'm interested in your solution but your link is no longer working....