Hi there,
I have db with table as
CREATE TABLE GasStats10s(GasStats10s_MaxValue REAL,GasStats10s_IntervalDuration
INTEGER,GasStats10s_DataCount INTEGER,GasStats10s_MinValue
REAL,GasStats10s_Value REAL,GasStats10s_DateTime INTEGER,GasStats10s_Source
UID,GasStats10s_Sum REAL,Class UID,DeleteLog UID,ID UID,Origin UID,TimeStamp
INTEGER, primary key (ID))
and
select statement
SELECT
t.GasStats10s_Value as val,
cast(hex(t.GasStats10s_Source) as text) as source,
cast(t.TimeStamp as integer) as timestamp
FROM GasStats10s t
WHERE t.TimeStamp >= ?
ORDER BY t.TimeStamp ASC
Preamble,
This issue came from very long path. Java is my primary language and I used
JDBC driver from https://github.com/xerial/sqlite-jdbc with embedded primary
SQLite C API code as JNI library mapped to SQLite C API release versions under
Apache NiFi.
Bug description.
Different behaviour of sqlite3_column_type and sqlite3_column_decltype
functions depends of result of select statement above. If SELECT statement
above returns more than 0 records, functions works perfect and return type of
columns for this statement, but if SELECT statement return 0 records, functions
for source columns return NULL.
I have wrote simple test program to proof that behaviour, one simple Java JDBC
program to open connection, execute SQL and get ResultSet, I can ask SQLite C
API to get columns types.
I used JDBC version 3.25.2 as SQLite 3.25.2, rebuild for 3.26.0 same results.
import java.sql.*;
public class Main {
public static void main(String[] args) {
String url = "jdbc:sqlite:<macOS path to file>";
try (Connection conn = DriverManager.getConnection(url)) {
System.out.println("Connection to SQLite has been
established.");
String query1 = "SELECT\n" +
" t.GasStats10s_Value as val,\n" +
" t.GasStats10s_Source as source,\n" +
" t.TimeStamp\n" +
"FROM GasStats10s t\n" +
"WHERE t.TimeStamp >= 0\n" +
"ORDER BY t.TimeStamp ASC\n" +
"LIMIT 50;";
String query2 = "SELECT\n" +
" t.GasStats10s_Value as val,\n" +
" t.GasStats10s_Source as source,\n" +
" t.TimeStamp\n" +
"FROM GasStats10s t\n" +
"WHERE t.TimeStamp >= 15446222410350000\n" +
"ORDER BY t.TimeStamp ASC\n" +
"LIMIT 50;";
try (Statement stmt = conn.createStatement())
{
ResultSet rs1 = stmt.executeQuery(query1);
ResultSetMetaData metaData1 = rs1.getMetaData();
System.out.println("Query 1");
System.out.println("Column 1 Type:" +
metaData1.getColumnType(1));
System.out.println("Column 2 Type:" +
metaData1.getColumnType(2));
System.out.println("Column 3 Type:" +
metaData1.getColumnType(3));
ResultSet rs2 = stmt.executeQuery(query2);
ResultSetMetaData metaData2 = rs2.getMetaData();
System.out.println("Query 2");
System.out.println("Column 1 Type:" +
metaData2.getColumnType(1));
System.out.println("Column 2 Type:" +
metaData2.getColumnType(2));
System.out.println("Column 3 Type:" +
metaData2.getColumnType(3));
} catch (SQLException e ) {
System.out.println(e.getMessage());
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
}
Result I got follow
Connection to SQLite has been established.
Query 1 (50 records)
Column 1 Type:7
Column 2 Type:2004
Column 3 Type:4
Query 2 (0 records)
Column 1 Type:7
Column 2 Type:0
Column 3 Type:4
I have spend a little bit time to dig this issue,
Primary method
org/sqlite/jdbc3/JDBC3ResultSet.java:741
/**
* @see java.sql.ResultSetMetaData#getColumnType(int)
*/
public int getColumnType(int col) throws SQLException {
String typeName = getColumnTypeName(col);
int valueType = getDatabase().column_type(stmt.pointer, checkCol(col));
next, calls in Java went to JNI native code directly to SQLite C API, on follow
methods
org.sqlite.core.NativeDB#column_type
org.sqlite.core.NativeDB#column_decltype
and respectfully to C counterpart
JNIEXPORT jint JNICALL Java_org_sqlite_core_NativeDB_column_1type(
JNIEnv *env, jobject this, jlong stmt, jint col)
{
if (!stmt)
{
throwex_stmt_finalized(env);
return SQLITE_MISUSE;
}
return sqlite3_column_type(toref(stmt), col);
}
JNIEXPORT jbyteArray JNICALL
Java_org_sqlite_core_NativeDB_column_1decltype_1utf8(
JNIEnv *env, jobject this, jlong stmt, jint col)
{
const char *str;
if (!stmt)
{
throwex_stmt_finalized(env);
return NULL;
}
str = (const char*) sqlite3_column_decltype(toref(stmt), col);
if (!str) return NULL;
return utf8BytesToJavaByteArray(env, str, strlen(str));
}
How I can resolve this issue? If you have any questions let me know.
Sincerely,
Dmitry Mashkov
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
