Joe Wilson wrote:
> Long/short column names in result sets have always been unpredictable
> in sqlite - especially when joins, unions and subqueries are used.
> I can't see how you can implement this behavior outside of sqlite
> itself or without parsing and understanding SQL syntax and name
> resolution rules. Otherwise, you risk breaking working programs.
>
Hurray, I have found the solution of my original problem
<http://groups.google.com/group/sqlitejdbc/browse_thread/thread/555416d40c3d7fb7>.
If I use
Statement st = connection.createStatement();
*st.execute("PRAGMA full_column_names = 1");
st.execute("PRAGMA short_column_names = 0" );*
I can use
ResultSet rs = connection.createStatement().executeQuery(
"SELECT * FROM sqlitetest p1, sqlitetest p2 "
+ "WHERE p1.value = p2.value AND p1.id < p2.id");
while (rs.next()) {
int first = *rs.getInt("p1.id")*;
int second = *rs.getInt("p2.id")*;
System.out.println(first + " - " + second);
}
Why is it not too good? I must use
rs = connection.createStatement().executeQuery("SELECT * FROM
sqlitetest");
while (rs.next()) {
System.out.println(*rs.getInt("sqlitetest.id")*);
}
So it doesn't understand *rs.getInt("id")*;
But… I am currently happy!
Thank you for SQLite JDBC driver!
Regards,
Máté.
--~--~---------~--~----~------------~-------~--~----~
Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en
To unsubscribe, send email to [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
package sqliteTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author FARKAS, Máté
* @version 2008.05.10. 11:54:47
*/
public class SQLiteTest {
private final static String MySQLDatabase = "//localhost:8508/javatest?user=root";
private final static String SQLiteDatabase = ":memory:";
private Connection connection;
private SQLiteTest() throws SQLException {
if (1 == 1) {
loadDriver("SQLite");
} else {
loadDriver("MySQL");
}
createDatabase();
crossJoinTest();
}
private void crossJoinTest() throws SQLException {
ResultSet rs = connection.createStatement().executeQuery(
"SELECT * FROM sqlitetest p1, sqlitetest p2 "
+ "WHERE p1.value = p2.value AND p1.id < p2.id");
System.out.println("Pairs:");
while (rs.next()) {
int first = rs.getInt("p1.id");
int second = rs.getInt("p2.id");
System.out.println(first + " - " + second);
}
rs = connection.createStatement().executeQuery("SELECT * FROM sqlitetest");
System.out.println("Singles:");
while (rs.next()) {
System.out.println(rs.getInt("sqlitetest.id"));
}
}
private void createDatabase() throws SQLException {
Statement statement = connection.createStatement();
try {
statement.executeUpdate("DROP TABLE sqlitetest");
} catch (Exception e) {}
statement.executeUpdate("CREATE TABLE sqlitetest "
+ "(id INTEGER PRIMARY KEY, value INTEGER)");
statement.executeUpdate("INSERT INTO sqlitetest VALUES (1,1)");
statement.executeUpdate("INSERT INTO sqlitetest VALUES (2,1)");
statement.executeUpdate("INSERT INTO sqlitetest VALUES (3,2)");
statement.executeUpdate("INSERT INTO sqlitetest VALUES (4,2)");
statement.executeUpdate("INSERT INTO sqlitetest VALUES (5,3)");
statement.executeUpdate("INSERT INTO sqlitetest VALUES (6,3)");
}
private void loadDriver( String driverName ) {
try {
if (driverName.toLowerCase().equals("sqlite")) {
Class.forName("org.sqlite.JDBC");
connection = DriverManager.getConnection("jdbc:sqlite:"
+ SQLiteDatabase);
Statement st = connection.createStatement();
st.execute("PRAGMA full_column_names = 1");
st.execute("PRAGMA short_column_names = 0");
System.out.println("SQLite loaded successful!");
} else {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql:" + MySQLDatabase);
System.out.println("MySQL loaded successful!");
}
} catch (Exception e) {
throw new RuntimeException("Cannot load " + driverName + "!", e);
}
}
public static void main( String[] args ) throws Exception {
new SQLiteTest();
}
}
