1
2
3
4
5
6
7 package org.astrogrid.tableserver.jdbc;
8
9 import java.io.IOException;
10 import java.sql.ResultSet;
11 import java.sql.ResultSetMetaData;
12 import java.sql.SQLException;
13 import java.sql.Types;
14 import java.util.Date;
15 import org.apache.commons.logging.Log;
16 import org.astrogrid.cfg.ConfigFactory;
17 import org.astrogrid.dataservice.DatacenterException;
18 import org.astrogrid.dataservice.metadata.MetadataException;
19 import org.astrogrid.dataservice.queriers.Querier;
20 import org.astrogrid.dataservice.queriers.TableResults;
21 import org.astrogrid.dataservice.queriers.status.QuerierStatus;
22 import org.astrogrid.query.Query;
23 import org.astrogrid.query.condition.ColumnReference;
24 import org.astrogrid.query.condition.Expression;
25 import org.astrogrid.query.returns.ReturnTable;
26 import org.astrogrid.tableserver.metadata.ColumnInfo;
27 import org.astrogrid.tableserver.metadata.TableMetaDocInterpreter;
28 import org.astrogrid.tableserver.out.TableWriter;
29
30 /***
31 * Implementation of <tt>QueryResults</tt> as a wrapper around a <tt>ResultSet</tt>
32 *
33 * <p>Can be used (I believe) for any
34 * SQL/JDBC query results.
35 *
36 * @author M Hill
37 */
38
39 public class SqlResults extends TableResults {
40
41 protected ResultSet sqlResults;
42 protected static final Log log = org.apache.commons.logging.LogFactory.getLog(SqlResults.class);
43
44 /***
45 * Construct this wrapper around the given JDBC/SQL ResultSet. We don't
46 * know how big this result set will be, so it's likely we'll need a workspace
47 * for any temporary files created when doing conversions
48 */
49 public SqlResults(Querier parentQuerier, ResultSet givenResults)
50 {
51 super(parentQuerier);
52 this.sqlResults = givenResults;
53
54 }
55
56 /*** Returns number of rows */
57 public int getCount() {
58 return -1;
59
60
61
62
63
64
65
66
67
68
69 }
70
71 /***/
72 public Class getJavaType(int sqlType) {
73 switch (sqlType)
74 {
75 case Types.BIGINT: return Long.class;
76 case Types.BOOLEAN: return Boolean.class;
77 case Types.VARCHAR: return String.class;
78 case Types.CHAR: return String.class;
79 case Types.DOUBLE: return Double.class;
80 case Types.FLOAT: return Float.class;
81 case Types.INTEGER: return Integer.class;
82 case Types.REAL: return Float.class;
83 case Types.SMALLINT: return Integer.class;
84 case Types.TINYINT: return Integer.class;
85 case Types.DATE: return Date.class;
86 case Types.TIMESTAMP:return Date.class;
87 default: {
88 log.error("Don't know what Java class SQL type "+sqlType+" maps to, storing as string", new RuntimeException());
89 return String.class;
90 }
91 }
92 }
93
94 /***
95 * Writes out results from SQL Result set to given table writer
96 */
97 public void writeTable(TableWriter tableWriter, QuerierStatus statusToUpdate) throws IOException
98 {
99
100 long localLimit = ConfigFactory.getCommonConfig().getInt(Query.MAX_RETURN_KEY, -1);
101 long queryLimit = querier.getQuery().getLimit();
102
103 try
104 {
105
106 ResultSetMetaData metadata = sqlResults.getMetaData();
107
108
109 int numCols = metadata.getColumnCount();
110 ColumnInfo[] cols = new ColumnInfo[numCols];
111 Expression[] colDefs = ((ReturnTable) querier.getQuery().getResultsDef()).getColDefs();
112 TableMetaDocInterpreter interpreter = new TableMetaDocInterpreter();
113 for (int i=1;i<=numCols;i++)
114 {
115
116 if (colDefs == null) {
117
118 try {
119 cols[i-1] = interpreter.guessColumn(querier.getQuery().getScope(), metadata.getColumnName(i));
120 }
121 catch (MetadataException me) {
122 log.error(me+" guessing which column "+metadata.getColumnName(i)+" belongs to which table");
123
124 }
125 if (cols[i-1] == null) {
126 cols[i-1] = new ColumnInfo();
127 cols[i-1].setName(metadata.getColumnName(i));
128 cols[i-1].setId("?."+metadata.getColumnName(i));
129 }
130 }
131 else if (colDefs[i-1] instanceof ColumnReference) {
132 ColumnReference colRef = (ColumnReference) colDefs[i-1];
133 cols[i-1] = new ColumnInfo();
134 cols[i-1].setName(metadata.getColumnName(i));
135 cols[i-1].setId(colRef.getTableName()+"."+colRef.getColName());
136 cols[i-1].setUcd(interpreter.getColumn(null, colRef.getTableName(), colRef.getColName()).getUcd("1"),"1");
137 cols[i-1].setUnits(interpreter.getColumn(null, colRef.getTableName(), colRef.getColName()).getUnits());
138 cols[i-1].setPublicType(interpreter.getColumn(null, colRef.getTableName(), colRef.getColName()).getPublicType());
139 }
140 else {
141
142 throw new IllegalArgumentException("Column Definition "+colDefs[i-1]+" is not a ColumnReference; not suitable for SQL data");
143 }
144 cols[i-1].setBackType(""+metadata.getColumnType(i));
145 try {
146 cols[i-1].setJavaType(Class.forName(metadata.getColumnClassName(i)));
147 }
148 catch (ClassNotFoundException cnfe) {
149 log.error(cnfe+" for column "+i,cnfe);
150 }
151 catch (SQLException se) {
152
153 log.error(se+" for column "+i,se);
154 }
155
156 }
157 tableWriter.startTable(cols);
158
159 int row = 0;
160 statusToUpdate.newProgress("Processing Row", getCount());
161 String[] colValues = new String[numCols];
162 while (sqlResults.next() && ((queryLimit <=0) || (row<=queryLimit)))
163 {
164 row++;
165 statusToUpdate.setProgress(row);
166
167 for (int i=1;i<=numCols;i++)
168 {
169 try {
170 colValues[i-1] = sqlResults.getString(i);
171 }
172 catch (SQLException se) {
173 log.error(se+" reading value of column "+i+" row "+row,se);
174 colValues[i-1] = se.toString();
175 }
176 catch (Exception se) {
177 log.error(se+" reading value of column "+i+" row "+row,se);
178 colValues[i-1] = se.toString();
179 }
180 }
181 tableWriter.writeRow(colValues);
182
183
184
185 if ((localLimit>0) && (row>localLimit)) {
186 statusToUpdate.addDetail("Results limited to "+localLimit+" rows by datacenter");
187 log.warn("Limiting returned results to "+localLimit);
188 tableWriter.writeRow(new String[] {"Limited to "+localLimit+" by datacenter"});
189 break;
190 }
191
192 if (querier.isAborted()) {
193 tableWriter.abort();
194 querier.setResultsSize(row);
195 return;
196 }
197 }
198 tableWriter.endTable();
199
200 statusToUpdate.addDetail(row+" rows sent");
201 statusToUpdate.clearProgress();
202
203 querier.setResultsSize(row);
204
205 tableWriter.close();
206 }
207 catch (SQLException sqle)
208 {
209 log.error(sqle+" reading results",sqle);
210 throw new DatacenterException(sqle+", reading results", sqle);
211 }
212
213
214 }
215
216
217 public String getUcdFor(String columnName)
218 {
219
220 return "unknown";
221 }
222
223 /*** Returns the formats that this plugin can provide. Doesn't provide Raw */
224 public static String[] listFormats() {
225 return new String[] { ReturnTable.VOTABLE, ReturnTable.CSV, ReturnTable.HTML } ;
226 }
227
228
229
230 }
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265