SQL Power Software Forum

Is it possible to connect to a Real SQL Server database? It is based on SQLite. I have tried a couple different SQLite JDBC drivers, but I receive this error when I try to connect:

path to '//75.127.72.165:4430/ManageDev': '/75.127.72.165:4430' does not exist

The database is running just fine on my server. Any help would be greatly appreciated.

Hi Ryan,

You should be able to connect to Real SQL Server with a JDBC driver that works with it. Maybe you have some settings wrong in Wabit that is preventing it from connecting to the database. If you could attach a screen shot of your connection properties, the JDBC driver settings, and the stack trace we can see if anything is missing.

Thomas

This message was edited 2 times. Last update was at 2009-06-10 14:09:19

I too had problems with SQLite I did use driver from http://www.zentus.com/sqlitejdbc/ and it did work but when i try to use it i got message "Couldn't get children of Imported Keys folder of ..."
and stack trice:

ca.sqlpower.sqlobject.SQLObjectException: relationship.populate
at ca.sqlpower.sqlobject.SQLRelationship.fetchImportedKeys(SQLRelationship.java:548)
at ca.sqlpower.sqlobject.SQLTable.populateRelationships(SQLTable.java:378)
at ca.sqlpower.sqlobject.SQLTable.access$200(SQLTable.java:39)
at ca.sqlpower.sqlobject.SQLTable$Folder.populateImpl(SQLTable.java:1035)
at ca.sqlpower.sqlobject.SQLObject.populate(SQLObject.java:249)
at ca.sqlpower.sqlobject.SQLObject.getChildren(SQLObject.java:309)
at ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.ensurePopulated(PlayPen.java:1509)
at ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.ensurePopulated(PlayPen.java:1509)
at ca.sqlpower.architect.swingui.PlayPen$AddObjectsTask.doStuff(PlayPen.java:1486)
at ca.sqlpower.swingui.SPSwingWorker.run(SPSwingWorker.java:99)
at java.lang.Thread.run(Thread.java:636)
Caused by: java.sql.SQLException: not yet implemented
at org.sqlite.MetaData.getImportedKeys(MetaData.java:503)
at ca.sqlpower.sqlobject.SQLRelationship.fetchImportedKeys(SQLRelationship.java:543)
... 10 more

Hi SPLESH,

Thanks for posting this issue to the forums. I got the SQLiteJDBC driver you are using and was able to reproduce the error. It seems that the driver does not yet implement retrieving imported keys from the database meta data. As a fix I added a new database meta data wrapper for the SQLite driver that will prevent this exception. You can try a new version with this fix from http://pag.sqlpower.ca:8080/hudson/job/wabit/1097/. Let us know what you think and feel free to post any additional feedback.

Thomas

This message was edited 1 time. Last update was at 2009-08-23 00:48:17

Hi,

Since this problem was bugging me so much, I took the matter in my own hands and fixed sqlitejdbc to implement getImportedKeys. I'm putting the patch down here for anyone who's interested. If you'd like the fixed jar driver, just let me know, I can mail or put in a place everyone can grab it. You should patch Metada.java

patch -p0 Metadata.java patchfile

--- patch starts below---

19a20
> import java.util.Hashtable;
22a24,53
> 	private static String sqlQuote(String str) {
> 		if (str == null) {
> 			return "NULL";
> 		}
> 		int i, single = 0, dbl = 0;
> 		for (i = 0; i < str.length(); i++) {
> 			if (str.charAt(i) == '\'') {
> 				single++;
> 			} else if (str.charAt(i) == '"') {
> 				dbl++;
> 			}
> 		}
> 		if (single == 0) {
> 			return "'" + str + "'";
> 		}
> 		if (dbl == 0) {
> 			return "\"" + str + "\"";
> 		}
> 		StringBuffer sb = new StringBuffer("'");
> 		for (i = 0; i < str.length(); i++) {
> 			char c = str.charAt(i);
> 			if (c == '\'') {
> 				sb.append("''");
> 			} else {
> 				sb.append(c);
> 			}
> 		}
> 		return sb.toString();
> 	}
> 	
28d58
<         getCrossReference = null,
42c72,73
<         getColumnPrivileges = null;
---
>         getColumnPrivileges = null,
>         getIndexInfo = null;
59d89
<             if (getCrossReference != null) getCrossReference.close();
79d108
<             getCrossReference = null;
410,411c439,441
<     public ResultSet getCrossReference(String pc, String ps, String pt,
<                                        String fc, String fs, String ft)
---
>     public ResultSet getCrossReference(String primaryCatalog,
> 			String primarySchema, String primaryTable, String foreignCatalog,
> 			String foreignSchema, String foreignTable)
413,431c443,520
<         if (getCrossReference == null)
<             getCrossReference = conn.prepareStatement("select "
<                 + "null as PKTABLE_CAT, "
<                 + "null as PKTABLE_SCHEM, "
<                 + "null as PKTABLE_NAME, "
<                 + "null as PKCOLUMN_NAME, "
<                 + "null as FKTABLE_CAT, "
<                 + "null as FKTABLE_SCHEM, "
<                 + "null as FKTABLE_NAME, "
<                 + "null as FKCOLUMN_NAME, "
<                 + "null as KEY_SEQ, "
<                 + "null as UPDATE_RULE, "
<                 + "null as DELETE_RULE, "
<                 + "null as FK_NAME, "
<                 + "null as PK_NAME, "
<                 + "null as DEFERRABILITY "
<                 + "limit 0;");
<         getCrossReference.clearParameters();
<         return getCrossReference.executeQuery();
---
>     	
>     	StringBuilder sql = new StringBuilder();
>         ResultSet rs;
>         Statement stat = conn.createStatement();
> 
>         try {
>         	rs = stat.executeQuery("pragma foreign_key_list('"+escape(foreignTable)+"');");
>         }
>         catch(SQLException ex) {
>         	rs = null;
>         }
> 
>         sql.append("select "
>         	+ "null as PKTABLE_CAT, "
>             + "null as PKTABLE_SCHEM, "
>             + "PKTABLE_NAME, "
>             + "PKCOLUMN_NAME, "
>             + "null as FKTABLE_CAT, "
>             + "null as FKTABLE_SCHEM, "
>             + "'" + escape(foreignTable) + "' as FKTABLE_NAME, "
>             + "FKCOLUMN_NAME, "
>             + "KEY_SEQ, "
>             + "UPDATE_RULE, "
>             + "DELETE_RULE, "
>             + "null as FK_NAME, "
>             + "null as PK_NAME, "
>             + "7 as DEFERRABILITY ");
> 
>         if (primaryTable == null)
>         	primaryTable = "";
>         int i = 0;
>     	sql.append(" from ( ");
>         if ( rs == null || !rs.next()) {
>         	sql.append("  select ");
>             sql.append("	null AS PKTABLE_NAME,");
>             sql.append("	null AS PKCOLUMN_NAME,");
>             sql.append("	null AS FKCOLUMN_NAME,");
>             sql.append("	null AS KEY_SEQ,");
>             sql.append("	null AS UPDATE_RULE,");
>             sql.append("	null AS DELETE_RULE ");
>         	sql.append(" ) limit 0");
>         }
>         else {
> 	        do {
> 	            String pktable = rs.getString("table");
> 	            if (primaryTable.equalsIgnoreCase(pktable)) {
> 	            	continue;
> 	            }
> 	
> 	            String pkcolumn = rs.getString("to");
> 	            String fkcolumn = rs.getString("from");
> 	            String updateRule = "RESTRICT";
> 	            String deleteRule = "RESTRICT";
> 	            String seq = rs.getString("seq");
> 	                        
> 	            updateRule = getDbRuleCode(updateRule);
> 	            deleteRule = getDbRuleCode(deleteRule);
> 	                        
> 	            if (fkcolumn == null || fkcolumn.length() == 0)
> 	            	fkcolumn = pkcolumn;
> 	            
> 	            if (i > 0) 
> 	            	sql.append(" union all ");
> 	
> 	            sql.append("select ");
> 	            sql.append(sqlQuote(pktable)+" AS PKTABLE_NAME,");
> 	            sql.append(sqlQuote(pkcolumn)+" AS PKCOLUMN_NAME,");
> 	            sql.append(sqlQuote(fkcolumn)+" AS FKCOLUMN_NAME,");
> 	            sql.append(((Integer.valueOf(seq)).intValue() + 1)+" AS KEY_SEQ,");
> 	            sql.append(updateRule+" AS UPDATE_RULE,");
> 	            sql.append(deleteRule+" AS DELETE_RULE ");
> 	            i++;
> 	        } while( rs.next());
> 	        sql.append(")");
>         }
>         System.out.println(sql.toString());
>         
>         return stat.executeQuery(sql.toString());
433a523,533
> 	private String getDbRuleCode(String dbRule) {
> 		if (dbRule.equals("CASCADE"))
> 			dbRule = Integer.toString(DatabaseMetaData.importedKeyCascade);
> 		else
> 		if (dbRule.equals("SETNULL"))
> 			dbRule = Integer.toString(DatabaseMetaData.importedKeySetNull);
> 		else
> 			dbRule =Integer.toString(DatabaseMetaData.importedKeyRestrict);
> 		return dbRule;
> 	}
> 
503c603,605
<         throws SQLException { throw new SQLException("not yet implemented"); }
---
>         throws SQLException { 
>     	return getCrossReference(null,null,null,c,s,t);
>     }
505,506c607,626
<                                   boolean u, boolean approximate)
<         throws SQLException { throw new SQLException("not yet implemented"); }
---
>                                   boolean u, boolean approximate) 
>     	throws SQLException {
>     	if (getIndexInfo == null) getIndexInfo =
>     		conn.prepareStatement(
>     	            "select "
> 			    	+ "null as TABLE_CAT,"
> 			    	+ "null as TABLE_SCHEM,"
> 			    	+ "null as TABLE_NAME,"
> 			    	+ "null as NON_UNIQUE,"
> 			    	+ "null as INDEX_QUALIFIER,"
> 			    	+ "null as INDEX_NAME,"
> 			    	+ "null as TYPE,"
> 			    	+ "null as ORDINAL_POSITION,"
> 			    	+ "null as COLUMN_NAME,"
> 			    	+ "null as ASC_OR_DESC,"
> 			    	+ "null as CARDINALITY,"
> 			    	+ "null as PAGES,"
> 			    	+ "null as FILTER_CONDITION limit 0;");
>         return getIndexInfo.executeQuery(); 
>     }