Solved

ORA-00917

Posted on 2011-02-11
18
520 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 76

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 73

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
 

Author Comment

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

Open in new window

0
 
LVL 73

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 73

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 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
This video teaches viewers about errors in exception handling.

867 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

18 Experts available now in Live!

Get 1:1 Help Now