support "SELECT version()"

[GoogleCodeExporter]

This allows clients to enforce minimum version requirements.  Example usage on 
MySQL:

mysql> SELECT version() ;
+---------------+
| version()     |
+---------------+
| 5.5.24-55-log |
+---------------+
1 row in set (0.01 sec)

Original issue reported on code.google.com by arg...@gmail.com on 29 Jun 2012 at 5:30

[GoogleCodeExporter]

Added functionality in patch here:

http://gaul.org/files/h2_select_version.patch

Google Code will not let me attach my patch due to:

Issue attachment storage quota exceeded.

Original comment by arg...@gmail.com on 3 Sep 2012 at 9:20

[GoogleCodeExporter]

Hi,

Thanks! I have asked for increased storage quota. Anyway here the patch:

Index: h2/src/docsrc/help/help.csv
===================================================================
--- h2/src/docsrc/help/help.csv (revision 4356)
+++ h2/src/docsrc/help/help.csv (working copy)
@@ -3799,6 +3799,14 @@
 CURRENT_USER()
 "

+"Functions (System)","VERSION","
+VERSION()
+","
+Returns the H2 version as a String.
+","
+VERSION()
+"
+
 "System Tables","Information Schema","
 INFORMATION_SCHEMA
 ","
Index: h2/src/test/org/h2/test/db/TestFunctions.java
===================================================================
--- h2/src/test/org/h2/test/db/TestFunctions.java   (revision 4356)
+++ h2/src/test/org/h2/test/db/TestFunctions.java   (working copy)
@@ -29,6 +29,7 @@
 import java.util.UUID;
 import org.h2.api.AggregateFunction;
 import org.h2.constant.ErrorCode;
+import org.h2.engine.Constants;
 import org.h2.store.fs.FileUtils;
 import org.h2.test.TestBase;
 import org.h2.tools.SimpleResultSet;
@@ -54,6 +55,7 @@

     public void test() throws Exception {
         deleteDb("functions");
+        testVersion();
         testFunctionTable();
         testArrayParameters();
         testDefaultConnection();
@@ -78,6 +80,20 @@
         FileUtils.deleteRecursive(TEMP_DIR, true);
     }

+    private void testVersion() throws SQLException {
+        Connection conn = getConnection("functions");
+        Statement stat = conn.createStatement();
+        String query = "select version()";
+        ResultSet rs = stat.executeQuery(query);
+        assertTrue(rs.next());
+        String version = rs.getString(1);
+        assertEquals(Constants.getVersion(), version);
+        assertFalse(rs.next());
+        rs.close();
+        stat.close();
+        conn.close();
+    }
+
     private void testFunctionTable() throws SQLException {
         Connection conn = getConnection("functions");
         Statement stat = conn.createStatement();
Index: h2/src/main/org/h2/expression/Function.java
===================================================================
--- h2/src/main/org/h2/expression/Function.java (revision 4356)
+++ h2/src/main/org/h2/expression/Function.java (working copy)
@@ -26,6 +26,7 @@
 import org.h2.command.Command;
 import org.h2.command.Parser;
 import org.h2.constant.ErrorCode;
+import org.h2.engine.Constants;
 import org.h2.engine.Database;
 import org.h2.engine.Mode;
 import org.h2.engine.Session;
@@ -96,7 +97,8 @@
             CASE = 206, NEXTVAL = 207, CURRVAL = 208, ARRAY_GET = 209, CSVREAD = 210, CSVWRITE = 211,
             MEMORY_FREE = 212, MEMORY_USED = 213, LOCK_MODE = 214, SCHEMA = 215, SESSION_ID = 216, ARRAY_LENGTH = 217,
             LINK_SCHEMA = 218, GREATEST = 219, LEAST = 220, CANCEL_SESSION = 221, SET = 222, TABLE = 223, TABLE_DISTINCT = 224,
-            FILE_READ = 225, TRANSACTION_ID = 226, TRUNCATE_VALUE = 227, NVL2 
= 228, DECODE = 229, ARRAY_CONTAINS = 230;
+            FILE_READ = 225, TRANSACTION_ID = 226, TRUNCATE_VALUE = 227, NVL2 
= 228, DECODE = 229, ARRAY_CONTAINS = 230,
+            VERSION = 231;

     public static final int ROW_NUMBER = 300;

@@ -340,6 +342,7 @@
         addFunction("FILE_READ", FILE_READ, VAR_ARGS, Value.NULL, false, false, false);
         addFunctionNotDeterministic("TRANSACTION_ID", TRANSACTION_ID, 0, Value.STRING);
         addFunctionWithNull("DECODE", DECODE, VAR_ARGS, Value.NULL);
+        addFunction("VERSION", VERSION, 0, Value.STRING);

         // TableFunction
         addFunctionWithNull("TABLE", TABLE, VAR_ARGS, Value.RESULT_SET);
@@ -1094,6 +1097,9 @@
         case LPAD:
             result = ValueString.get(StringUtils.pad(v0.getString(), v1.getInt(), v2 == null ? null : v2.getString(), false));
             break;
+        case VERSION:
+            result = ValueString.get(Constants.getVersion());
+            break;
             // date
         case DATE_ADD:
             result = ValueTimestamp.get(dateadd(v0.getString(), v1.getInt(), v2.getTimestamp()));

Original comment by thomas.t...@gmail.com on 3 Sep 2012 at 9:28

[GoogleCodeExporter]

Reattaching patch.  Anything else needed to push this upstream?

Original comment by arg...@gmail.com on 25 Oct 2012 at 6:35

Attachments:

• h2_select_version.patch

[GoogleCodeExporter]

This issue was closed by revision r4550.

Original comment by noelgrandin on 12 Dec 2012 at 6:46

• Changed state: Fixed

[GoogleCodeExporter]

Unfortunately, I need to undo the change. The problem is that the PostgreSQL 
ODBC driver expects a function VERSION(), and it needs to return something 
starting with "PostgreSQL".

Original comment by thomas.t...@gmail.com on 14 Dec 2012 at 7:36

• Changed state: New

[GoogleCodeExporter]

Can we add the "PostgreSQL" prefix when MODE=PostgreSQL?

Original comment by g...@maginatics.com on 15 Dec 2012 at 12:31

[GoogleCodeExporter]

Instead, I suggest to create a user defined function when using the MySQL mode. 
Just like a user defined function is used in the PostgreSQL TCP server.

Original comment by thomas.t...@gmail.com on 15 Dec 2012 at 1:42

[GoogleCodeExporter]

In standard SQL you can use

SELECT CHARACTER_VALUE 
  FROM INFORMATION_SCHEMA.SQL_IMPLEMENTATION_INFO 
 WHERE IMPLEMENTATION_INFO_NAME='DBMS VERSION'

Original comment by ysang...@gmail.com on 26 Mar 2013 at 12:22

[GoogleCodeExporter]

Thanks! This seems to be the standard (even thought I don't know who actually 
does support it). I found more information at:

https://kb.askmonty.org/en/information_schemasql_implementation_info/

Patches are welcome!

Original comment by thomas.t...@gmail.com on 26 Mar 2013 at 6:06

[GoogleCodeExporter]

Fixed in revision 4718.

I committed this, changing the function to be called "H2VERSION".

This will prevent it from conflicting with our PostgreSQL ODBC driver stuff, 
but it allows the client to do version checking.

Original comment by noelgrandin on 2 Apr 2013 at 8:20

• Changed state: Fixed