Link to home
Start Free TrialLog in
Avatar of gbcbr
gbcbrFlag for Cyprus

asked on

SQL statement reply

CREATE TABLE "LIONFX"."RBF_DATA"
  (
    "ID" NUMBER NOT NULL ENABLE,
    "OUTY0" FLOAT(126),
    "OUTY1" FLOAT(126),

Open in new window

public void run() {
        try {
            ResultSet rs =
                    conn.select("SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1");

                outY[0] = rs.getDouble("OUTY0");
                outY[1] = rs.getDouble("OUTY1");

Open in new window

i13.02.2011 0:36:05 connect.SelectData run
SEVERE: null
java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthes

Open in new window

Please advice
Avatar of for_yan
for_yan
Flag of United States of America image



I cannot see what is it with prentheses - are you sure the exception comes form this line?

Did you put ex.printStackTrace() so that you should get the exact line.


Frankly, the whole statement seems strange to me

if conn is connection then normally what  I do is:

Connection conn = ... some format of connection to db

then

Statement stmt = conn.createStatement();

then

ResultSet rs = stmt.executeQuery("...");

I never did conn.select(...)

But if it compiles for you maybe this is another option in JDBC which I'm not aware of.










Avatar of Sharath S
What are you trying to achieve with the query? What is your database?
conn.select("SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1");
The query seems reasonable to me - it is just to find all values which correspond to
maximum of TIMEST in RBF_DATA, and if there are more than one - only one is selected,
I used such things - it usually worked. I didn't use conn.select... though
No, the syntax of SQL command itself seems correct - I checked it with my table in Oracle - it works fine and does exactly that.
Please, check if the exception really comes from this line
Still, it would be good to see a little bit of code before -
what type is your "conn" variable

Maybe you still want to try statement.executeQuery(....) method
Avatar of gbcbr

ASKER

I change only table name in this query which perfect worked before with few tables and extract data from all of them.
 
public void run() {
        try {
                ResultSet rs =
                    conn.select("SELECT * FROM EURUSD where timest = ( select max( timest ) from EURUSD ) and rownum = 1 union all " +
                                "SELECT * FROM USDCHF where timest = ( select max( timest ) from USDCHF ) and rownum = 1  union all " +
                                "SELECT * FROM EURCHF where timest = ( select max( timest ) from EURCHF ) and rownum = 1");

                while (rs.next()) {

                String s = rs.getString("symbol");
                Timestamp ts = rs.getTimestamp("TIMEST");
                double bpx = rs.getDouble("BIDPX");
                double apx = rs.getDouble("ASKPX");

                if (s.equals("EUR/USD")) {

                    timest[0] = ts;
                    openBid[0] = bpx;
                    openAsk[0] = apx;

                } else if (s.equals("EUR/CHF")) {

                    timest[1] = ts;
                    openBid[1] = bpx;
                    openAsk[1] = apx;

Open in new window

and for me very strange why this much simple code doesn't work correct?
Did you insert printStackTrace()? I mean are you absolutely sure that it is
this SQL statement

 SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1

which causes missing parehthesis exception.

You see, your previos statemts like that worked,
I tried similar statement (pasted from your post to make sure I would not type
in extra parenthesis myself)  just with my table name
in my Oracle environment and it also worked perfectly well,
Maybe it is different SQL statement which causes this error?



Avatar of gbcbr

ASKER

public class SelectData extends TimerTask {

    private LionFXConnect conn;
    ArrayConverter aConverter;
    double outY[] = new double[10];

    public SelectData() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, FieldNotFound, NotDefinedException, Exception {

        conn = new LionFXConnect();
        aConverter = new ArrayConverter(outY);
    }

    public void run() {
        try {
            ResultSet rs =
                    conn.select("SELECT (OUTY0, OUTY0, OUTY1, OUTY2, OUTY3, OUTY4) FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1");

                outY[0] = rs.getDouble("OUTY0");
                outY[1] = rs.getDouble("OUTY1");
                outY[2] = rs.getDouble("OUTY2");
                outY[3] = rs.getDouble("OUTY3");
                outY[4] = rs.getDouble("OUTY4");
                outY[5] = rs.getDouble("OUTY5");
                outY[6] = rs.getDouble("OUTY6");
                outY[7] = rs.getDouble("OUTY7");
                outY[8] = rs.getDouble("OUTY8");
                outY[9] = rs.getDouble("OUTY9");

                equals(rs);

                System.out.println("OUTY0    : " + outY[0]);

                aConverter.arrayConverter(outY);

        } catch (Exception ex) {
            Logger.getLogger(SelectData.class.getName()).log(Level.SEVERE,
                    null, ex);
        }
    }
}

Open in new window

public class LionFXConnect {

    private static String databaseurl = "jdbc:oracle:thin:@";
    private static String user = "";
    private static String password = "";
    private static String driverName = "oracle.jdbc.OracleDriver";

    Connection conn;
    ResultSet rs;
    PreparedStatement ps;

    public ResultSet select(String sql) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException  {
        Class.forName(driverName).newInstance();
        conn = DriverManager.getConnection(databaseurl, user, password);
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        return rs;
    }

    public void destroy() throws SQLException, IOException {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(LionFXConnect.class.getName()).log(Level.SEVERE, null,
                                                            ex);
        }
    }
}

Open in new window

13.02.2011 11:30:21 connect.SelectData run
SEVERE: null
java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthes

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
        at connect.LionFXConnect.select(LionFXConnect.java:45)>>>>>  rs = ps.executeQuery();
        at connect.SelectData.run(SelectData.java:50) >>>>>ResultSet rs =
                    conn.select("SELECT (OUTY0, OUTY0, OUTY1, OUTY2, OU........
        at java.util.TimerThread.mainLoop(Timer.java:512)
        at java.util.TimerThread.run(Timer.java:462)

Open in new window

So the first error happens at LionFXConnet.java:45 at ps.executeQuery(),
so suggest before this ps.executeQuery()

But if you say

PreparedStatement ps = conn.preapreaStatement(sql);

What is your string sql - ?
It is probably something with ???? instead of parameters.
After that you need to assign paramters

like ps.setFloat(1,x); etc.
and only then you should make ps.executeQuery();
and in your code you first prepare statement and then immediately execute it.
Am I right?

 




Avatar of gbcbr

ASKER

I change it like this:
public ResultSet select(String sql) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException  {
        Class.forName(driverName).newInstance();
        conn = DriverManager.getConnection(databaseurl, user, password);
        sql = ("SELECT (?,?,?,?,?,?,?,?,?,?) FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1");
        ps = conn.prepareStatement(sql);
        ps.setFloat(1, (float) OUTY0);
        ps.setFloat(2, (float) OUTY1);
        ps.setFloat(3, (float) OUTY2);
        ps.setFloat(4, (float) OUTY3);
        ps.setFloat(5, (float) OUTY4);
        ps.setFloat(6, (float) OUTY5);
        ps.setFloat(7, (float) OUTY6);
        ps.setFloat(8, (float) OUTY7);
        ps.setFloat(9, (float) OUTY8);
        ps.setFloat(10, (float) OUTY9);
        rs = ps.executeQuery();
        return rs;
    }

......................

public void run() {
        try {
            ResultSet rs = conn.select(sql);

                outY[0] = rs.getDouble("OUTY0");
                outY[1] = rs.getDouble("OUTY1");

Open in new window

but the same error.
ORA-00907: missing right parenthes
in the same line >> rs = ps.executeQuery();
ASKER CERTIFIED SOLUTION
Avatar of aciuica
aciuica
Flag of Romania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gbcbr

ASKER

Yes, I found this 10 minutes ago, I don't need (OUTY0, OUTY1, OUTY2, OUTY3, OUTY4), it works well, but I still try to use  
sql = ("SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ? FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1");
        ps = conn.prepareStatement(sql);
        ps.setFloat(1, OUTY0);
        ps.setFloat(2, OUTY1);
        ps.setFloat(3, OUTY2);
        ps.setFloat(4, OUTY3);
        ps.setFloat(5, OUTY4);
        ps.setFloat(6, OUTY5);
        ps.setFloat(7, OUTY6);
        ps.setFloat(8, OUTY7);
        ps.setFloat(9, OUTY8);
        ps.setFloat(10, OUTY9);
        rs = ps.executeQuery();
        return rs;

Open in new window

because it more transparent construction in case when I will need to change query.
But this issue error Invalid column name at SelectData >> outY[0] = rs.getDouble("OUTY0");
 
public void run() {
        try {
            ResultSet rs = conn.select (sql);
//                    ("SELECT OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9 FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1");

            while (rs.next()) {

            outY[0] = rs.getDouble("OUTY0");
            outY[1] = rs.getDouble("OUTY1");
            outY[2] = rs.getDouble("OUTY2");
            outY[3] = rs.getDouble("OUTY3");
            outY[4] = rs.getDouble("OUTY4");
            outY[5] = rs.getDouble("OUTY5");
            outY[6] = rs.getDouble("OUTY6");
            outY[7] = rs.getDouble("OUTY7");
            outY[8] = rs.getDouble("OUTY8");
            outY[9] = rs.getDouble("OUTY9");

            equals(rs);

Open in new window

Please advice
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gbcbr

ASKER

Yes, now it works, but please look at code at the beginning of the question, it's exactly the same with *, why it didn't work there?
I spend 1 day to come back to my code.
The only difference in this line >> while (rs.next()) {
But it has no relation with ORA-00907: missing right parenthes
This is my last working code.
public void run() {
        try {
            ResultSet rs = conn.select
           ("SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1");
            while (rs.next()) {

            outY[0] = rs.getDouble("OUTY0");
            outY[1] = rs.getDouble("OUTY1");
            outY[2] = rs.getDouble("OUTY2");
            outY[3] = rs.getDouble("OUTY3");
            outY[4] = rs.getDouble("OUTY4");
            outY[5] = rs.getDouble("OUTY5");
            outY[6] = rs.getDouble("OUTY6");
            outY[7] = rs.getDouble("OUTY7");
            outY[8] = rs.getDouble("OUTY8");
            outY[9] = rs.getDouble("OUTY9");

            equals(rs);

Open in new window

But the error it was writing in the beginning - it was generated by another line - not by this
one - when you added printStackTrace() - it pointed to another line - not at this one;
this has good SQL and this line
SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1
 has no problems
Avatar of gbcbr

ASKER

This connect rs method which is exactly the same as at the beginning
public ResultSet select(String sql) throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class.forName(driverName).newInstance();
        conn = DriverManager.getConnection(databaseurl, user, password);
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        return rs;
    }

Open in new window

because I restore it and everything work OK.
What created this error before?
No, the previous excerpt the very first one with
SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1
made sense.

The last excerpt:

        conn = DriverManager.getConnection(databaseurl, user, password);
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();

does not make much sense - you first need to
create PreparedStatemnt, then assign values
to its parametes, then execute - otherwise it does not make much sense -
add System.out.println(sql) after orr just before  ps = conn.prepareStatemnt()
You'll see all these ???? -which it cannpot execute until you assign actual values







SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gbcbr

ASKER

this what tell you, this construction works well apart of your explanation that it's not correct.
Avatar of gbcbr

ASKER

for me is the question why it stops work yesterday and today morning.
Anyway, thank you for your lessons, I find many new things for me.
Well, we wrere probably typing simultaneously -
this construction does work if there are no ??? inside,
but if there are ????, and some paramteres are left unset - it would not work

Therefore the same piece of code

        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();

would either work or not work,
but if you had printed out System.out.println(sql)
in both cases you would have found that in case where it didn't work you have
not set some or all of the ????

Now we already cannot check, but when this code didn't work for you you happended to have
unset ??? still inside


Avatar of gbcbr

ASKER

I never use before in this method ???, it was like I post last time exactly, I start put them after your suggestion only.
So, the problem was not in ???
Well, now we already cannot check, because if you corrected somewhere you
cannot know where it was broken.

At least if we now look through the trail - we have no proof that your exact line

SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1

was exactly the one which caused the problem.

First you didn't have exact  line of where exception was
happening, and when you did print ex.printStackTrace() it pointed
already at another line, the one with ps.executeQuery()
And I guess the diagnostics of the error - "...missing comma" seems to be fitting
with the situation when you use unset parameters in PrepStatmnt
but does not fit with the perfectly good SQL
SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1

I always have ex.printStackTrace() within in my catch loops,
because it is very easy to misjudge which line in particular is thrwoing the exception

Another observation is that computers a very deterministic - almost in all cases
when it seems I'm doing everything the same and it behaves differently it in the end
turns out that I after all didn't do everything exactly the same.