Start a new topic

Do not validate/parse statement

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

Hi, I am wondering if there is a way to disable parsing/validation of a query by dbVis before it executes it against the database? (almost the opposite of a question asked by Matt a few days ago!) The reason for this is that I want to execute a non-SQL (configuration) statement against our (Ingres) database - for example "set session read only" - this statement makes the session read only for an Ingres connection. If I write a custom Java program which establishes a JDBC connection to my database and then runs: stmt.executeUpdate("set session read only"); it works fine. However, I can't run this statement in dbVis, because it is being rejected as invalid. Is there any way round this? Thanks. Regards, Adrian

[This reply is migrated from our old forums.]

Re: Do not validate/parse statement
Adrian, DbVisualizer use the stmt.execute() call in JDBC to execute all SQLs. Seems that the Ingress driver don't accept the set read only command via execute(). What exact error do you get from the Ingress driver? An option if the Ingress driver supports it is to set the complete session in read-only mode. Do this in the in the Connection Tab, click the lower "Properties" tab. Locate "Driver Properties" category in the list. Now add a new row: java.sql.connection.setReadOnly = true Press Apply and re-connect. First verify that the JDBC driver is not reporting an error during connect, if it doesn't then try if the setting have any effect by doing a simple write operation such as updating a table or similar. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Do not validate/parse statement
Hi Roger, Thanks for the quick response. The error I'm getting is: *15:37:55 [SET - 0 row(s), 0.000 secs] [Error Code: 2500, SQL State: 42000] Syntax error on line 1. Last symbol read was: 'read'.* *set session read only;* *... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]* I've checked in a custom Java program using a JDBC connection, and the following code also works fine: *stmt = conn.createStatement();* *stmt.execute("set session read only");* Note that I am not using prepared statements. I turned on the debug in dbVisualiser and saw this: *15:37:55 [DEBUG] prepareStatement(set session read only)* so I guess it is? If so this could be the issue - is there a way to get it to directly execute the SQL without preparing it? I believe the driver does support the set read only as well, so the property suggestion you made will probably work anyway - thanks for that, I'll try that now. Cheers, Adrian
[This reply is migrated from our old forums. The original author name has been removed]

Re: Do not validate/parse statement
Hi, Just tried this in custom java client with a prepared statement, it definitely doesn't work! *pstmt = conn.prepareStatement("set session read only");* *pstmt.execute();* *com.ingres.gcf.util.SqlEx: Syntax error on line 1. Last symbol read was: 'read'.* * at com.ingres.gcf.jdbc.DrvObj.readError(Unknown Source)* * at com.ingres.gcf.jdbc.DrvObj.readResults(Unknown Source)* * at com.ingres.gcf.jdbc.DrvPrep.prepare(Unknown Source)* * at com.ingres.gcf.jdbc.DrvConn.createPrepStmt(Unknown Source)* * at com.ingres.gcf.jdbc.JdbcPrep.(Unknown Source)* * at com.ingres.gcf.jdbc.JdbcConn.createPrep(Unknown Source)* * at com.ingres.gcf.jdbc.JdbcConn.prepareStatement(Unknown Source)* Cheers, Adrian Edited by: Adrian on 15-Feb-2011 16:46
[This reply is migrated from our old forums.]

Re: Do not validate/parse statement
Adrian, Thanks for the test! Interestingly is that no other drivers we are working with have restrictions what SQLs can be prepared. Maybe the Ingres driver dev team can comment why their driver alarm an error rather than just ignoring the statement. I will register a ticket for this and we will look into a workaround. Regards Roger
[This reply is migrated from our old forums.]

Re: Do not validate/parse statement
Adrian, We are looking into this problem. One question I have is whether the SQL->Process Parameter Markers in SQL is enabled in the SQL main menu? If so, uncheck it and try again. Please let me know the progress. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Do not validate/parse statement
Hi Roger, Your update led me to realise I was using quite an old version of dbVisualiser - v5.1. So I downloaded the latest (v7.1.5) and have found that this issue does not exist in that version - i.e. I can run "set session read only" against my Ingres database without any issue, and it takes effect as expected. The "Process Parameter Markers..." setting doesn't seem to have any impact - everything works ok with it either on or off. Thanks for your help, and sorry for the time-wasting - I should have checked my version before posting a question! Regards, Adrian
[This reply is migrated from our old forums.]

Re: Do not validate/parse statement
Adrian, Thanks for the update. Regards Roger