Statement.execute throws Syntax error (206/210)

[Helmut Leininger]

This was not the case in 200

Syntax error in SQL statement "create table persons (mstb_per_no int [*]identity not null, mstb_father int, mstb_mother int, mstb_last_edit date, mstb_dsid int, mstb_ref_id int, mstb_reference varchar(12), mstb_spoulast int, mstb_scbuff varchar(10), mstb_pbirth varchar(30), mstb_pdeath varchar(30), mstb_relate int, mstb_relatefo int, mstb_tt varchar(1), mstb_sex varchar(1), mstb_living varchar(1), mstb_birthorder varchar(2), mstb_multibirth varchar(1), mstb_adopted varchar(1), mstb_ance_int varchar(1), mstb_desc_int varchar(1))"; expected "ARRAY, INVISIBLE, VISIBLE, NOT, NULL, AS, DEFAULT, GENERATED, ON, NOT, NULL, AUTO_INCREMENT, DEFAULT, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, COMMENT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, AUTO_INCREMENT, ,, )"; SQL statement:

create table persons (mstb_per_no int identity not null, mstb_father int, mstb_mother int, mstb_last_edit date, mstb_dsid int, mstb_ref_id int, mstb_reference varchar(12), mstb_spoulast int, mstb_scbuff varchar(10), mstb_pbirth varchar(30), mstb_pdeath varchar(30), mstb_relate int, mstb_relatefo int, mstb_tt varchar(1), mstb_sex varchar(1), mstb_living varchar(1), mstb_birthorder varchar(2), mstb_multibirth varchar(1), mstb_adopted varchar(1), mstb_ance_int varchar(1), mstb_desc_int varchar(1)) [42001-210]

Sorry for possible double  posting (this is my first try in this group)

[Noel Grandin]

You can specify a column as either INT or IDENTITY but not both.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/f64c2b8d-9e77-4d40-acc5-649a5ebdf311n%40googlegroups.com.

[Evgenij Ryazanov]

Hello!

INT GENERATED BY DEFAULT AS IDENTITY should be used for identity column of INT (INTEGER) data type. Additional NOT NULL clause isn't needed, but may be specified. H2 intentionally supports this standard-compliant syntax in all compatibility modes to have possibility to use the same syntax in all of them.

INT IDENTITY is a syntax from SQL Server, H2 supports it only in MSSQLSERVER and LEGACY compatibility modes. You can't use in in any other mode.

https://h2database.com/html/features.html#compatibility

LEGAGY mode is based on REGULAR modes, but it is less restrictive and provides better compatibility with H2 1.4.200. You can use it if you can't change definition of your table.

[Andreas Reichel]

Greetings.

To my best knowledge, H2 database does not support Named Parameters. Furthermore, Oracle does not support Parameters in DDL Statements (not even in CTAS).

I have started writing a small library https://github.com/manticore-projects/MJdbcUtils in order to handle Named Parameter Statements nicely. Summary and examples are shown below.

It is in its very early stages and I will appreciate feedback and tests and are more than willing to incorporate interesting use cases.

I do use H2 as basis for the development, although I want to be RDBMS agnostic as possible.

Warm regards

Andreas

--

MJdbcUtils

Library supporting Named Parameters (e.g. :Customer_Id ) in Queries or DML/DDL statements.

Use case

When the RDBMS does not support Named Parameters directly, it will:

1) find any Named Parameter, 

2) replace it with an ordinary Positional Parameter ?, 

3) maintain a map between the Position and the Named Parameter 

4) provide methods for applying the Parameter Values and retrieving Parameter Type information.

It supports PreparedStatements with parameters and also rewriting/injecting SQL Statements for execution without parameters. Rewriting/injecting is useful for Oracle Databases, which do not allow parameters for DDL Statements (not even for the query block of CTAS).

Furthermore, MJdbcUtils makes it easy to build an UI Parameter Dialog based on the used Parameters and the Type Information.

Examples

Based on a Table Definition

CREATE TABLE test ( 
    a DECIMAL(3) PRIMARY KEY
    , b VARCHAR(128) NOT NULL
    , c DATE NOT NULL
    , d TIMESTAMP NOT NULL
    , e DECIMAL(23,5) NOT NULL 
    ) 

1) We can fill the table with a simple update

// DML statement with Named Parameters
String dmlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )";

// Helper function will fill our parameter map with values
Map<String, Object> parameters = toMap("a", 1, "b", "Test String", "c", new Date(), "d", new Date(), "e", "0.12345");

// Create a Prepared Statement, which holds our paramater mapping
MPreparedStatement st = new MPreparedStatement(conn, dmlStr);

// Execute our statement with the provided parameter values
Assertions.assertFalse( st.execute(parameters) );

2) We can fill table using Batch Updates

int maxRecords = 100;
int batchSize = 4;
String dmlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )";
Map<String, Object> parameters = toMap("a", 1, "b", "Test String", "c", new Date(), "d", new Date(), "e", "0.12345");

MPreparedStatement st = new MPreparedStatement(conn, dmlStr, batchSize);

for (int i=0; i < maxRecords; i++) {
    parameters.put("a", i);
    parameters.put("b", "Test String " + i);
    
    // submit a new set of parameter values and execute automatically after 4 records
    int[] results = st.addAndExecuteBatch(parameters);
}
// submit any outstanding records
st.executeBatch();
        

3) We can query our table

String qryStr = "SELECT Count(*) FROM test WHERE a = :a or b = :b";
Map<String, Object> parameters = toMap("a", 1, "b", "Test String", "c", new Date(), "d", new Date(), "e", "0.12345");
MPreparedStatement st = new MPreparedStatement(conn, qryStr);
 ResultSet rs = st.executeQuery(parameters);

4) We can rewrite our statement and inject the parameter values directly (useful for Oracle DDLs)

Date dateParameterValue = new Date();

HashMap<String, Object> parameters = new HashMap<>();
parameters.put("param1", "Test String");
parameters.put("param2", 2);
parameters.put("param3", dateParameterValue);

String sqlStr = "select :param1, :param2, :param3;";
String rewrittenSqlStr = MJdbcTools.rewriteStatementWithNamedParameters(sqlStr, parameters);

Assertions.assertEquals("SELECT 'Test String', 2, " + getSQLDateTimeStr(dateParameterValue), rewrittenSqlStr);

sqlStr = "UPDATE tableName SET a = :param1, b = :param2, c = :param3;";
rewrittenSqlStr = MJdbcTools.rewriteStatementWithNamedParameters(sqlStr, parameters);

Assertions.assertEquals("UPDATE tableName SET a = 'Test String', b = 2, c = " + getSQLDateTimeStr(dateParameterValue), rewrittenSqlStr);

5) We can retrieve the information about the used parameters for building a UI Dialog

String qryStr = "SELECT * FROM test WHERE d = :d and c = :c and b = :b and a = :a and e = :e";
MPreparedStatement st = new MPreparedStatement(conn, qryStr);
    
List<MNamedParameter> parameters = st.getNamedParametersByAppearance();

Output of the List:

INFO: Found Named Parameters:
D   java.sql.Timestamp
C   java.sql.Date
B   java.lang.String
A   java.math.BigDecimal
E   java.math.BigDecimal

[Helmut Leininger]

Thanks for your explanation. Obviously, syntax check has been more lenient in previous releases.