CODEWITHSUNDEEP
javajdbcinformix
Cao Felix
Cao Felix

Reputation: 329

ROW_NUMBER() on informix DB shows BIGINT is not supported

I am trying to add row numbers to a sql query to get a return resultset, but the JDBC does not support BIGINT it says. I look up https://db.apache.org/derby/docs/10.9/ref/rreffuncrownumber.html and https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.jdbc_pg.doc/ids_jdbc_141.htm.

The code:

String query = new StringBuilder("SELECT ROW_NUMBER() OVER() AS id, * FROM "+tableName).toString();

Error:

[Informix JDBC Driver][Informix]The data type bigint is not supported for current client/server configuration.

The IBM solution tells you to use getBigSerial() to get the BIGINT after the insert. However, I want to find a way to be able to add some auto increment numbers when it queries the table without creating an actual column. Is there a way?

Upvotes: 0

Views: 1587

Answers (2)

Cao Felix
Cao Felix

Reputation: 329

Casting is good:

String query = "SELECT CAST(ROW_NUMBER() OVER() AS INT) AS id, * FROM "+tableName;

I test it with my informix database and it works.

Upvotes: 0

Michał Niklas
Michał Niklas

Reputation: 54342

You can cast it to VARCHAR in the query.

You can cast that String into BigInteger in Java code if you use it for something more than present it to the client.

Upvotes: 1

Related Questions