Some refinements to getTables() and getColumns().
Add or replace the following functions in src/org/sqlite/MetaData.java:
public static String join(String[] arr) {
int len = arr.length;
if (len == 0) return "";
String s = "'";
for (int i = 0; i < len; ++i) {
if (i != 0) s += "','";
s += arr[i];
}
s += "'";
return s;
}
public synchronized ResultSet getTables(String c, String s,
String t, String[] types) throws SQLException {
checkOpen();
if (getTables == null) {
// TODO: perhaps return "GLOBAL TEMPORARY" for temp tables
getTables = conn.prepareStatement(
"select"
+ " null as TABLE_CAT,"
+ " null as TABLE_SCHEM,"
+ " upper(name) as TABLE_NAME,"
+ " upper(type) as TABLE_TYPE,"
+ " null as REMARKS,"
+ " null as TYPE_CAT,"
+ " null as TYPE_SCHEM,"
+ " null as TYPE_NAME,"
+ " null as SELF_REFERENCING_COL_NAME,"
+ " null as REF_GENERATION"
+ " from (select name, type from sqlite_master union
all"
+ " select name, type from sqlite_temp_master)"
+ " where TABLE_NAME like upper(?)"
+ " and TABLE_TYPE in (" + join(types).toUpperCase() +
");"
);
}
getTables.clearParameters();
getTables.setString(1, t == null || "".equals(t) ? "%" : t);
return getTables.executeQuery();
}
public ResultSet getColumns(String c, String s, String tbl, String
colPat)
throws SQLException {
Statement stat = conn.createStatement();
ResultSet rs;
String sql;
checkOpen();
if (getColumnsTblName == null)
getColumnsTblName = conn.prepareStatement(
"select tbl_name from sqlite_master where tbl_name like
?;");
// determine exact table name
getColumnsTblName.setString(1, tbl);
rs = getColumnsTblName.executeQuery();
if (!rs.next()) return null;
tbl = rs.getString(1);
rs.close();
sql = "select "
+ "null as TABLE_CAT, "
+ "null as TABLE_SCHEM, "
+ "'" + escape(tbl) + "' as TABLE_NAME, "
+ "cn as COLUMN_NAME, "
+ "-1 as DATA_TYPE, " // TODO
+ "tn as TYPE_NAME, "
+ "2000000000 as COLUMN_SIZE, " // TODO
+ "2000000000 as BUFFER_LENGTH, "
+ "10 as DECIMAL_DIGITS, "
+ "10 as NUM_PREC_RADIX, "
+ "colnullable as NULLABLE, "
+ "null as REMARKS, "
+ "null as COLUMN_DEF, "
+ "0 as SQL_DATA_TYPE, "
+ "0 as SQL_DATETIME_SUB, "
+ "2000000000 as CHAR_OCTET_LENGTH, "
+ "ordpos as ORDINAL_POSITION, " // TODO
+ "(case colnullable when 0 then 'N' when 1 then 'Y' else
'' end)"
+ " as IS_NULLABLE, " // TODO
+ "null as SCOPE_CATLOG, "
+ "null as SCOPE_SCHEMA, "
+ "null as SCOPE_TABLE, "
+ "null as SOURCE_DATA_TYPE from (";
// the command "pragma table_info('tablename')" does not embed
// like a normal select statement so we must extract the
information
// and then build a resultset from unioned select statements
rs = stat.executeQuery("pragma table_info ("+tbl+");");
boolean aColumnFound = false;
for (int ordpos=1; rs.next(); ordpos++) {
String colName = rs.getString(2);
String colType = rs.getString(3);
if (colType == null) colType = "TEXT";
String colNotNull = rs.getString(4);
int colNullable = 2;
if (colNotNull != null) {
colNullable = colNotNull.equals("0") ? 1 : 0;
}
if (aColumnFound == true) sql += " union all ";
aColumnFound = true;
sql += "select " + ordpos + " as ordpos, "
+ colNullable + " as colnullable, '"
+ escape(colName) + "' as cn, '"
+ escape(colType) + "' as tn";
if (colPat != null) {
sql += " where upper(cn) like upper('" + escape(colPat)
+ "')";
}
}
sql += aColumnFound ? ");" :
"select null as ordpos, null as colnullable, "
+ "null as cn, null as tn) limit 0;";
rs.close();
return stat.executeQuery(sql);
}
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"SQLiteJDBC" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups-beta.google.com/group/sqlitejdbc?hl=en
-~----------~----~----~----~------~----~------~--~---
