Sql generation error / comment error.

[razman52]

Thanks for the App I love it and use it every day.

When I am connected to an Oracle DB and am using the results pane to
edit an existing value in the database. The sql that is generated for
me when clicking the save icon always puts in an owner of 'public'
before the table name. Is there anyway to keep it from putting in the
owner modifier before the table name?

example:
UPDATE PUBLIC.CONTACT
SET LNAME = 'Dylan'
WHERE CONTACT_ID = 34452;

Also a long standing problem that I was wondering about is when you
have a comment in the sql pane before a block of sql and you try to
execute the sql with a cntl + Enter it throws a "missing mandatory
parameter" error.

example:
--This is a comment
select *
from dual;

[Thomas Kellerer]

Hi,

> When I am connected to an Oracle DB and am using the results pane to
> edit an existing value in the database. The sql that is generated for
> me when clicking the save icon always puts in an owner of 'public'
> before the table name. Is there anyway to keep it from putting in the
> owner modifier before the table name?
>
> example:
> UPDATE PUBLIC.CONTACT
> SET LNAME = 'Dylan'
> WHERE CONTACT_ID = 34452;

Are you sure you are using Oracle? I have never seen that happen there. Usually the owner prefix is only added when the table you are updating is not in the current schema (i.e. the current user in most of the cases)

Is contact by any chance a public synonym? That could explain that behaviour. If it was a table that was owned by the current user, this should not happen.

Can you check if you have the following line in your workbench.settings:

workbench.sql.ignoreschema.oracle=PUBLIC

If it's not there just add it using a text editor. You have to close the application before editing the file.

If you are on Windows this file is (normally) located in the directory "%USERPROFILE%\.sqlworkbench"

On a *nix system this would be $HOME/.sqlworkbench

> Also a long standing problem that I was wondering about is when you
> have a comment in the sql pane before a block of sql and you try to
> execute the sql with a cntl + Enter it throws a "missing mandatory
> parameter" error.
>
> example:
> --This is a comment
> select *
> from dual;

Very strange, this works for me without problems.
Where exactly do is the cursor when you hit Ctrl-Enter?
What else do you have in the editor?
Which version (build) are you using?

Could you set the log level to DEBUG (Tools -> Options -> General), run this again and then send me the logfile to the support email?

Regards
Thomas

[razman52]

Thanks for the quick reply.

Windows XP
Build 104.2(2009-06-21 18:25)
Java Version 1.5.0_07-b03

As to the first problem. I found the settings file that you described
and it indeed had the line you were looking for in it.

When this issue happens I am logged into an oracle 10g database, but
not as the schema owner. I am accessing it through a public synonym
and access to the table was granted to me through a role. This however
this does not seem to be the problem because I can repeat the issue
when logged in as the schema owner. This is something that used to
work up until about 2-3 builds ago when the public modifier started
showing up in the update queries. My permissions to the tables in
question haven't changed.


As to the second issue.


The sql I ran was this:

--this is a comment

Select * from dual;

and the cursor was placed between the c and the t in the word Select,
but this doesn't seem to matter because the problem repeats with the
cursor anywhere in the sql statement. It seems like it is reading too
far and trying to parse the comment as well. In fact when I have
finished hitting cntl + enter and the error occurs, both the comment
in its entirety and the select statement are highlighted in red. When
I run a statement that doesn't have a comment above the sql everything
seems to work correctly. But as soon as the comment is added it seems
to keep parsing passed the beginning of the sql and all the way back
up into the comment.

When running the following there is no errors at all.

/* this is another comment */

select * from dual;


So it only seems to be a problem with -- comments.


I have sent the log file to the support e-mail.

There was nothing other than the sql stated in the editor window when
I pressed cntl + Enter.




INFO 24.06.2009 07:47:14 DbDriver.loadDriverClass() - Adding
ClassLoader URL=file:/C:/Workbench SQL/classes12.zip
INFO 24.06.2009 07:47:15 DbMetadata - Using DBID=oracle
DEBUG 24.06.2009 07:47:15 DbMetadata - Identifier quote character
obtained from driver: "
INFO 24.06.2009 07:47:15 ConnectionMgr.getConnection() - Connected
to: [Oracle], Database version: [Oracle Database 10g Enterprise
Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options], Driver version:
[8.1.7.1.0], ID: [Wb1-9]
WARN 24.06.2009 07:47:15 WbConnection.getDatabaseVersion() - Error
retrieving DB version
(oracle.jdbc.driver.OracleDatabaseMetaData.getDatabaseMajorVersion()I)
ERROR 24.06.2009 07:47:27 DataStore.initData() - SQL Error during
retrieve: ORA-01009: missing mandatory parameter [SQL State=72000, DB
Errorcode=1009]

java.sql.SQLException: ORA-01009: missing mandatory parameter

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:889)
at oracle.jdbc.driver.OracleResultSetImpl.next
(OracleResultSetImpl.java:244)
at workbench.storage.DataStore.initData(DataStore.java:1111)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:410)
at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:
126)
at workbench.sql.StatementRunner.runStatement(StatementRunner.java:
380)
at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:2586)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:1707)
at workbench.gui.sql.SqlPanel$11.run(SqlPanel.java:1675)

DEBUG 24.06.2009 07:47:27 SelectCommand.execute() - Error executing
statement: --this is a comment

Select * from dual: ORA-01009: missing mandatory parameter [SQL
State=72000, DB Errorcode=1009]

java.sql.SQLException: ORA-01009: missing mandatory parameter

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:889)
at oracle.jdbc.driver.OracleResultSetImpl.next
(OracleResultSetImpl.java:244)
at workbench.storage.DataStore.initData(DataStore.java:1111)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:410)
at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:
126)
at workbench.sql.StatementRunner.runStatement(StatementRunner.java:
380)
at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:2586)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:1707)
at workbench.gui.sql.SqlPanel$11.run(SqlPanel.java:1675)

[Thomas Kellerer]

Hi,

> INFO 24.06.2009 07:47:14 DbDriver.loadDriverClass() - Adding
> ClassLoader URL=file:/C:/Workbench SQL/classes12.zip

> Driver version: [8.1.7.1.0]

You are using an *extremely* outdated Oracle JDBC driver [8.1.7.1.0]

classes12.zip is intended for JDK 1.2 and is probably de-supported
since several years.

With Oracle 10.x you should at least use a 10.x drivers (if not the
11.x) especially because anything before 9.x did not support BLOBS
very well. And the 10.x driver is a *lot* faster than all previous
driver versions.

You can download the current drivers here:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

The error message

> Error retrieving DB version (oracle.jdbc.driver.OracleDatabaseMetaData.getDatabaseMajorVersion()I)

also indicates that the 8.x driver does not support all of JDBC API
correctly.

It could very well be that the 8.x driver does not support embedded
comments in statements (because they are sent to the server as well).

This _might_ influence the detection for the PUBLIC thing as well, but
I will need to re-check that. I have not done any testing using public
synonyms because I strongly dislike them (it's like using global
variables in a programming language) and I always recommend not to use
them.
But as I said I will re-test this with a public synonym.


Regards
Thomas

[razman52]

Awesome.

That seems to have fixed the comment error that I was receiving. I
downloaded a newer DB driver (ojdbc14.jar), installed it and TA-DA it
works.


However even with the newer driver I am still getting the public
modifier in the auto generated update statement. This doesn't seem to
matter if I am logged in as the schema owner or not. Any other options
to try?


Thanks

[Thomas Kellerer]

Hi,

> However even with the newer driver I am still getting the public
> modifier in the auto generated update statement. This doesn't seem to
> matter if I am logged in as the schema owner or not. Any other options
> to try?

No, I rechecked this.

This is working as expected. If the object you are trying to update is
not in the current schema, I need to prefix the table with the schema
(owner for Oracle).

The ignoreschema is (currently) only intended for cases where there is
either no current schema or if it's the same as the current one.

I'm not sure if I can safely ignore the owner in this situation
(because that part of the SQL generation affects other RDBMS as well
that do not have the concept of "global variables"

Thomas

[razman52]

In my situation the owner of the "contact" table is "scd" not
"public". So if you were going to reference the schema owner (which is
fine) wouldn't the generated sql look like the following?

UPDATE SCD.CONTACT

SET LNAME = 'Dylan'
WHERE CONTACT_ID = 34452;

If this is what were generated I would not have any issues, since this
is the proper oracle syntax.
I think it is just picking up the wrong owner.

I had a co-worker run the same query in an older version of sql-
workbench and his did not add the public modifier in front of the
table. This would seem to mean it is something that has changed in the
revisions since. We ran the query with the same credentials against
the same database.

He was running
Build 102 2008/09/15
with a jre of 1.6_13
and a database driver of ojdbc14.jar.


Thanks
razman52

[Thomas Kellerer]

On 24 Jun., 21:06, razman52 <razma...@gmail.com> wrote:
> In my situation the owner of the "contact" table is "scd" not
> "public". So if you were going to reference the schema owner (which is
> fine) wouldn't the generated sql look like the following?

The problem is that, when I'm asking the driver "give me the details
about the object 'CONTACT' so that I can generate the proper UPDATE
statement", the driver will return the (global) synonym definition
first.

That's why SQL Workbench thinks you are selecting from the synonym
rather than from the real table.

I will see if I can implement something that will prefer the object in
the current schema over the object in "PUBLIC" without breaking this
for all the other databases.

As far as I can tell using the prefix PUBLIC seems to be always wrong
for Oracle, so the quick fix is to catch that situation. But the
proper solution is to change the way the correct DB object is
searched.

(This is one of the reasons I hate public synonyms: they just make
everything so unclear)

Regards
Thomas

[razman52]

Any idea why it worked fine in the older version of SQL Workbench? Was
there an Oracle hook in there before?

I spoke with my DBA and asked what the query you are describing would
look like. So I took a look at the table and I think the problem could
be solved with a simple order by statement.

example:
select * from all_objects where object_name = 'CONTACT' order by
data_object_id;

This will put them in the correct order if you grab the first one
returned. The public synonyms have a null data_object_id so they sort
to the bottom naturally.
The only question would be, does this scenario work for other DB's

Thanks
razman52

[Thomas Kellerer]

On 24 Jun., 23:34, razman52 <razma...@gmail.com> wrote:
> Any idea why it worked fine in the older version of SQL Workbench? Was
> there an Oracle hook in there before?

Yes as I said: the schemas defined by ignoreschema were *always*
ignored, but that caused problems with other DBMS.

> I spoke with my DBA and asked what the query you are describing would
> look like. So I took a look at the table and I think the problem could
> be solved with a simple order by statement.

That doesn't help, because retrieving the table information is a JDBC
API call and that call returns the objects sorted by type.

If I can't get that to work, I can ignore the PUBLIC schema for Oracle
in al cases.

Thomas

[razman52]

Thanks for the clarification, and a great product.

Good luck.