CODEWITHSUNDEEP
javasqldatabaseplsql
user2512806
user2512806

Reputation: 441

Convert a String in Java to a VARCHAR IN SQL

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)

Upvotes: 2

Views: 6360

Answers (1)

Reimeus
Reimeus

Reputation: 159784

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

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

Upvotes: 5

Related Questions