invalid generated sql in create table wizard (pg8)

the create table wizard generates invalid sql. generated tablename is *db.schema.tablename* but it should be *schema.tablename* DB: postgresql8 Product: DbVisualizer Personal 6.0.9 Build: #1299 (2008/03/16 21:41) Java VM: Java HotSpot(TM) Server VM Java Version: 1.5.0_11 Java Vendor: Sun Microsystems Inc. OS Name: Linux OS Arch: i386 OS Version: 2.6.18.8-0.7-default

Hi Thomas, You can disable the qualifiers in Tool Properties, under the Database tab in the Qualifiers category for the PostgreSQL database. If you have them enabled, both database and schema is used. This is syntactically correct and the safest way to identify a table. Is there a specific reason why you only want the schema qualifier? Best Regards, Hans

hi, i would not disable qualifiers cause i use multiple schemas. And identifiers like the described are not valid sql in pg8. so it doesnt matter if i enable qualifiers. I tried to disable all qualifiers (connection properties -> PostgreSQL -> Qualifiers) but "Create Table" Dialog doesnt change something. database and schema are always prepended. thx Thomas

Hi Thomas, > {quote:title=Thomas wrote:}{quote} > > i would not disable qualifiers cause i use multiple schemas. And identifiers like the described are not valid sql in pg8. so it doesnt matter if i enable qualifiers. "CREATE TABLE .." works fine for me with PostgreSQL 8.1. Please post the error message you get if it doesn't work for you, along with information about the specific database version and JDBC driver. > I tried to disable all qualifiers (connection properties -> PostgreSQL -> Qualifiers) but "Create Table" Dialog doesnt change something. database and schema are always prepended. Sorry, you're right. In the next release you will be able to set the Database and Schema combos in the dialog to "nothing" to avoid qualifiers. Kind Regards, Hans

my steps: * connect to PostgreSQL 8.1.4 * select Tables Node (Databases/testdb/Schemas/public/Tables) * right click -> Create Table -> Execute * generated sql is CREATE TABLE testdb.public.newTable ( ) +ERROR: cross-database references are not implemented: "testdb.public.newtable"+ thats right. generated sql should look like CREATE TABLE public.newTable ( ) Thomas Edited by: Thomas on 02.04.2008 07:30 Edited by: Thomas on 02.04.2008 07:32 argh, the editor cant handle italic text with spaces at line start

Hi Thomas, > {quote:title=Thomas wrote:}{quote} > +ERROR: cross-database references are not implemented: "testdb.public.newtable"+ Is testdb the database you are connected to? If not, that explains the error. I know that using the database qualifier may seem superfluous, but it is syntactically correct according to the PostgreSQL documentation (see 5.7.1. Creating a Schema), as long as it specifies the database you are connected to. [http://www.postgresql.org/docs/8.1/static/ddl-schemas.html] If testdb is the database you are connected to and you still get this error, it may be the JDBC driver that generates the error message. I have tested with "PostgreSQL 8.2 JDBC3 with SSL (build 506)" and do not get the error. That said, we may add a new property for selecting which qualifiers to include for databases that support both a database and a schema qualifier (e.g., PostgreSQL, SQL Server and Sybase), but we need to make sure it doesn't cause any unwanted side-effects in any of the features that generate SQL based on these properties. Best Regards, Hans

problem found i disabled connection properties -> postgresql -> delimited identifiers -> scripting and my database name is in upper case. so the generated sql is create table TESTDB.public.newTable and is db internal converted to create table testdb.public.newtable so quotes are required here. maybe its a good idee to ignore that option on existing identifiers. kind regards Thomas