@Override public ResultSet executeQuery(String sql) throws SQLException { checkOpen(); CsvDriver.writeLog("CsvStatement:executeQuery() - sql= " + sql); /* * Close any previous ResultSet, as required by JDBC. */ try { if (lastResultSet != null) lastResultSet.close(); } finally { lastResultSet = null; multipleParsers = null; } SqlParser parser = new SqlParser(); try { parser.parse(sql); } catch (Exception e) { throw new SQLException(CsvResources.getString("syntaxError") + ": " + e.getMessage()); } return executeParsedQuery(parser); }
public void testWhere() throws Exception { ExpressionEvaluator evaluator = new ExpressionEvaluator(); String testSql = "select * from aaa where /*%if false*/ename = 'aaa'/*%end */"; SqlParser parser = new SqlParser(testSql); SqlNode sqlNode = parser.parse(); PreparedSql sql = new NodePreparedSqlBuilder( config, SqlKind.SELECT, "dummyPath", evaluator, SqlLogType.FORMATTED) .build(sqlNode, Function.identity()); assertEquals("select * from aaa", sql.getRawSql()); }
public void testWhere_embeddedVariable_orderBy() throws Exception { ExpressionEvaluator evaluator = new ExpressionEvaluator(); evaluator.add("embedded", new Value(String.class, "order by bbb")); String testSql = "select * from aaa where /*%if false*/ename = 'aaa'/*%end */ /*#embedded*/"; SqlParser parser = new SqlParser(testSql); SqlNode sqlNode = parser.parse(); PreparedSql sql = new NodePreparedSqlBuilder( config, SqlKind.SELECT, "dummyPath", evaluator, SqlLogType.FORMATTED) .build(sqlNode, Function.identity()); assertEquals("select * from aaa order by bbb", sql.getRawSql()); }
private void addFilterClause(StringBuilder sb, HashMap parameters) { String whereclause = parser.getWhereClauseString(); if (whereclause != null && whereclause.length() > 0) { sb.append("( "); sb.append(whereclause); sb.append(" )"); } }
public boolean processIndex(DFUFile indexfiletouse, StringBuilder keyedandwild) { boolean isPayloadIndex = containsPayload(indexfiletouse.getAllFieldsProps(), parser.getSelectColumns().iterator()); Vector<String> keyed = new Vector<String>(); Vector<String> wild = new Vector<String>(); // Create keyed and wild string Properties keyedcols = indexfiletouse.getKeyedColumns(); for (int i = 1; i <= keyedcols.size(); i++) { String keyedcolname = (String) keyedcols.get(i); if (parser.whereClauseContainsKey(keyedcolname)) keyed.add(" " + parser.getExpressionFromName(keyedcolname).toString() + " "); else if (keyed.isEmpty()) wild.add(" " + keyedcolname + " "); } if (isPayloadIndex) { if (keyed.size() > 0) { keyedandwild.append("KEYED( "); for (int i = 0; i < keyed.size(); i++) { keyedandwild.append(keyed.get(i)); if (i < keyed.size() - 1) keyedandwild.append(" AND "); } keyedandwild.append(" )"); } if (wild.size() > 0) { // TODO should I bother making sure there's a KEYED entry ? for (int i = 0; i < wild.size(); i++) { keyedandwild.append(" and WILD( "); keyedandwild.append(wild.get(i)); keyedandwild.append(" )"); } } keyedandwild.append(" and (").append(parser.getWhereClauseString()).append(" )"); } else // non-payload just AND the keyed expressions { keyedandwild.append("( "); keyedandwild.append(parser.getWhereClauseString()); keyedandwild.append(" )"); } return isPayloadIndex; }
public void testEmbeddedVariable_blockComment() throws Exception { ExpressionEvaluator evaluator = new ExpressionEvaluator(); evaluator.add("name", new Value(String.class, "hoge")); evaluator.add("salary", new Value(BigDecimal.class, new BigDecimal(10000))); evaluator.add("orderBy", new Value(String.class, "aaa/*bbb")); String testSql = "select * from aaa where ename = /*name*/'aaa' and sal = /*salary*/-2000 /*#orderBy*/"; SqlParser parser = new SqlParser(testSql); SqlNode sqlNode = parser.parse(); try { new NodePreparedSqlBuilder( config, SqlKind.SELECT, "dummyPath", evaluator, SqlLogType.FORMATTED) .build(sqlNode, Function.identity()); fail(); } catch (JdbcException expected) { System.out.println(expected.getMessage()); assertEquals(Message.DOMA2123, expected.getMessageResource()); } }
private static void printStatement(String sql) { println(sql.trim()); println(""); Statement statement = SQL_PARSER.createStatement(sql); println(statement.toString()); println(""); println(SqlFormatter.formatSql(statement, Optional.empty())); println(""); assertFormattedSql(SQL_PARSER, statement); println(repeat("=", 60)); println(""); }
public SqlNode parseQuery(String sql) throws Exception { SqlParser parser = SqlParser.create(sql); SqlNode sqlNode = parser.parseQuery(); return sqlNode; }
public ArrayList execute() throws Exception { int sqlreqtype = parser.getSqlType(); List<EclColumnMetaData> expectedretcolumns = new ArrayList(); ArrayList<EclColumnMetaData> storeProcInParams = new ArrayList(); String hpccfilename = ""; String indexposfield = null; StringBuilder keyedandwild = new StringBuilder(); DFUFile indexfiletouse = null; StringBuilder eclcode = new StringBuilder(""); int totalparamcount = 0; boolean isPayloadIndex = false; switch (sqlreqtype) { case SqlParser.SQL_TYPE_SELECT: { hpccfilename = Utils.handleQuotedString(parser.getTableName()); if (!dbMetadata.tableExists("", hpccfilename)) throw new Exception("Invalid table found: " + hpccfilename); DFUFile dfufile = dbMetadata.getDFUFile(hpccfilename); // in future this might need to be a container of dfufile(s) parser.verifySelectColumns(dfufile); expectedretcolumns = parser.getSelectColumns(); totalparamcount = parser.getWhereClauseExpressionsCount(); if (indexToUseName != null) { indexfiletouse = dbMetadata.getDFUFile(indexToUseName); indexposfield = indexfiletouse.getIdxFilePosField(); isPayloadIndex = processIndex(indexfiletouse, keyedandwild); eclEnteties.put("KEYEDWILD", keyedandwild.toString()); if (isPayloadIndex) eclEnteties.put("PAYLOADINDEX", "true"); } eclEnteties.put("PARAMCOUNT", Integer.toString(totalparamcount)); if (dfufile.hasFileRecDef()) { if (indexfiletouse != null && indexposfield != null) eclcode.append( dfufile.getFileRecDefwithIndexpos( indexfiletouse.getFieldMetaData(indexposfield), "filerecstruct")); else eclcode.append(dfufile.getFileRecDef("filerecstruct")); } else throw new Exception( "Target HPCC file (" + hpccfilename + ") does not contain ECL record definition"); if (!dfufile.isKeyFile()) eclcode .append("fileds := DATASET(\'~") .append(dfufile.getFullyQualifiedName()) .append("\', filerecstruct,") .append(dfufile.getFormat()) .append("); "); else { eclcode.append("fileds := INDEX( "); eclcode.append('{'); eclcode.append(dfufile.getKeyedFieldsAsDelmitedString(',', "filerecstruct")); eclcode.append("},{"); eclcode.append(dfufile.getNonKeyedFieldsAsDelmitedString(',', "filerecstruct")); eclcode.append("},"); eclcode.append("\'~").append(dfufile.getFullyQualifiedName()).append("\');"); } StringBuilder selectstruct = new StringBuilder(" selectstruct:=RECORD "); String datasource = indexToUseName == null || isPayloadIndex ? "fileds" : "fetchedds"; // boolean usescalar = expectedretcolumns.size() == 1; for (int i = 0; i < expectedretcolumns.size(); i++) { EclColumnMetaData col = expectedretcolumns.get(i); if (col.getColumnType() == EclColumnMetaData.COLUMN_TYPE_CONSTANT) { selectstruct .append(col.getEclType()) .append(" ") .append(col.getColumnName()) .append(" := ") .append(col.getConstantValue()) .append("; "); if (i == 0 && expectedretcolumns.size() == 1) eclEnteties.put("SCALAROUTNAME", col.getColumnName()); } else if (col.getColumnType() == EclColumnMetaData.COLUMN_TYPE_FNCTION) { if (col.getColumnName().equalsIgnoreCase("COUNT")) { eclEnteties.put("COUNTFN", "TRUE"); selectstruct.append("countout := "); if (parser.hasGroupByColumns()) { selectstruct.append(col.getColumnName().toUpperCase()).append("( GROUP"); List<EclColumnMetaData> funccols = col.getFunccols(); if (funccols.size() > 0) { String paramname = funccols.get(0).getColumnName(); if (!paramname.equals("*") && funccols.get(0).getColumnType() != EclColumnMetaData.COLUMN_TYPE_CONSTANT) { selectstruct.append(", "); selectstruct.append(datasource); selectstruct.append("."); selectstruct.append(paramname); selectstruct.append("<> \'\'"); // if (eclEnteties.size() > 0) // addFilterClause(selectstruct, eclEnteties); } } selectstruct.append(" );"); } else { selectstruct.append(" scalarout;"); if (expectedretcolumns.size() == 1) eclEnteties.put("SCALAROUTNAME", col.getColumnName()); } col.setSQLType(java.sql.Types.NUMERIC); } else if (col.getColumnName().equalsIgnoreCase("MAX")) { eclEnteties.put("MAXFN", "TRUE"); selectstruct.append("maxout := "); if (parser.hasGroupByColumns()) { selectstruct.append("MAX( GROUP "); } else { selectstruct.append("MAX( ").append(datasource); if (eclEnteties.size() > 0) addFilterClause(selectstruct, eclEnteties); } List<EclColumnMetaData> funccols = col.getFunccols(); if (funccols.size() > 0) { String paramname = funccols.get(0).getColumnName(); eclEnteties.put("FNCOLS", paramname); if (!paramname.equals("*") && funccols.get(0).getColumnType() != EclColumnMetaData.COLUMN_TYPE_CONSTANT) { selectstruct.append(", "); selectstruct.append(datasource); selectstruct.append("."); selectstruct.append(paramname); } } selectstruct.append(" );"); /* if (parser.hasGroupByColumns()) { selectstruct.append(col.getColumnName().toUpperCase()).append("( GROUP"); List<EclColumnMetaData> funccols = col.getFunccols(); if (funccols.size() > 0) { String paramname = funccols.get(0).getColumnName(); eclEnteties.put("FNCOLS", paramname); if (!paramname.equals("*") && funccols.get(0).getColumnType() != EclColumnMetaData.COLUMN_TYPE_CONSTANT) { selectstruct.append(", "); selectstruct.append(datasource); selectstruct.append("."); selectstruct.append(paramname); } } selectstruct.append(" );"); } else { selectstruct.append(" totalmax;"); //if (expectedretcolumns.size() == 1) // eclEnteties.put("SCALAROUTNAME", col.getColumnName()); } */ /* selectstruct.append(col.getColumnName().toUpperCase()).append(" ( "); if (parser.hasGroupByColumns()) selectstruct.append("GROUP"); else selectstruct.append(datasource); List<EclColumnMetaData> funccols = col.getFunccols(); if (funccols.size() > 0) { String paramname = funccols.get(0).getColumnName(); if (!paramname.equals("*") && funccols.get(0).getColumnType() != EclColumnMetaData.COLUMN_TYPE_CONSTANT) { selectstruct.append(", "); selectstruct.append(datasource); selectstruct.append("."); selectstruct.append(paramname); } } selectstruct.append(" );");*/ } } else selectstruct .append(col.getEclType()) .append(" ") .append(col.getColumnName()) .append(" := ") .append(datasource) .append(".") .append(col.getColumnName()) .append("; "); // if (i == 0 && expectedretcolumns.size() == 1 && col.getColumnType() != // EclColumnMetaData.COLUMN_TYPE_DATA ) // eclEnteties.put("SCALAROUTNAME", col.getColumnName()); } selectstruct.append("END; "); eclEnteties.put("SELECTSTRUCT", selectstruct.toString()); if (parser.hasOrderByColumns()) eclEnteties.put("ORDERBY", parser.getOrderByString()); if (parser.hasGroupByColumns()) eclEnteties.put("GROUPBY", parser.getGroupByString()); if (parser.hasLimitBy()) eclEnteties.put("LIMIT", Integer.toString(parser.getLimit())); return executeSelect(eclcode.toString(), eclEnteties, indexfiletouse); } case SqlParser.SQL_TYPE_SELECTCONST: { System.out.println("Processing test_query..."); // ArrayList<EclColumnMetaData> columns = new ArrayList(); eclcode.append("selectstruct:=RECORD "); expectedretcolumns = parser.getSelectColumns(); // defaultEclQueryReturnDatasetName = "ConstECLQueryResult"; StringBuilder ecloutput = new StringBuilder(" OUTPUT(DATASET([{ "); for (int i = 1; i <= expectedretcolumns.size(); i++) { EclColumnMetaData col = expectedretcolumns.get(i - 1); eclcode.append(col.getEclType()).append(" ").append(col.getColumnName()).append("; "); ecloutput.append(col.getConstantValue()); if (i < expectedretcolumns.size()) ecloutput.append(", "); } ecloutput.append("}],selectstruct), NAMED(\'"); // ecloutput.append(defaultEclQueryReturnDatasetName); ecloutput.append("ConstECLQueryResult"); ecloutput.append("\'));"); eclcode.append(" END; "); eclcode.append(ecloutput.toString()); return executeSelectConstant(eclcode.toString()); } case SqlParser.SQL_TYPE_CALL: { eclqueryname = Utils.handleQuotedString(parser.getStoredProcName()); if (!dbMetadata.eclQueryExists("", eclqueryname)) throw new Exception("Invalid store procedure found"); return executeCall(null); } default: break; } return null; }
public ArrayList executeCall(Map parameters) { try { urlString = "http://" + props.getProperty("ServerAddress") + ":" + props.getProperty("WsECLPort") + "/WsEcl/submit/query/" + props.getProperty("Cluster") + "/" + eclqueryname + "/expanded"; System.out.println("WSECL:executeCall: " + urlString); // Construct data StringBuilder sb = new StringBuilder(); sb.append(URLEncoder.encode("submit_type_=xml", "UTF-8")); sb.append("&").append(URLEncoder.encode("S1=Submit", "UTF-8")); ArrayList<EclColumnMetaData> storeProcInParams = dbMetadata.getStoredProcInColumns("", eclqueryname); String[] procInParamValues = parser.getStoredProcInParamVals(); for (int i = 0; i < procInParamValues.length; i++) { String key = storeProcInParams.get(i).getColumnName(); { sb.append("&").append(key).append("=").append(procInParamValues[i]); } } // if (parameters.size() > 0) // { // for (Object key : parameters.keySet()) // { // Object value = parameters.get(key); // sb.append("&").append(key).append("=").append(value); // } // } long startTime = System.currentTimeMillis(); // Send data URL url = new URL(urlString); HttpURLConnection conn = (HttpURLConnection) url.openConnection(); conn.setInstanceFollowRedirects(false); conn.setRequestProperty("Authorization", basicAuth); conn.setRequestMethod("GET"); conn.setDoOutput(true); conn.setDoInput(true); OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream()); wr.write(sb.toString()); wr.flush(); System.out.println("WsEcl Execute: " + urlString + " : " + sb.toString()); // Get the response return parse(conn.getInputStream(), startTime); } catch (Exception e) { throw new RuntimeException(e); } }
protected ResultSet executeParsedQuery( SqlParser parser, Map<String, Object> parentobjectEnvironment) throws SQLException { if (parser.getTableNames().size() > 1) throw new SQLException(CsvResources.getString("joinNotSupported")); String tableName = null; if (parser.getTableNames().size() > 0) tableName = parser.getTableNames().get(0); String tableAlias = null; if (parser.getTableAliases().size() > 0) tableAlias = parser.getTableAliases().get(0); String path = connection.getPath(); TableReader tableReader = connection.getTableReader(); if (path != null) CsvDriver.writeLog("Connection Path: " + path); else CsvDriver.writeLog("Connection TableReader: " + tableReader.getClass().getName()); CsvDriver.writeLog("Parser Table Name: " + tableName); CsvDriver.writeLog("Connection Extension: " + connection.getExtension()); DataReader reader = null; String fileName = null; if (tableName == null) { /* * Create an empty dataset with one row. */ String[] columnNames = new String[0]; String[] columnTypes = new String[0]; ArrayList<Object[]> rows = new ArrayList<Object[]>(); rows.add(new Object[0]); reader = new ListDataReader(columnNames, columnTypes, rows); } else { if (path != null && (!connection.isIndexedFiles())) { fileName = path + tableName + connection.getExtension(); CsvDriver.writeLog("CSV file name: " + fileName); File checkFile = new File(fileName); if (!checkFile.exists()) { throw new SQLException(CsvResources.getString("fileNotFound") + ": " + fileName); } if (!checkFile.canRead()) { throw new SQLException(CsvResources.getString("fileNotReadable") + ": " + fileName); } } try { if (connection.getExtension().equalsIgnoreCase(".dbf")) { reader = new DbfReader(fileName, tableName, tableAlias, connection.getCharset()); } else { LineNumberReader input; if (tableReader == null) { InputStream in; CryptoFilter filter = connection.getDecryptingCodec(); if (connection.isIndexedFiles()) { String fileNamePattern = tableName + connection.getFileNamePattern() + connection.getExtension(); String[] nameParts = connection.getNameParts(); String dirName = connection.getPath(); in = new FileSetInputStream( dirName, fileNamePattern, nameParts, connection.getSeparator(), connection.isFileTailPrepend(), connection.isSuppressHeaders(), filter, connection.getSkipLeadingDataLines() + connection.getTransposedLines()); } else if (filter == null) { in = new FileInputStream(fileName); } else { filter.reset(); in = new EncryptedFileInputStream(fileName, filter); } if (connection.getCharset() != null) { input = new LineNumberReader(new InputStreamReader(in, connection.getCharset())); } else { input = new LineNumberReader(new InputStreamReader(in)); } } else { /* * Reader for table comes from user-provided class. */ input = new LineNumberReader(tableReader.getReader(this, tableName)); } String headerline = connection.getHeaderline(tableName); CsvRawReader rawReader = new CsvRawReader( input, tableName, tableAlias, connection.getSeparator(), connection.isSuppressHeaders(), connection.isHeaderFixedWidth(), connection.getQuotechar(), connection.getCommentChar(), headerline, connection.getTrimHeaders(), connection.getTrimValues(), connection.getSkipLeadingLines(), connection.isIgnoreUnparseableLines(), connection.getMissingValue(), connection.isDefectiveHeaders(), connection.getSkipLeadingDataLines(), connection.getQuoteStyle(), connection.getFixedWidthColumns()); reader = new CsvReader( rawReader, connection.getTransposedLines(), connection.getTransposedFieldsToSkip(), headerline); } } catch (IOException e) { throw new SQLException(CsvResources.getString("fileReadError") + ": " + e); } catch (SQLException e) { throw e; } catch (Exception e) { e.printStackTrace(); throw new SQLException(CsvResources.getString("dataReaderError") + ": " + e); } } CsvResultSet resultSet = null; try { resultSet = new CsvResultSet( this, reader, tableName, parser.getColumns(), parser.isDistinct(), this.resultSetType, parser.getWhereClause(), parser.getGroupByColumns(), parser.getHavingClause(), parser.getOrderByColumns(), parser.getLimit(), parser.getOffset(), connection.getColumnTypes(tableName), connection.getSkipLeadingLines(), parentobjectEnvironment); lastResultSet = resultSet; } catch (ClassNotFoundException e) { CsvDriver.writeLog("" + e); } return resultSet; }