Solved

SQL statement reply

Posted on 2011-02-12
23
286 Views
Last Modified: 2012-05-11
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
Comment
Question by:gbcbr
  • 11
  • 9
  • 2
  • +1
23 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 34880673


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
 
LVL 40

Expert Comment

by:Sharath
ID: 34880682
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34880691
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34880725
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34880766
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
 

Author Comment

by:gbcbr
ID: 34881762
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34881775
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
 

Author Comment

by:gbcbr
ID: 34882135
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34882152
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
 

Author Comment

by:gbcbr
ID: 34882216
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
 
LVL 5

Accepted Solution

by:
aciuica earned 200 total points
ID: 34882393
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:gbcbr
ID: 34882418
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
 
LVL 5

Assisted Solution

by:aciuica
aciuica earned 200 total points
ID: 34882535
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
 

Author Comment

by:gbcbr
ID: 34882906
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34882981
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
 

Author Comment

by:gbcbr
ID: 34883017
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34883037
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
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 300 total points
ID: 34883067
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
 

Author Comment

by:gbcbr
ID: 34883071
this what tell you, this construction works well apart of your explanation that it's not correct.
0
 

Author Comment

by:gbcbr
ID: 34883086
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34883097
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
 

Author Comment

by:gbcbr
ID: 34883112
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
 
LVL 47

Expert Comment

by:for_yan
ID: 34883157
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Fibonacci challenge 11 84
mapAB Challlenge 35 89
topping3 challenge 14 51
word0 challenge 3 58
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now