Could not load record: Incompatible types in expression

Classic Servoy
1

Hi,

I´m really new to Servoy.

I am working with RM/COBOL indexed files using Relativity JDBC Driver. Relativity is based on Simba. When the primary key is alphanumeric everything goes fine with Servoy but when the key is integer I receive the error Could not load record because of Incompatible types.

Could not load record

com.servoy.j2db.dataprocessing.DataException: [Relativity JDBC Driver][SimbaLNA][Liant][Relativity Server]Incompatible types in expression.

Is this something related to Servoy or to the JDBC Driver?

Best regards,

Juan Manuel Urraburu
ProRM
www.prorm.com

2

I have followed this issue with Liant´s Tech Support (Micro Focus) and as far as they can tell, this is an issue with Servoy. It is trying to perform a SQLBindParameter on the column OfOrderNumber, which is a SQL Integer. However, it is trying to bind it as a SQL Char.

Is there any way to control this behavior in Servoy?

Best regards,

3

Juan,

If you look at the table in the table editor, what is the type of the column that gives the issue?
if you turn tracing on, start servoy and touch the table (open table editor for instance) you should see a log statement like this what shows what we get from the driver

Column name % type % lenght % nullable %

Rob

4

Thanks Rob,

The data is comming as Integer and that´s fine.
2008-09-29 14:12:57,250 INFO [AWT-EventQueue-0] com.servoy.j2db.util.Debug - questiondata[0]= 107 ,type: java.lang.Integer

Then Servoy does the SELECT and the error is returned:

2008-09-29 14:12:57,250 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - select OrderNumber, Producto, Quantity, OrderValue from PedidosLineas where OrderNumber in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
java.sql.SQLException: [Relativity JDBC Driver][SimbaLNA][Liant][Relativity Server]Dynamic parameter #1 type mismatch.

Regards,

5

Juan,

Servoy sees the pk as an integer which it is as I understand from your post, but the driver seems not to accept numerical values.

What happens if you try another query tool, for instance SQuirreL SQL Client.
Can you try a select on numerical pk?

Rob

6

Hi Rob,

What SQL can I try into SQuirreL SQL Client in order to know if there is a problem with my Relativity Driver or nor? Standard SELECT works fine.

Thanks,

7

Juan,

The problem is probably with the setObject call, try this little java program.
Compile it and run it (add the driver to the class path), I expect that the same error occurs.

If it does you can go to the driver vendor, this is just standard jdbc that should work with the driver.

Rob

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBTest
{
        public static void main(String[] args) throws Exception
        {
                // same as in server definition
                String user = "theuser";
                String password = "secret";
                String url = "jdbc:the.url";
                String driver = "the.driver";

                Class.forName(driver);

                Connection connection = DriverManager.getConnection(url, user, password);

                PreparedStatement ps = connection.prepareStatement("select OrderNumber, Producto, Quantity, OrderValue from PedidosLineas where OrderNumber in (?, ?)");
                ps.setObject(1, new Integer(42));
                ps.setObject(2, new Integer(77));
                ResultSet rs = ps.executeQuery();
                while (rs.next())
                {
                        System.out.println(rs.getInt(1));
                }
                rs.close();
                ps.close();
                connection.close();
        }
}
8

Hi Rob,

I have news from Liant’s Technical Support about this issue: The problem is in SQLBindParameter that Servoy is trying to do:

Below is the SQLBindParameter that we are getting from Servoy:

SQLBindParameter(0x00bc517c, 1, 1, 1, 1, 3, 0, 0x00bc62cc, 4, 0x00bc5fba)

Here is the argument list for SQLBindParameter from Microsoft:

SQLRETURN SQLBindParameter(
SQLHSTMT StatementHandle,
SQLUSMALLINT ParameterNumber,
SQLSMALLINT InputOutputType,
SQLSMALLINT ValueType,
SQLSMALLINT ParameterType,
SQLULEN ColumnSize,
SQLSMALLINT DecimalDigits,
SQLPOINTER ParameterValuePtr,
SQLINTEGER BufferLength,
SQLLEN * StrLen_or_IndPtr);

In our case the fifth argument “ParameterType” is set to 1. A “1” is a SQL_CHAR. This is incorrect since the column that you are binding to is actually a “4” or SQL_INTEGER.

Below is how the SQLBindParameter should look:

SQLBindParameter(0x00af66c4, 1, 1, 1, 4, 10, 0, 0x00af0ff4, 0, 0x0079dfda)

**They obtained this from Microsoft’s ODBCTest.

Why Servoy is trying to bind a SQL_CHAR after receiving info from the driver that it is an Integer?

Best regards,

9

Juan,

Servoy speaks jdbc, not odbc, so we do not call SQLBindParameter directly, the jdbc to odbc bridge does.
Does the little program i posted produce the same error?

Rob

10

Hi Rob,

Yes, your sample returns the same error:

C:\Archivos de programa\Java\jdk1.6.0_07\bin>java DBTest
Exception in thread “main” java.sql.SQLException: [Relativity JDBC Driver][Simba
LNA][Liant][Relativity Server]Dynamic parameter #1 type mismatch.
at relativity.jdbc.PackBuffer.getSQLException(Unknown Source)
at relativity.jdbc.RPCInterface.handleError(Unknown Source)
at relativity.jdbc.RPCInterface.SQLExecuteMP(Unknown Source)
at relativity.jdbc.RelativityPreparedStatement.execute(Unknown Source)
at relativity.jdbc.RelativityPreparedStatement.executeQuery(Unknown Sour
ce)
at DBTest.main(DBTest.java:24)

11

Hi,

I received new information from the JDBC Driver provider:

I took a look at the Java API documentation and found the following information:

setObject

void setObject(int parameterIndex,
Object x)
throws SQLException

Sets the value of the designated parameter using the given object. The second parameter must be of type Object; therefore, the java.lang equivalent objects should be used for built-in types.

The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database.

Note that this method may be used to pass datatabase- specific abstract data types, by using a driver-specific Java type. If the object is of a class implementing the interface SQLData, the JDBC driver should call the method SQLData.writeSQL to write it to the SQL data stream. If, on the other hand, the object is of a class implementing Ref, Blob, Clob, NClob, Struct, java.net.URL, RowId, SQLXML or Array, the driver should pass it to the database as a value of the corresponding SQL type.

Note: Not all databases allow for a non-typed Null to be sent to the backend. For maximum portability, the setNull or the setObject(int parameterIndex, Object x, int sqlType) method should be used instead of setObject(int parameterIndex, Object x).

Note: This method throws an exception if there is an ambiguity, for example, if the object is of a class implementing more than one of the interfaces named above.

Parameters:
parameterIndex - the first parameter is 1, the second is 2, …
x - the object containing the input parameter value
Throws:
SQLException - if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs; this method is called on a closed PreparedStatement or the type of the given object is ambiguous

According to this documentation, we should be mapping all Java Data Types to SQL Data Types on a call to setObject. It seems that the Relativity JDBC driver is not handling this properly. I have submitted a Defect ticket (#3288) to our database regarding this issue.

As a workaround, you can explicitly state the sqlType for setObject by using the following format (I tested this and it works):

setObject(int parameterIndex, Object x, int sqlType)

Best regards,