Solved

ORA-00917

Posted on 2011-02-11
18
539 Views
Last Modified: 2012-05-11
Please advice where is mistake:
sql_Y = "INSERT INTO RBF_DATA" + "(OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, TIMEST) "
//                + "VALUES" + "(" + outY[0] + "','" + outY[1] + "','" + outY[2] + "','" + outY[3] + "','" + outY[4] + "','"
//                + outY[5] + "','" + outY[6] + "','" + outY[7] + "','" + outY[8] + "','" + outY[9] + "','" + timest[0] + ")";


//        sql_Y = "INSERT INTO RBF_DATA" + "(OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, TIMEST) "
//                + "VALUES" + "(" + outY[0] + "," + outY[1] + "," + outY[2] + "," + outY[3] + "," + outY[4] + ","
//                + outY[5] + "," + outY[6] + "," + outY[7] + "," + outY[8] + "," + outY[9] + "," + timest[0] + ")";

        sql_Y = "INSERT INTO RBF_DATA" + "(OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, TIMEST) "
                + "VALUES" + "(" + outY[0] + ',' + outY[1] + ',' + outY[2] + ',' + outY[3] + ',' + outY[4] + ','
                + outY[5] + ',' + outY[6] + ',' + outY[7] + ',' + outY[8] + ',' + outY[9] + ',' + timest[0] + ")";

Open in new window

0
Comment
Question by:gbcbr
  • 7
  • 5
  • 4
  • +2
18 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34873685
Is any of your data varchar2?  You will need single quotes around those.
0
 

Author Comment

by:gbcbr
ID: 34873697
CREATE TABLE "LIONFX"."RBF_DATA"
  (
    "ID" NUMBER NOT NULL ENABLE,
    "OUTY0" FLOAT(126),
    "OUTY1" FLOAT(126),
    "OUTY2" FLOAT(126),
    "OUTY3" FLOAT(126),
    "OUTY4" FLOAT(126),
    "OUTY5" FLOAT(126),
    "OUTY6" FLOAT(126),
    "OUTY7" FLOAT(126),
    "OUTY8" FLOAT(126),
    "OUTY9" FLOAT(126),
    "TIMEST" TIMESTAMP (0),

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34873710

since you're building a java string you want to append with  
 + "," +
not

 + ',' +


what are your outY  and timest elements?
if any of your elements have a ")" character in them or a "'" character you'll have a problem

your code is begging for sql injection problems.

better would be to use bind variables
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gbcbr
ID: 34873731
double[] outY = new double[10];
Timestamp[] timest = new Timestamp[10];

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34873733
your timest  value can't be inserted directly through string concatenation

either use binds

or

 "," + timest[0] + ")";

must be something like...

 ",to_timestamp('" + timest[0] + "','yyyy-mm-dd hh24:mi:ssxff'))";

I'm assuming your timest[0] is a string,  if it's not, you'll need to convert it to a string that corresponds to the same format as the to_timestamp call
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34873773
since timest[0] is a Timestamp  then you'll need to convert it with


",to_timestamp('" + timest[0].toString()  + "','yyyy-mm-dd hh24:mi:ssxff'))";




or use binds
I highly recommend using binds,  your dba will thank you, and your code will be simpler
0
 

Author Comment

by:gbcbr
ID: 34873802
>>I highly recommend using binds
?
Please advice
0
 
LVL 47

Expert Comment

by:for_yan
ID: 34873804

In this command

sql_Y = "INSERT INTO RBF_DATA" + "(OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, TIMEST) "
                + "VALUES" + "(" + outY[0] + ',' + outY[1] + ',' + outY[2] + ',' + outY[3] + ',' + outY[4] + ','
                + outY[5] + ',' + outY[6] + ',' + outY[7] + ',' + outY[8] + ',' + outY[9] + ',' + timest[0] + ")";

you should
+"," + everyweher instaed of +','+ - your commas should be in double quotes

timestamp - is it a string ?

then in the end you need to have it to enclose in single quote + ",'"  + timest[0] + "')";

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 350 total points
ID: 34873858
using binds your code might look something like this...

PreparedStatement pstat =
    conn.prepareStatement
    ("INSERT INTO RBF_DATA (OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, TIMEST) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
    for (int i = 0; i <= 9; i++) {
        pstat.setFloat( i, outY[i]);
    }
    pstat.setTimestamp( 10, timest[0]);
    ResultSet rset = pstat.executeQuery();

Open in new window

   
0
 

Author Comment

by:gbcbr
ID: 34874124
public void insert(double[] outY, Timestamp[] timest) throws SQLException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        Object newInstance = Class.forName(driverName).newInstance();
        conn = DriverManager.getConnection(databaseurl, user, password);
         System.out.println(" conn insert =>  " + conn + " : " + new java.util.Date());
         PreparedStatement pstat =
    conn.prepareStatement
    ("INSERT INTO RBF_DATA (OUTY0, OUTY1, OUTY2, OUTY3, OUTY4, OUTY5, OUTY6, OUTY7, OUTY8, OUTY9, TIMEST) VALUES (?,?,?,?,?,?,?,?,?,?,?)");
    for (int i = 0; i <= 9; i++) {
        pstat.setFloat( i, (float) outY[i]);
    }
    pstat.setTimestamp( 10, timest[0]);
    ResultSet rset = pstat.executeQuery();
{

Open in new window

-0.41104586321728726  = outY[0]  Fri Feb 11 21:08:03 EET 2011
Timestamp 2   : 2011-02-11 19:07:39.0
 conn insert =>  oracle.jdbc.driver.T4CConnection@3844006e : Fri Feb 11 21:08:04 EET 2011
11.02.2011 21:08:04 connect.SelectData run
SEVERE: null
java.sql.SQLException: Invalid column index
        at oracle.jdbc.driver.OraclePreparedStatement.setFloatInternal(OraclePreparedStatement.java:4810)
        at oracle.jdbc.driver.OraclePreparedStatement.setFloat(OraclePreparedStatement.java:4797)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setFloat(OraclePreparedStatementWrapper.java:190)
        at connect.LionFXConnect.insert(LionFXConnect.java:63) >> pstat.setFloat( i, (float) outY[i]);

Open in new window

0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 150 total points
ID: 34874137
columns in SQL start from one, not from zero
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34874163
oops, yes, good catch

 for (int i = 0; i <= 9; i++) {
        pstat.setFloat( i+1, outY[i]);
    }
pstat.setTimestamp( 11, timest[0]);

Open in new window

0
 
LVL 47

Expert Comment

by:for_yan
ID: 34874177
Does it work now?
0
 
LVL 92

Expert Comment

by:objects
ID: 34875718
> your code is begging for sql injection problems.
> better would be to use bind variables

Theres a discussion of that here:

http://helpdesk.objects.com.au/java/use-preparedstatement-to-protect-against-sql-injection
0
 

Author Comment

by:gbcbr
ID: 34878151
what about this code?
 
conn.select("SELECT * FROM EURUSD where timest = ( select max( timest ) from EURUSD ) and rownum = 1 union all " +
                               "SELECT * FROM EURCHF where timest = ( select max( timest ) from EURCHF ) and rownum = 1 union all " +
                               "SELECT * FROM EURGBP where timest = ( select max( timest ) from EURGBP ) and rownum = 1 union all " +
                               "SELECT * FROM USDCHF where timest = ( select max( timest ) from USDCHF ) and rownum = 1 union all " +
                               "SELECT * FROM GBPUSD where timest = ( select max( timest ) from GBPUSD ) and rownum = 1 union all " +
                               "SELECT * FROM GBPCHF where timest = ( select max( timest ) from GBPCHF ) and rownum = 1 union all " +
                               "SELECT * FROM EURJPY where timest = ( select max( timest ) from EURJPY ) and rownum = 1 union all " +
                               "SELECT * FROM USDJPY where timest = ( select max( timest ) from USDJPY ) and rownum = 1 union all " +
                               "SELECT * FROM GBPJPY where timest = ( select max( timest ) from GBPJPY ) and rownum = 1 union all " +
                               "SELECT * FROM CHFJPY where timest = ( select max( timest ) from CHFJPY ) and rownum = 1");

            while (rs.next()) {

                if (rs.getString("SYMBOL").equals("EUR/USD")) {
                    symbol[0] = rs.getString("SYMBOL");
                    timest[0] = rs.getTimestamp("TIMEST");
                    openBid[0] = rs.getDouble("BIDPX");
                    openAsk[0] = rs.getDouble("ASKPX");
                } else if (rs.getString("SYMBOL").equals("EUR/CHF")) {
                    symbol[1] = rs.getString("SYMBOL");
                    timest[1] = rs.getTimestamp("TIMEST");
                    openBid[1] = rs.getDouble("BIDPX");
                    openAsk[1] = rs.getDouble("ASKPX");
................................................

                    symbol[9] = rs.getString("SYMBOL");
                    timest[9] = rs.getTimestamp("TIMEST");
                    openBid[9] = rs.getDouble("BIDPX");
                    openAsk[9] = rs.getDouble("ASKPX");

                    equals(rs);

Open in new window

how to change it, or it's not necessary?
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 150 total points
ID: 34878242
Not quite understand - do you have all this data in numerous tables and you want to select the latest transactions from each table - is it true?
In any case, I don't think your java part is not quite OK, as you should not have rs.getString("symbol") more than once within the rs.next() loop.
You first assign String s = rs.geString("symbol") and then use this string for conditions.
0
 

Author Comment

by:gbcbr
ID: 34878326
Thank you for advice.

>>you want to select the latest transactions from each table - is it true?

yes.

Do I need to move query from this class to ConnectDB class into the method select?
0
 

Author Closing Comment

by:gbcbr
ID: 34878823
Thank you for the good lesson
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

756 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