Start a new topic

DBs supporting named parameter markers?

[This topic is migrated from our old forums. The original author name has been removed]

Trying this new feature (http://confluence.dbvis.com/display/UG92/Using+Parameter+Markers) on PostgreSQL 9.3 (with the DbVis supplied driver), I am able to get the ? syntax, but not the :named syntax. Is there a list of which drivers support it? SELECT * FROM test WHERE month_dt = ?; -- succeeds SELECT * FROM test WHERE month_dt = :dt; -- [Error Code: 0, SQL State: 42601] ERROR: syntax error at or near ":"

[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Hi Matt, Currently only question marks are supported. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
OK - the release notes for 92.2320 show "SQL Commander: Allow use of parameter markers (?) for any database [http://www.dbvis.com/forum/thread.jspa?threadID=4247 ]" as an addressed issue, which was specifically about a different syntax for named parameters to match those used in psql scripts.
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, Just to inform you that we've released the next beta of the upcoming 9.2 version with support for ?, :var, &var and :{var}. Please give it a try: http://www.dbvis.com/download/beta/ Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
How is the parameter substitution supposed to work with the :var syntax (PostgreSQL)? If I have "select * from customer where customer_hash = :id;", v92.2329 prompts with a grid of key, name, value with "id" in the value. Only the value column is editable, and replacing the string "id" with an integer results in: "[SELECT - 0 row(s), 0.000 secs] [Error Code: 0, SQL State: 22023] No value specified for parameter 2." Also note if the variable name is in quotes (:'dt'), valid SQL in Postgres, DbVis reports a syntax error. Edited by: MattK on Dec 1, 2014 4:55 PM
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Hi Matt, Oops, it looks like a last minute change didn't make it all the way through in the latest Beta. For this to work, you must also enable SQL Commander->Get Parameter Types via JDBC. Note that this is a work-around and should not be needed in the final version. Please let us know how it goes. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
That option was checked on my system (Mac 10.10). I unchecked it, then re-enabled it as a test and still receive the error.
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, What version of the JDBC driver are you using? Can you please enable debug in Tools->Debug Window (check Debug DbVisualizer only) and re-run the query? Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
Using the driver supplied in the app bundle, running the query "select * from customer where customer_hash = :id;" 11:54:01 [DEBUG pool-4-thread-5 C.ā] RootConnection: Jdbc4Connection.createStatement() 11:54:01 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.isValid] Executing connection validation statement: 'select 1'. Physical connection 'RootConnection' for 'testserver [testdb]' 11:54:01 [DEBUG pool-4-thread-5 B.ā] RootConnection: Jdbc4Statement.executeQuery("select 1") Extracting variables... Found 0 variables Extracting unnamed markers... Found 0 unnamed markers Extracting named markers... Found 1 named markers Extracting/preparing named markers... 11:54:01 [DEBUG pool-4-thread-5 C.ā] RootConnection: Jdbc4Connection.setCatalog("testdb") 11:54:01 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.getColumn] executing "show search_path" 11:54:01 [DEBUG pool-4-thread-5 C.ā] RootConnection: Jdbc4Connection.createStatement() 11:54:01 [DEBUG pool-4-thread-5 B.ā] RootConnection: Jdbc4Statement.executeQuery("show search_path") 11:54:01 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.getColumn] executing "show search_path" 11:54:01 [DEBUG pool-4-thread-5 C.ā] RootConnection: Jdbc4Connection.createStatement() 11:54:01 [DEBUG pool-4-thread-5 B.ā] RootConnection: Jdbc4Statement.executeQuery("show search_path") 11:54:01 [DEBUG pool-4-thread-5 C.ā] RootConnection: Jdbc4Connection.prepareStatement("select * from customer where customer_hash = :id") 11:54:01 [DEBUG ExecutorRunner-pool-3-thread-5 N.ā] Processing parameter markers org.postgresql.util.PSQLException: ERROR: syntax error at or near ":" Position: 46 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc3.AbstractJdbc3Statement.getParameterMetaData(AbstractJdbc3Statement.java:412) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at com.onseven.dbvis.h.B.B.ā(Z:2603) at com.onseven.dbvis.h.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) 11:54:01 [DEBUG pool-4-thread-5 C.ā] RootConnection: Jdbc4Connection.setCatalog("testdb") 11:54:01 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.getColumn] executing "show search_path" 11:54:01 [DEBUG pool-4-thread-5 C.ā] RootConnection: Jdbc4Connection.createStatement() 11:54:01 [DEBUG pool-4-thread-5 B.ā] RootConnection: Jdbc4Statement.executeQuery("show search_path") Found 0 named JDBC markers 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 H.ā] Creating new connection: DefaultEditor39 for testserver [testdb] 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 G.ij] Connecting: testserver [testdb] 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 L.ā] Loading class using dynamic ClassLoader: org.postgresql.Driver 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 L.ā] Loading class using dynamic ClassLoader: org.postgresql.Driver 11:54:06 [DEBUG pool-4-thread-5 D.ā] DefaultEditor39: Driver.acceptsURL("jdbc:postgresql://127.0.0.1:63789/testdb") 11:54:06 [DEBUG pool-4-thread-5 D.ā] DefaultEditor39: Driver.connect("jdbc:postgresql://127.0.0.1:63789/testdb", {user=********, password=*******}) 11:54:06 [DEBUG pool-4-thread-5 C.ā] DefaultEditor39: Jdbc4Connection.setAutoCommit(true) 11:54:06 [DEBUG pool-4-thread-5 E.ā] RootConnection: Jdbc4DatabaseMetaData.getDatabaseMajorVersion() 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 G.Ć] Using facade: postgresql for testserver [testdb] 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.getColumn] executing "show search_path" 11:54:06 [DEBUG pool-4-thread-5 C.ā] DefaultEditor39: Jdbc4Connection.createStatement() 11:54:06 [DEBUG pool-4-thread-5 B.ā] DefaultEditor39: Jdbc4Statement.executeQuery("show search_path") 11:54:06 [DEBUG pool-4-thread-5 E.ā] DefaultEditor39: Jdbc4DatabaseMetaData.getDatabaseProductName() 11:54:06 [DEBUG pool-4-thread-5 E.ā] DefaultEditor39: Jdbc4DatabaseMetaData.getDatabaseProductVersion() 11:54:06 [DEBUG pool-4-thread-5 E.ā] DefaultEditor39: Jdbc4DatabaseMetaData.getDriverName() 11:54:06 [DEBUG pool-4-thread-5 E.ā] DefaultEditor39: Jdbc4DatabaseMetaData.getDriverVersion() 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.getColumn] executing "show search_path" 11:54:06 [DEBUG pool-4-thread-5 C.ā] DefaultEditor39: Jdbc4Connection.createStatement() 11:54:06 [DEBUG pool-4-thread-5 B.ā] DefaultEditor39: Jdbc4Statement.executeQuery("show search_path") 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 J.execute] Executing... 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 J.setCurrentCatalog] DbConnection='testserver [testdb]' Catalog='testdb' Schema='null' NewCatalog='testdb' 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.getColumn] executing "show search_path" 11:54:06 [DEBUG pool-4-thread-5 C.ā] DefaultEditor39: Jdbc4Connection.createStatement() 11:54:06 [DEBUG pool-4-thread-5 B.ā] DefaultEditor39: Jdbc4Statement.executeQuery("show search_path") 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 J.setCurrentSchema] DbConnection='testserver [testdb]' Catalog='null' Schema='public' NewSchema='public' 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.getColumn] executing "show search_path" 11:54:06 [DEBUG pool-4-thread-5 C.ā] DefaultEditor39: Jdbc4Connection.createStatement() 11:54:06 [DEBUG pool-4-thread-5 B.ā] DefaultEditor39: Jdbc4Statement.executeQuery("show search_path") 11:54:06 [DEBUG pool-4-thread-5 C.ā] DefaultEditor39: Jdbc4Connection.prepareStatement("?s?e?l?e?c?t? ?*? ?f?r?o?m? ?c?u?s?t?o?m?e?r? ?w?h?e?r?e? ?c?u?s?t?o?m?e?r?_?h?a?s?h? ?=? ?:?i?d?") 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 O.ā] index=[1] name=[] value=[1] type=[Boolean] options=[mustbind] 11:54:06 [DEBUG pool-4-thread-5 B.ā] DefaultEditor39: Jdbc4PreparedStatement.setBoolean(1, true) 11:54:06 [DEBUG pool-4-thread-5 B.ā] DefaultEditor39: Jdbc4PreparedStatement.execute() 11:54:06 [DEBUG pool-4-thread-5 B.ā] DefaultEditor39: EXCEPTION -> org.postgresql.util.PSQLException: No value specified for parameter 2. org.postgresql.util.PSQLException: No value specified for parameter 2. at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:223) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:244) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:412) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at com.onseven.dbvis.h.B.B.ā(Z:2603) at com.onseven.dbvis.h.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) 11:54:06 [DEBUG ExecutorRunner-pool-3-thread-5 AbstractFacade.getColumn] executing "show search_path" 11:54:06 [DEBUG pool-4-thread-5 C.ā] DefaultEditor39: Jdbc4Connection.createStatement() 11:54:06 [DEBUG pool-4-thread-5 B.ā] DefaultEditor39: Jdbc4Statement.executeQuery("show search_path")
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Hi Matt, What is the enable state for these in the SQL Commander menu: - Parameterized SQL - Get Parameter Types via JDBC When you run the SQL the value prompter window should be displayed. What is listed in the Name column? What value do you specify and what is the Type? A screenshot would be great. (Initially you said "id" was displayed in the Name column but I need to double-check this as ":id" should be displayed). Best Regards Roger
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, We've just released 92.2335 with a few fixes. It would be great if you can give it a try since your latest finding is stil a mystery. My previous post above would also be great to see answered. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]
[Attachment has been removed.]

Re: DBs supporting named parameter markers?
Both are checked, and screenshots of the SQLCommander options, run dialog, and driver manager window are attached. The problem still occurs on v92.2335: Product: DbVisualizer Pro 92.2335 [Build #2335] [BETA VERSION] OS: Mac OS X OS Version: 10.10.1 OS Arch: x86_64 Java Version: 1.7.0_71 Java VM: Java HotSpot(TM) 64-Bit Server VM Java Vendor: Oracle Corporation
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, Thanks! Strangely the Name field is blank. It should show :id and the value should be empty at first display. We'll try find out what is happening. Regards Roger
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, Which of the execute actions in the SQL Commander menu are you using? (Execute, Execute Current, Execute Buffer?) What is the exact content in the editor when you run the script? Is the script loaded from file? It would great if you can mail your /Users//.dbvis/config70/dbvis.xml file to support@dbvis.com as it might be some setting that cause the problem. Don't post it here. I would really like to find out what is going on... Thanks! Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
> Which of the execute actions in the SQL Commander menu are you using? (Execute, Execute Current, Execute Buffer?) I have tried Execute and Execute Buffer with the same results. > Is the script loaded from file? No. Entered directly. > What is the exact content in the editor when you run the script? select * from d_customer where customer_id = :id; resulting in: ... Physical database connection acquired for: bi-app-prod [hips] 12:22:10 [SELECT - 0 row(s), 0.000 secs] [Error Code: 0, SQL State: 22023] No value specified for parameter 2. ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors] I will email you the config file. Edited by: MattK on Dec 8, 2014 12:21 PM