ORA-00917

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

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

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

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

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

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

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();
{

Select all 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]);

Select all Open in new window

oops, yes, good catch

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

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

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

Select all Open in new window

how to change it, or it's not necessary?

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?

Thank you for the good lesson