Convert a String in Java to a VARCHAR IN SQL

Question

I am trying to write PL/SQL in Java. I want to pass an argument as String, but in the SQL Procedure it is a VARCHAR(140). However it is giving me an error when I run the program and I don't understand why?

This is my SQL PROCEDURE:

CREATE OR REPLACE PROCEDURE task1
 (input_tweet_id IN integer, input_user_id IN integer, input_time IN timestamp, input_tweet IN varchar(140))
is
begin
    INSERT INTO tweet(tweetid, userid, publishtime, tweet)
    VALUES (input_tweet_id,input_user_id,input_time,input_tweet);
END;

The Java call:

    int in_tweet_id = 0;
    int in_user_id = 0;
    Timestamp in_time =null;
    String in_tweet ="";

 Connection conn = DriverManager.getConnection (connStr, uname, passwd);
 CallableStatement cstmt = conn.prepareCall("{? = call task1(?, ?)}");
       // cstmt.registerOutParameter (1, oracle.jdbc.OracleTypes.CURSOR);
        cstmt.setInt(1, in_tweet_id);
        cstmt.setInt(2, in_user_id);
        cstmt.setTimestamp(3,in_time);
        cstmt.setString(4,in_tweet);

The error:

 at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:197)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:269)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:490)
        at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5013)
        at oracle.jdbc.driver.OracleCallableStatement.setString(OracleCallableStatement.java:4094)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:246)
        at hw4.execute_task1(hw4.java:247)
        at hw4.main(hw4.java:101)

You luckily removed the exception message from the stacktrace, because it could be helpful. — Tom
– Tom, Commented Apr 15, 2015 at 17:45

Reimeus · Accepted Answer · 2015-04-15 18:10:28Z

5

You are attempting to set the 3rd & 4th CallableStatement parameters where there are only 2 defined

CallableStatement cstmt = conn.prepareCall("{call task1(?, ?, ?, ?)}");

edited Apr 15, 2015 at 18:10

answered Apr 15, 2015 at 17:38

Reimeus

160k16 gold badges225 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user2512806 Over a year ago

Yeah I changed that, but does it need to have the "?" in the very front? {? = call .......} Does that need to be there because i am not returning anything ?

user2512806 Over a year ago

I changed it to this : CallableStatement cstmt = conn.prepareCall("{call task1(?,?,?,?)}");

Reimeus Over a year ago

No need, take a look at this example

user2512806 Over a year ago

And I get the following error: Exception in thread "main" java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00905: object USER.TASK1 is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Reimeus Over a year ago

Can you check the type being sent to column 7 with the type that's being sent in? Looks like a mismatch...

|

Collectives™ on Stack Overflow

Convert a String in Java to a VARCHAR IN SQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related