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. The original author name has been removed]

Re: DBs supporting named parameter markers?
Also note that this returns the expected rows: > select * from d_customer where customer_id = '1'; But while this appears to parse and complete without error, it returns nothing with a value of '1' supplied, including the single quotes: > select * from d_customer where customer_id = ?; In that second query, the parameter markers dialog shows "Parameter 1" in the Name column, unlike when I use the ":var" syntax, but since I am not getting any rows back, it appears as though the values are not getting passed. The parameter markers dialog also shows this bit of text: "1: Parameter 1  text Allow NULL JDBC: N/A, Java: String"
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, Thanks for your help on this and thanks for the dbvis.xml file. Problem seem to be a setting in Tool Properties and the General / Variables category: Variable Delimiter is set to a single colon (:) and that is cause the bad parsing. I see also that Variable Identifier Prefix is ${ and Suffix is }. Default for these are ${ and }$. Click Defaults in the lower left corner in the Tool Properties window and then "Revert Selected" when prompted. The OK out of the tool properties and re-run the SQL. Now it *should* run fine. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
Those settings were entered in prior versions to work with Postgres scripts - perhaps not the best approach because they made scripts that worked with DbVis but not psql. Reverting the settings does work for a script such as: > select * from d_customer where customer_id = :id; but not > select * from d_customer where customer_id = :'id'; which is valid in PostgreSQL when a type cast is not specified in the SQL That said "select * from d_customer where customer_id = :id" parses and executed without error, but returns and empty row set, where this returns data as expected: > select * from d_customer where customer_id = '1'; So something is still not populating correctly. Its also worth noting that queries with parameter markers generate errors on Execute Explain Plan: > [SELECT - 0 row(s), 0.000 secs] Failed to fetch explain result. ERROR: syntax error at or near ":"
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, > {quote:title=MattK wrote:}{quote} > but not > > > select * from d_customer where customer_id = :'id'; > > which is valid in PostgreSQL when a type cast is not specified in the SQL Currently :x, :{x} and &x is supported. We'll look into :'x' as well. > That said "select * from d_customer where customer_id = :id" parses and executed without error, but returns and empty row set, where this returns data as expected: > > > select * from d_customer where customer_id = '1'; > > So something is still not populating correctly. Can you please enable debug of DbVisualizer and run this? > Its also worth noting that queries with parameter markers generate errors on Execute Explain Plan: > > > [SELECT - 0 row(s), 0.000 secs] Failed to fetch explain result. ERROR: syntax error at or near ":" We'll look into this as well. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
With no parameters and data returned as expected: > select * from d_customer where customer_id = '1'; 15:41:13 [DEBUG pool-4-thread-11 C.ā] RootConnection: Jdbc4Connection.createStatement() 15:41:13 [DEBUG ExecutorRunner-pool-3-thread-7 AbstractFacade.isValid] Executing connection validation statement: 'select 1'. Physical connection 'RootConnection' for 'bi-app-prod [hips]' 15:41:13 [DEBUG pool-4-thread-11 B.ā] RootConnection: Jdbc4Statement.executeQuery("select 1") 15:41:13 [DEBUG pool-4-thread-11 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:41:13 [DEBUG ExecutorRunner-pool-3-thread-7 AbstractFacade.isValid] Executing connection validation statement: 'select 1'. Physical connection 'DefaultEditor47' for 'bi-app-prod [hips]' 15:41:13 [DEBUG pool-4-thread-11 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("select 1") 15:41:13 [DEBUG ExecutorRunner-pool-3-thread-7 AbstractFacade.getColumn] executing "show search_path" 15:41:13 [DEBUG pool-4-thread-11 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:41:13 [DEBUG pool-4-thread-11 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") 15:41:13 [DEBUG ExecutorRunner-pool-3-thread-7 J.execute] Executing... 15:41:13 [DEBUG ExecutorRunner-pool-3-thread-7 J.setCurrentCatalog] DbConnection='bi-app-prod [hips]' Catalog='hwdw' Schema='null' NewCatalog='hwdw' 15:41:13 [DEBUG ExecutorRunner-pool-3-thread-7 AbstractFacade.getColumn] executing "show search_path" 15:41:13 [DEBUG pool-4-thread-11 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:41:13 [DEBUG pool-4-thread-11 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") 15:41:13 [DEBUG ExecutorRunner-pool-3-thread-7 J.setCurrentSchema] DbConnection='bi-app-prod [hips]' Catalog='null' Schema='hwdw' NewSchema='hwdw' 15:41:13 [DEBUG ExecutorRunner-pool-3-thread-7 AbstractFacade.getColumn] executing "show search_path" 15:41:13 [DEBUG pool-4-thread-11 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:41:13 [DEBUG pool-4-thread-11 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") 15:41:14 [DEBUG pool-4-thread-11 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:41:14 [DEBUG pool-4-thread-11 B.ā] DefaultEditor47: Jdbc4Statement.execute("select * from d_customer where customer_id = '1'") 15:41:14 [DEBUG ExecutorRunner-pool-3-thread-7 AbstractFacade.getColumn] executing "show search_path" 15:41:14 [DEBUG pool-4-thread-11 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:41:14 [DEBUG pool-4-thread-11 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") 15:41:14 [DEBUG ExecutorRunner-pool-3-thread-7 N.ě] DbConnection='bi-app-prod [hips]' Catalog='hwdw' Schema='hwdw' Table='d_customer' Column='%' 15:41:14 [DEBUG pool-4-thread-11 E.ā] DefaultEditor47: Jdbc4DatabaseMetaData.getColumns("hwdw", "hwdw", "d_customer", "%") 15:41:14 [DEBUG ExecutorRunner-pool-3-thread-7 AbstractFacade.getColumn] executing "show search_path" 15:41:14 [DEBUG pool-4-thread-11 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:41:14 [DEBUG pool-4-thread-11 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") 15:41:15 [DEBUG ExecutorRunner-pool-3-thread-7 AbstractFacade.getColumn] executing "show search_path" 15:41:15 [DEBUG pool-4-thread-11 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:41:15 [DEBUG pool-4-thread-11 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") With a parameter, and a syntax error logged, but no warnings in the tool itself: > select * from d_customer where customer_id = :id; 15:42:24 [DEBUG pool-4-thread-12 C.ā] RootConnection: Jdbc4Connection.createStatement() 15:42:24 [DEBUG ExecutorRunner-pool-3-thread-8 AbstractFacade.isValid] Executing connection validation statement: 'select 1'. Physical connection 'RootConnection' for 'bi-app-prod [hips]' 15:42:24 [DEBUG pool-4-thread-12 B.ā] RootConnection: Jdbc4Statement.executeQuery("select 1") 15:42:24 [DEBUG ExecutorRunner-pool-3-thread-8 J.execute] Found 1 named parameter markers 15:42:24 [DEBUG pool-4-thread-12 C.ā] RootConnection: Jdbc4Connection.setCatalog("hwdw") 15:42:24 [DEBUG ExecutorRunner-pool-3-thread-8 AbstractFacade.getColumn] executing "show search_path" 15:42:24 [DEBUG pool-4-thread-12 C.ā] RootConnection: Jdbc4Connection.createStatement() 15:42:24 [DEBUG pool-4-thread-12 B.ā] RootConnection: Jdbc4Statement.executeQuery("show search_path") 15:42:25 [DEBUG ExecutorRunner-pool-3-thread-8 AbstractFacade.getColumn] executing "show search_path" 15:42:25 [DEBUG pool-4-thread-12 C.ā] RootConnection: Jdbc4Connection.createStatement() 15:42:25 [DEBUG pool-4-thread-12 B.ā] RootConnection: Jdbc4Statement.executeQuery("show search_path") 15:42:25 [DEBUG pool-4-thread-12 C.ā] RootConnection: Jdbc4Connection.prepareStatement("select * from d_customer where customer_id = :id") 15:42:25 [DEBUG ExecutorRunner-pool-3-thread-8 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) 15:42:25 [DEBUG pool-4-thread-12 C.ā] RootConnection: Jdbc4Connection.setCatalog("hwdw") 15:42:25 [DEBUG ExecutorRunner-pool-3-thread-8 AbstractFacade.getColumn] executing "show search_path" 15:42:25 [DEBUG pool-4-thread-12 C.ā] RootConnection: Jdbc4Connection.createStatement() 15:42:25 [DEBUG pool-4-thread-12 B.ā] RootConnection: Jdbc4Statement.executeQuery("show search_path") 15:42:27 [DEBUG pool-4-thread-12 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:42:27 [DEBUG ExecutorRunner-pool-3-thread-8 AbstractFacade.isValid] Executing connection validation statement: 'select 1'. Physical connection 'DefaultEditor47' for 'bi-app-prod [hips]' 15:42:27 [DEBUG pool-4-thread-12 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("select 1") 15:42:27 [DEBUG ExecutorRunner-pool-3-thread-8 AbstractFacade.getColumn] executing "show search_path" 15:42:27 [DEBUG pool-4-thread-12 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:42:27 [DEBUG pool-4-thread-12 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") 15:42:27 [DEBUG ExecutorRunner-pool-3-thread-8 J.execute] Executing... 15:42:27 [DEBUG ExecutorRunner-pool-3-thread-8 J.setCurrentCatalog] DbConnection='bi-app-prod [hips]' Catalog='hwdw' Schema='null' NewCatalog='hwdw' 15:42:27 [DEBUG ExecutorRunner-pool-3-thread-8 AbstractFacade.getColumn] executing "show search_path" 15:42:27 [DEBUG pool-4-thread-12 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:42:27 [DEBUG pool-4-thread-12 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") 15:42:27 [DEBUG ExecutorRunner-pool-3-thread-8 J.setCurrentSchema] DbConnection='bi-app-prod [hips]' Catalog='null' Schema='hwdw' NewSchema='hwdw' 15:42:27 [DEBUG ExecutorRunner-pool-3-thread-8 AbstractFacade.getColumn] executing "show search_path" 15:42:27 [DEBUG pool-4-thread-12 C.ā] DefaultEditor47: Jdbc4Connection.createStatement() 15:42:27 [DEBUG pool-4-thread-12 B.ā] DefaultEditor47: Jdbc4Statement.executeQuery("show search_path") 15:42:27 [DEBUG pool-4-thread-12 C.ā] DefaultEditor47: Jdbc4Connection.prepareStatement("select * from d_customer where customer_id = ?") 15:42:27 [DEBUG ExecutorRunner-pool-3-thread-8 O.ā] index=[1] name=[:id] value=['1'] type=[String] options=[mustbind] 15:42:27 [DEBUG pool-4-thread-12 B.ā] DefaultEditor47: Jdbc4PreparedStatement.setString(1, "'1'") 15:42:27 [DEBUG pool-4-thread-12 B.ā] DefaultEditor47: Jdbc4PreparedStatement.execute() Edited by: MattK on Dec 8, 2014 3:41 PM
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Thanks Matt. Does it work better if you don't specify quotes in the value prompt window for the following? select * from d_customer where customer_id = :id; Just enter the value and make sure the Type match the type of the column in the table. Type is automatically set based on what Value is entered but since you use to enter 1 it will set it to Integer and probably fail during execution. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
Setting the datatype was an option I did not realize was selectable in that column of the dialog. I was using quotes to have the UI detect the string type. Specifying the value without quotes, and selecting the string type does run the query as desired. Execution plan does not work, because the error occurs before the parameter dialog is presented.
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Great. Explain plan fail since PostgreSQL require that any parameters are bound properly before explaining the statement. Other databases are able to properly analyze the plan for a query that contain markers without having the values. We will fix this in the next version. Other than explain we will look into this parameter format: [pre]:'x'[/pre] as previously promised. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: DBs supporting named parameter markers?
> Other than explain we will look into this parameter format: > :'x' > as previously promised. And perhaps the :'x'::datatype format, which does work in the current beta without the quotes: > select * from d_customer where customer_id = :id::varchar; > > select * from d_customer where customer_id = :'id'::varchar;
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, I'll open a ticket for that format. Regards Roger
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, In the just released BETA 92.2339, :'x' is supported. Note that :'x'::datatype is on the future list. Regards Roger
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, Explain with parameter markers now also works. Regards Roger
[This reply is migrated from our old forums.]

Re: DBs supporting named parameter markers?
Matt, DbVisualizer 9.2 has just been released including support for parameter markers as discussed in this thread. http://www.dbvis.com/doc/relnotes/?version=9.2&showtoc=false Regards Roger