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

asked on

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

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Is any of your data varchar2?  You will need single quotes around those.
Avatar of gbcbr

ASKER

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


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
Avatar of gbcbr

ASKER

double[] outY = new double[10];
Timestamp[] timest = new Timestamp[10];

Open in new window

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
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
Avatar of gbcbr

ASKER

>>I highly recommend using binds
?
Please advice

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] + "')";

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

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

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

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

Does it work now?
> 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
Avatar of gbcbr

ASKER

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?
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

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?
Avatar of gbcbr

ASKER

Thank you for the good lesson