• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

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
0
gbcbr
Asked:
gbcbr
  • 11
  • 9
  • 2
  • +1
3 Solutions
 
for_yanCommented:


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.










0
 
SharathData EngineerCommented:
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");
0
 
for_yanCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
for_yanCommented:
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
0
 
for_yanCommented:
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
0
 
gbcbrAuthor Commented:
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?
0
 
for_yanCommented:
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?



0
 
gbcbrAuthor Commented:
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

0
 
for_yanCommented:
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?

 




0
 
gbcbrAuthor Commented:
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();
0
 
aciuicaCommented:
I think that you don't need ? in this case because you don't have parameters.
What I see wrong in your previews sql are the brackets around the fields after SELECT. Maybe you can try:
SELECT OUTY0, OUTY1, OUTY2, OUTY3, OUTY4 FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1
0
 
gbcbrAuthor Commented:
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
0
 
aciuicaCommented:
Here you try to set some floats values fore columns names parameters. Even if you set Strings this doesn't work. I don't think that you need to use ? for column names in this sql statement.
It seems like you need almost all columns from this table.
So maybe is better to use SELECT * FROM ... in this case - is more flexible.
Else you can build a small framework which create SQL string from table name, column names and WHERE clause.
See here a sample of a simple sqlbuider: http://openhms.sourceforge.net/sqlbuilder/
0
 
gbcbrAuthor Commented:
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

0
 
for_yanCommented:
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
0
 
gbcbrAuthor Commented:
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?
0
 
for_yanCommented:
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







0
 
for_yanCommented:
And it makes no sense to create prepared satement, if you
don't have parametrs within it. In this case, just use
normal Statemnt (not prepared).

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM RBF_DATA where timest = ( select max( timest ) from RBF_DATA ) and rownum = 1");

Maybe now in your sql strng you don't have any ????, so it still works, and previous time you had ???
them inside sql - and it didn't work. In any case there is no reason to use PreparedStatement
if you don't have parameters to set inside of it. Without parameters just Statement is
is more straightforward.
0
 
gbcbrAuthor Commented:
this what tell you, this construction works well apart of your explanation that it's not correct.
0
 
gbcbrAuthor Commented:
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.
0
 
for_yanCommented:
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


0
 
gbcbrAuthor Commented:
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 ???
0
 
for_yanCommented:
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.













0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 11
  • 9
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now