Timestamp Field

[former_member187444]

Hi Friends;

I have oracle database and one of myfield is timestamp.

I want to execute insert statement in jsp. but it always gives me

2007-05-02 17:51:36.321 The SQL statement 
"INSERT INTO "TBL_MSG" ("ID","SENDER","RECIPIENT","SUBJECT",
"BODY","ATTACHMENTS","SENTDATE","IP","STATUS","VIEWTIMES") 
VALUES ('125345345','TESTFIT','TESTFIT','sdfgsadgasdg',' qertertsd',
'asd','02-May-07 05:11:55','10.1.21.88','0',0)" 
contains the semantics error[s]: - type check error: 
new value (element number 7 (CHAR)) is not assignable 
to column >>SENTDATE<< (TIMESTAMP) 

How can i insert this timestamp field in jsp?

Thanks

Best Regards

[former_member182372 · Accepted solution]

Eray, how do you build your SQL statement? Post Java code.

[former_member187444 · Accepted solution]

Hi;

JSP code is below

Thanks

<%
java.sql.Connection con = null;
try {
	InitialContext ctx = new InitialContext();
	javax.sql.DataSource ds =
             (javax.sql.DataSource) ctx.lookup("jdbc/SAPSR3DB");
	con = ds.getConnection();
} catch (NamingException e) {
	out.println(e.getMessage());
} catch (SQLException e) {
	out.println(e.getMessage());
}
try{
    java.sql.Statement stat = con.createStatement();
    java.rmi.server.UID userId = new java.rmi.server.UID();
    stat.execute("INSERT INTO TBL_MSG (ID,SENDER,RECIPIENT,SUBJECT,BODY,"+
		" ATTACHMENTS,SENTDATE,IP,STATUS,VIEWTIMES) "+
                " VALUES ('"+userId.toString()+"','TESTFIT','TESTFIT', "+
               "  'sdfgsadgasdg', '<p>qertertsd</p>',"+
                " 'asd','02-May-07 05:11:55','10.1.21.88','0',0)");
				
				
}
}catch(java.lang.Exception ex){
	out.println(ex.getMessage());
}

%>

[Former Member · Accepted solution]

stat.execute("INSERT INTO TBL_MSG (ID,SENDER,RECIPIENT,SUBJECT,BODY,"+
		" ATTACHMENTS,SENTDATE,IP,STATUS,VIEWTIMES) "+
                " VALUES ('"+userId.toString()+"','TESTFIT','TESTFIT', "+
               "  'sdfgsadgasdg', '<p>qertertsd</p>',"+
                " 'asd',to_date('02-May-07 05:11:55','mm-dd-yyyy hh24misi'),'10.1.21.88','0',0)");

Basically use the oracle method to convert the timestamp string into a time or timestamp value.

Dennis

[former_member182372 · Accepted solution]

Try this one

		PreparedStatement stat = con.prepareStatement("INSERT INTO TBL_MSG (ID,SENDER,RECIPIENT,SUBJECT,BODY,ATTACHMENTS,SENTDATE,IP,STATUS,VIEWTIMES) VALUES (?,?,?,?,?,?,?,?,?,?)");
		java.rmi.server.UID userId = new java.rmi.server.UID();
		
		stat.setString(1, userId.toString());
		stat.setString(2, "TESTFIT");
		stat.setString(3, "TESTFIT");
		stat.setString(4, "sdfgsadgasdg");
		stat.setString(5, "<p>qertertsd</p>");
		stat.setString(6, "asd");
		stat.setTimestamp(7, new Timestamp(2007, 5, 2, 5, 11, 55 ,0));
		stat.setString(8, "10.1.21.88");
		stat.setString(9, "0");
		stat.setString(10, "0");
		
		stat.execute();

[Former Member]

Hi,

Try this Code

stat.execute("INSERT INTO TBL_MSG (ID,SENDER,RECIPIENT,SUBJECT,BODY,"+

" ATTACHMENTS,SENTDATE,IP,STATUS,VIEWTIMES) "+

" VALUES ('"+userId.toString()+"','TESTFIT','TESTFIT', "+

"  'sdfgsadgasdg', '<p>qertertsd</p>',"+

" 'asd',to_date('02-May-07 05:11:55','mm-dd-yyyy hh24misi'),'10.1.21.88','0',0)");

Regards,

Beevin.

[former_member187444]

Hi Dennis and Joseph;

If i try yours i get always

<b>Details: com.sap.sql.log.OpenSQLException: The SQL statement "INSERT INTO TBL_MSG (ID,SENDER,RECIPIENT, SUBJECT, BODY, ATTACHMENTS, SENTDATE, IP, STATUS, VIEWTIMES) VALUES ('-4c09be60:1124128e62d:-7fce','TESTFIT','TESTFIT','34645745','<p>asdgadfhg</p>','A', to_date('03-May-07 11:05:29','mm-dd-yyyy hh24:mi:si') ,'10.1.21.88','0',0)" contains the syntax error[s]: - 1:206 - SQL syntax error: the token "(" was not expected here

</b>

Why doesnt accept '(' ???

Thanks in advance

Best Regards

[Former Member]

hi,

I have made some changes.try it

stat.execute("INSERT INTO TBL_MSG (ID,SENDER,RECIPIENT,SUBJECT,BODY,"+
 
" ATTACHMENTS,SENTDATE,IP,STATUS,VIEWTIMES) "+
 
" VALUES ('"+userId.toString()+"','TESTFIT','TESTFIT', "+
 
"  'sdfgsadgasdg', '<p>qertertsd</p>',"+
 
" 'asd',to_date('02-May-07 05:11:55','DD-MON-YY hh24:mi:si'),'10.1.21.88','0',0)");

Regards,

Beevin.

[former_member187444]

Hi again!

I have tried many example about to_date but still problem occurs.

Also i tried oracle's example but i coultdnt resolve with this way.

But i have solved it via PreparedStatement!

Thanks

[former_member182372]

Eray, if the problem is solved, please, assign points and mark thread as solved.