Auto Completion is not working in non-dbo schemas

Connected to a Sybase db.. Schema is a non-dbo (see attached pic) select * from and hit Ctrl+Space Nothing comes up, debug output below. Manually changing Schema to "dbo" solves the issue.... 08:42:45 [DEBUG ExecutorRunner-pool-2-thread-134 N.?] DbConnection='SYBAMR_FDA_PRD_DS01 PROD (PRIMARY fda_etma_prd)' Catalog='FDA_PRD_NY' Schema='fda_etma_prd' Table='null' Type(s)='null' 08:42:45 [DEBUG pool-3-thread-234 E.?] RootConnection: SybDatabaseMetaData.getTables("FDA_PRD_NY", "fda_etma_prd", "%", null) Product: DbVisualizer Pro 9.1.3 Build: #2091 (2013/11/22 16:43) Java VM: Java HotSpot(TM) Client VM Java Version: 1.6.0_51 Java Vendor: Sun Microsystems Inc. OS Name: Windows 7 OS Arch: x86 OS Version: 6.1

Hi Alex, Thanks. Does the FDA_PRD_NY database contain any tables or views owned by the fda_etma_prd schema? I'm asking since you say that it works when you set the Schema drop down list (?) to dbo. The value in the Schema list limits the auto completion to only look for tables/views in the specified schema. Best Regards, Hans

Hans No. But account +fda_etma_prd+ has +select+ privileges on many tables and thats the account we use to build queries in that particular server....

Hi Alex, You can set the Schema list to empty to let auto-complete list objects regardless of schema, or type the schema name and a period and then trigger auto-completion to list objects from that schema. The Schema list is used to limit the auto-completion lookup, as described here: http://confluence.dbvis.com/display/UG91/Selecting+Database+Connection%2C+Catalog+and+Schema Best Regards, Hans

I tried -- both radio buttons are disabled and I cant modify it. Tried it with Connected/Disconnected db.

Hi Alex, I'm talking about the Schema drop down list in the SQL Commander toolbar. In the screenshot you posted earlier, both the Database and the Schema drop down controls are enabled. Best Regards, Hans

I was trying to disable that behavior from the link you provided, so that autocomplete works as I expect it to and schema is set to blank or null.. +If you don't want the selected schema to be used as the default schema for these database, you can disable this behavior in the *Properties *tab for the connection, in the *SQL Commander *category+. Am I misunderstanding this? Thanks.

Hi Alex, Ah, I see. But it also says this above the section you quote: +For most databases, the schema selected in the Schema list is used only to limit the tables the Auto Completion feature shows in the completion pop-up; it does not define a default schema for tables referenced in the SQL, because most databases do not allow the default schema to be changed during a session.+ +For the databases that do allow the default schema to be changed, however, the selected schema is also used as the default schema, i.e., the schema used for unqualified table names in the SQL. *Currently, the databases that support setting a default schema are DB2 LUW, DB2 z/OS, HP Neoview, H2, JavaDB/Derby and Oracle*.+ Sybase is not one of the databases listed so, in other words, the selected schema is not used as the default schema for Sybase. But that means it is still used to limit auto-completion. Again, if you want auto-completion to look for objects in all schemas, set the Schema drop down in the SQL Commander toolbar to blank. Best Regards, Hans Edited by: Hans Bergsten on Nov 22, 2013 10:13 PM

Ah, I see now. Any way to set this as a preference per connection to set the default schema to null? That would alleviate the need to remember to reset it every time I open a connection to Sybase dB?

Also, with upgrade to 9.1.3, even after setting Schema to null and chen connected as a non-dbo : Autocomplete in 'from' works AutoComplete in 'where' does not: My guess is that it places 'tss_ds_dev2' in place of owner.. db.owner.table Because if I do this, then it works select * from tss_ds_dev2.*dbo*.apps_UTS_ACCF where ___ ------------------------- Product Information ------------------------ Product: DbVisualizer Pro 9.1.3 Build: #2091 (2013/11/22 16:43) Java VM: Java HotSpot(TM) Client VM Java Version: 1.6.0_51 Java Vendor: Sun Microsystems Inc. OS Name: Windows 7 OS Arch: x86 OS Version: 6.1 ------------------------- Connected Databases ------------------------ Database Type: Sybase ASE Major Version: 15 Minor Version: 7 Profile: sybase-ase Connection Message: Adaptive Server Enterprise Adaptive Server Enterprise/15.7.0/EBF 20809 SMP ESD#04 /P/x86_64/Enterprise Linux/ase157x/3245/64-bit/FBO/Fri Jan 4 05:11:12 2013 jConnect (TM) for JDBC (TM) jConnect (TM) for JDBC(TM)/7.07 ESD #4 (Build 26792)/P/EBF20144/JDK 1.6.0/jdbcmain/OPT/Thu Jun 7 23:57:39 PDT 2012 --------------------------- Debug Printouts -------------------------- 10:17:21 [DEBUG ExecutorRunner-pool-2-thread-5 J.execute] Executing... 10:17:21 [DEBUG pool-3-thread-7 C.?] RootConnection: SybConnection.createStatement() 10:17:21 [DEBUG pool-3-thread-7 B.?] RootConnection: SybStatement.execute("SELECT * FROM tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF WHERE 1 = 0") 10:17:21 [DEBUG pool-3-thread-7 B.?] RootConnection: EXCEPTION -> java.sql.SQLException: tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). java.sql.SQLException: tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2526) at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2323) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:276) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:253) at com.sybase.jdbc4.jdbc.SybStatement.executeLoop(SybStatement.java:2252) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:2239) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:1078) at sun.reflect.GeneratedMethodAccessor53.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.B.?(Z:104) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) 10:17:21 [DEBUG ExecutorRunner-pool-2-thread-5 A.?] AutoComplete exception com.onseven.dbvis.J.B.O: java.sql.SQLException: tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). at com.onseven.dbvis.J.B.J.execute(Z:70) at com.onseven.dbvis.J.B.Y.?(Z:1386) at com.onseven.dbvis.J.B.Y.run(Z:337) at com.onseven.dbvis.J.B.W.?(Z:365) at com.onseven.dbvis.J.B.W.?(Z:1021) at com.onseven.dbvis.J.B.W.?(Z:284) at com.onseven.dbvis.f.A.A$7.?(Z:763) at com.onseven.dbvis.J.B.A.I.execute(Z:2291) at com.onseven.dbvis.J.B.Y.?(Z:1386) at com.onseven.dbvis.J.B.K.?(Z:1374) at com.onseven.dbvis.J.B.K.doInBackground(Z:1521) at javax.swing.SwingWorker$1.call(Unknown Source) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at javax.swing.SwingWorker.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2526) at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2323) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:276) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:253) at com.sybase.jdbc4.jdbc.SybStatement.executeLoop(SybStatement.java:2252) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:2239) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:1078) at sun.reflect.GeneratedMethodAccessor53.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.B.?(Z:104) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) ... 3 more 10:19:02 [DEBUG ExecutorRunner-pool-2-thread-6 N.?] DbConnection='SYBAMR_ETMA_DEV_DS01 (etma_prod)' Catalog='tss_ds_dev2' Schema='null' Table='null' Type(s)='null' 10:19:02 [DEBUG pool-3-thread-8 E.?] RootConnection: SybDatabaseMetaData.getTables("tss_ds_dev2", null, "%", null) 10:19:06 [DEBUG ExecutorRunner-pool-2-thread-6 J.execute] Executing... 10:19:06 [DEBUG pool-3-thread-8 C.?] RootConnection: SybConnection.createStatement() 10:19:06 [DEBUG pool-3-thread-8 B.?] RootConnection: SybStatement.execute("SELECT * FROM tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF WHERE 1 = 0") 10:19:06 [DEBUG pool-3-thread-8 B.?] RootConnection: EXCEPTION -> java.sql.SQLException: tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). java.sql.SQLException: tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2526) at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2323) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:276) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:253) at com.sybase.jdbc4.jdbc.SybStatement.executeLoop(SybStatement.java:2252) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:2239) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:1078) at sun.reflect.GeneratedMethodAccessor53.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.B.?(Z:104) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) 10:19:06 [DEBUG ExecutorRunner-pool-2-thread-6 A.?] AutoComplete exception com.onseven.dbvis.J.B.O: java.sql.SQLException: tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). at com.onseven.dbvis.J.B.J.execute(Z:70) at com.onseven.dbvis.J.B.Y.?(Z:1386) at com.onseven.dbvis.J.B.Y.run(Z:337) at com.onseven.dbvis.J.B.W.?(Z:365) at com.onseven.dbvis.J.B.W.?(Z:1021) at com.onseven.dbvis.J.B.W.?(Z:284) at com.onseven.dbvis.f.A.A$7.?(Z:763) at com.onseven.dbvis.J.B.A.I.execute(Z:2291) at com.onseven.dbvis.J.B.Y.?(Z:1386) at com.onseven.dbvis.J.B.K.?(Z:1374) at com.onseven.dbvis.J.B.K.doInBackground(Z:1521) at javax.swing.SwingWorker$1.call(Unknown Source) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at javax.swing.SwingWorker.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: tss_ds_dev2.tss_ds_dev2.apps_UTS_ACCF not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(SybConnection.java:2526) at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(SybStatement.java:2323) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:276) at com.sybase.jdbc4.jdbc.SybStatement.nextResult(SybStatement.java:253) at com.sybase.jdbc4.jdbc.SybStatement.executeLoop(SybStatement.java:2252) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:2239) at com.sybase.jdbc4.jdbc.SybStatement.execute(SybStatement.java:1078) at sun.reflect.GeneratedMethodAccessor53.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.B.?(Z:104) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) ... 3 more

Hi Alex, Setting the schema to blank by default is something we have on our to do list and it will likely be done soon. Best Regards, Hans

Thanks! Are you by chance looking at my post from "Nov 25, 2013 10:21 AM" as well ?

Hi Alex, Sorry about the regression with auto-completion in WHERE clauses. I will try to resolve that. Until then, you need to qualify the table name with the schema. You can let auto-complete do that for you if you enable it in Tool Properties, under Database/Sybase ASE/Qualifiers. Best Regards, Hans

Hi -- regarding your post on *Nov 25, 2013 12:44 PM* when do you expect this to be done? (Schema to be null by default)