[Squirrel-sql-commits] sql12/test/src/net/sourceforge/squirrel_sql/fw/sql OracleSQL.java, NONE, 1.
A Java SQL client for any JDBC compliant database
Brought to you by:
colbell,
gerdwagner
From: Rob M. <man...@us...> - 2007-01-29 01:12:57
|
Update of /cvsroot/squirrel-sql/sql12/test/src/net/sourceforge/squirrel_sql/fw/sql In directory sc8-pr-cvs8.sourceforge.net:/tmp/cvs-serv8074/test/src/net/sourceforge/squirrel_sql/fw/sql Added Files: OracleSQL.java QueryTokenizerTest.java Log Message: Only attempt to join stored proc fragments for Oracle sessions(Other DBs don't necessarily allow embedded statement separators in the procedure body). Also, made the detection of store proc declarations more robust using regex. --- NEW FILE: QueryTokenizerTest.java --- package net.sourceforge.squirrel_sql.fw.sql; import java.io.File; import java.io.FileWriter; import java.io.IOException; import java.io.PrintWriter; import junit.framework.TestCase; import net.sourceforge.squirrel_sql.client.ApplicationArguments; public class QueryTokenizerTest extends TestCase implements OracleSQL { static String nullSQL = null; static String sql3 = "update test set /*PARAM1*/ thing /*C*/ = 'default value' /*/PARAM1*/;"; static String tmpFilename = null; static boolean removeMultilineComment = true; static { ApplicationArguments.initialize(new String[] {}); } QueryTokenizer qt = null; public void setUp() throws Exception { createSQLFile(); } public void tearDown() { } public void testHasQuery() { qt = new QueryTokenizer(SELECT_DUAL, ";", "--", removeMultilineComment, true); checkQueryTokenizer(qt, 1); } public void testCreateStoredProcedure() { qt = new QueryTokenizer(CREATE_STORED_PROC, ";", "--", removeMultilineComment, true); checkQueryTokenizer(qt, 1); } public void testCreateOrReplaceStoredProcedure() { qt = new QueryTokenizer(CREATE_OR_REPLACE_STORED_PROC, ";", "--", removeMultilineComment, true); checkQueryTokenizer(qt, 1); } public void testCreateOrReplaceStoredProcedure2() { qt = new QueryTokenizer(CREATE_OR_REPLACE_STORED_PROC2, ";", "--", removeMultilineComment, true); checkQueryTokenizer(qt, 1); } public void testHasQueryFromFile() { String fileSQL = "@" + tmpFilename + ";\n"; qt = new QueryTokenizer(fileSQL, ";", "--", removeMultilineComment, true); checkQueryTokenizer(qt, 3); } private void checkQueryTokenizer(QueryTokenizer qt, int stmtCount) { int count = 0; while (qt.hasQuery()) { count++; System.out.println(" query: "+qt.nextQuery()); } assertEquals(stmtCount, count); } private static void createSQLFile() throws IOException { if (tmpFilename != null) { return; } File f = File.createTempFile("test", ".sql"); f.deleteOnExit(); PrintWriter out = new PrintWriter(new FileWriter(f)); out.print(SELECT_DUAL); out.println(); out.print(SELECT_DUAL); out.println(); out.print(sql3); out.println(); out.print(CREATE_STORED_PROC); out.println(); out.print(CREATE_OR_REPLACE_STORED_PROC); out.print(";"); out.println(); out.close(); tmpFilename = f.getAbsolutePath(); //System.out.println("tmpFilename="+tmpFilename); } } --- NEW FILE: OracleSQL.java --- package net.sourceforge.squirrel_sql.fw.sql; public interface OracleSQL { public final static String SELECT_DUAL = "select 'X' from dual;"; public final static String CREATE_STORED_PROC = " create procedure fooproc (Person_name IN varchar2) \n" + "AS \n" + "BEGIN \n" + " insert into testdate \n" + " (mydate) \n" + " values \n" + " (sysdate); \n" + "END; \n" + " \n" + "/ \n"; public final static String CREATE_OR_REPLACE_STORED_PROC = "create or replace procedure fooproc (Person_name IN varchar2) \n" + "AS \n" + "BEGIN \n" + " insert into testdate \n" + " (mydate) \n" + " values \n" + " (sysdate); \n" + "END; \n" + " \n" + "/ \n"; public final static String CREATE_OR_REPLACE_STORED_PROC2 = "CREATE OR REPLACE PROCEDURE RUNPROCESS ( " + " processname IN VARCHAR2 DEFAULT NULL, " + " servername IN VARCHAR2 DEFAULT NULL, " + " run_ OUT NUMBER) IS " + "BEGIN \n" + " \n" + " DECLARE \n" + " server$ VARCHAR2(255); \n" + " enabled$ CHAR; " + " run$ CHAR; " + " lastrun$ DATE; " + " cycle$ NUMBER; " + " runcompare$ DATE; " + " starthour$ NUMBER; " + " endhour$ NUMBER; " + " nowhour$ NUMBER; \n" + " \n" + " BEGIN \n" + " BEGIN \n" + " SELECT Server, FEnabled, LastRunTime, CycleTime, StartHour, EndHour " + " INTO server$, enabled$, lastrun$, cycle$, starthour$, endhour$ " + " FROM JF_ScheduledProcess " + " WHERE Process = processname; " + " EXCEPTION " + " WHEN NO_DATA_FOUND THEN " + " enabled$ := '0'; " + " END; \n" + " \n" + " IF (enabled$ = '0') THEN \n" + " run$ := '0'; " + " ELSE " + " nowhour$ := TO_CHAR(SYSDATE, 'HH24'); " + " IF ((((starthour$ > nowhour$) or (endhour$ < nowhour$)) and (starthour$ < endhour$)) or " + " ((starthour$ > nowhour$) and (endhour$ < nowhour$))) THEN " + " run$ := '0'; " + " ELSE " + " IF (server$ = servername) THEN " + " runcompare$ := lastrun$ + (cycle$/1440); " + " IF (runcompare$ < SYSDATE) THEN " + " UPDATE JF_ScheduledProcess SET LastRunTime = SYSDATE " + " WHERE Process = processname; " + " run$ := '1'; " + " ELSE " + " run$ := '0'; " + " END IF; " + " ELSE " + " runcompare$ := lastrun$ + ((2 * cycle$)/1440); " + " IF (runcompare$ < SYSDATE) THEN " + " UPDATE JF_ScheduledProcess SET LastRunTime = SYSDATE, " + " Server = servername " + " WHERE Process = processname; " + " run$ := '1'; " + " ELSE " + " run$ := '0'; " + " END IF; " + " END IF; " + " END IF; " + " END IF; " + " " + " run_ := run$; " + " END; \n" + "END RUNPROCESS; \n" + " \n" + "/ \n"; } |