Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

ORA-00917

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
gbcbr
Asked:
gbcbr
  • 7
  • 5
  • 4
  • +2
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Is any of your data varchar2?  You will need single quotes around those.
0
 
gbcbrAuthor Commented:
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
 
sdstuberCommented:

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!

 
gbcbrAuthor Commented:
double[] outY = new double[10];
Timestamp[] timest = new Timestamp[10];

Open in new window

0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
gbcbrAuthor Commented:
>>I highly recommend using binds
?
Please advice
0
 
for_yanCommented:

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
 
sdstuberCommented:
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
 
gbcbrAuthor Commented:
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
 
for_yanCommented:
columns in SQL start from one, not from zero
0
 
sdstuberCommented:
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
 
for_yanCommented:
Does it work now?
0
 
objectsCommented:
> 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
 
gbcbrAuthor Commented:
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
 
for_yanCommented:
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
 
gbcbrAuthor Commented:
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
 
gbcbrAuthor Commented:
Thank you for the good lesson
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now