f



JDBC error with mysql timediff value (java.sql.SQLException: Bad format for Time)

I am running a mysql query that creates a timediff value and sends
back to a java servlet

I am getting a java error  (Error code 0, SQL state S1009:
java.sql.SQLException: Bad format for Time '410:37:42' in column 5)

So am wondering do I fix this in mysql land or java land? Can I modify
the timediff to an integer in mysql so JDBC will like it?

should I post this to a java group?
0
6/12/2009 9:54:16 AM
comp.databases.mysql 6944 articles. 1 followers. laredotornado (869) is leader. Post Follow

5 Replies
2301 Views

Similar Articles

[PageSpeed] 46

terry433iid@yahoo.com: 
> I am running a mysql query that creates a timediff value and sends
> back to a java servlet
> 
> I am getting a java error  (Error code 0, SQL state S1009:
> java.sql.SQLException: Bad format for Time '410:37:42' in column 5)
> 
> So am wondering do I fix this in mysql land or java land? Can I modify
> the timediff to an integer in mysql so JDBC will like it?

Have you considered TIMESTAMPDIFF() with the interval parameter set to 
seconds?


-- 
Erick
0
Erick
6/12/2009 10:00:46 AM
On 12 June, 11:00, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
wrote:
> terry433...@yahoo.com:
>
> > I am running a mysql query that creates a timediff value and sends
> > back to a java servlet
>
> > I am getting a java error  (Error code 0, SQL state S1009:
> > java.sql.SQLException: Bad format for Time '410:37:42' in column 5)
>
> > So am wondering do I fix this in mysql land or java land? Can I modify
> > the timediff to an integer in mysql so JDBC will like it?
>
> Have you considered TIMESTAMPDIFF() with the interval parameter set to
> seconds?
>
> --
> Erick

can't use seconds as the time difference could go as high as 100 hours
and this would be un-workable in seconds. I need hours/mins/seconds
0
6/12/2009 10:31:11 AM
terry433iid@googlemail.com: 
> On 12 June, 11:00, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
> wrote:
> > terry433...@yahoo.com:
> >
> > > Can I modify
> > > the timediff to an integer in mysql so JDBC will like it?
> >
> > Have you considered TIMESTAMPDIFF() with the interval parameter set to
> > seconds?
> 
> can't use seconds as the time difference could go as high as 100 hours
> and this would be un-workable in seconds. I need hours/mins/seconds

You lost me here. Didn't you say you wanted an integer?
100 hours is about 360000 seconds. Can't your applet/servlet handle 
such integer?

-- 
Erick
0
Erick
6/12/2009 10:34:11 AM
On 12 June, 11:34, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
wrote:
> terry433...@googlemail.com:
>
> > On 12 June, 11:00, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
> > wrote:
> > > terry433...@yahoo.com:
>
> > > > Can I modify
> > > > the timediff to an integer in mysql so JDBC will like it?
>
> > > Have you considered TIMESTAMPDIFF() with the interval parameter set to
> > > seconds?
>
> > can't use seconds as the time difference could go as high as 100 hours
> > and this would be un-workable in seconds. I need hours/mins/seconds
>
> You lost me here. Didn't you say you wanted an integer?
> 100 hours is about 360000 seconds. Can't your applet/servlet handle
> such integer?
>
> --
> Erick

oops - sorry - you're right

I can convert seconds back to hours in my servlet....

I'll try using TIMESTAMPDIFF(seconds) and ssee if that fixes things

thanks for your input
0
6/12/2009 10:38:14 AM
terry433iid@googlemail.com wrote:
> On 12 June, 11:00, Erick T. Barkhuis <erick.use-...@ardane.c-o-m>
> wrote:
>> terry433...@yahoo.com:
>>
>>> I am running a mysql query that creates a timediff value and sends
>>> back to a java servlet
>>> I am getting a java error  (Error code 0, SQL state S1009:
>>> java.sql.SQLException: Bad format for Time '410:37:42' in column 5)
>>> So am wondering do I fix this in mysql land or java land? Can I modify
>>> the timediff to an integer in mysql so JDBC will like it?
>> Have you considered TIMESTAMPDIFF() with the interval parameter set to
>> seconds?
>>
>> --
>> Erick
> 
> can't use seconds as the time difference could go as high as 100 hours
> and this would be un-workable in seconds. I need hours/mins/seconds

Why would this be unworkable?  100 hours is only 360,000 seconds.  Not a 
problem at all.

But your problem is in Java, not MySQL.  Please follow up in a Java 
newsgroup.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
0
jstucklex (14659)
6/12/2009 10:58:53 AM
Reply:

Similar Artilces:

java.sql.SQLException: java.sql.Time
I get the following error message... java.sql.SQLException: Value '00:00:00 can not be represented as java.sql.Time >From the following SQL call... SELECT COUNT(pageurl) AS PageCount, pageurl, MAX(datetime) AS LastAccess, SUM(VisitLength) as TotalVisitLength, VisitLength, RecordNum, SUM(CASE WHEN VisitLength IS NULL THEN 1 ELSE 0 END) AS NullVisits FROM clubvisits WHERE groupid = #clubid# GROUP BY pageurl ORDER BY #OrderKey# I am using ColdFusion 7 on MySQL 4.1.12a. I'm not a Java programmer - I don't know any Java, but ColdFusion acts essentially as a script la...

java.sql.SQLException: java.sql.Time
I get the following error message... java.sql.SQLException: Value '00:00:00 can not be represented as java.sql.Time >From the following SQL call... SELECT COUNT(pageurl) AS PageCount, pageurl, MAX(datetime) AS LastAccess, SUM(VisitLength) as TotalVisitLength, VisitLength, RecordNum, SUM(CASE WHEN VisitLength IS NULL THEN 1 ELSE 0 END) AS NullVisits FROM clubvisits WHERE groupid = #clubid# GROUP BY pageurl ORDER BY #OrderKey# I am using ColdFusion 7 (not my choice - don't laugh) on MySQL 4.1.12a. The error refers to the "VisitLength" field which is a "time" da...

JDBC with MySQL 3.23.47 and Tomcat 4.1.18 (DBCP borrowObject failed: java.sql.SQLException: Server configuration denies access to data source)
When trying to display a test.jsp page i get this error message in Tomcat: DBCP borrowObject failed: java.sql.SQLException: Server configuration denies access to data source org.apache.commons.dbcp.DbcpException: java.sql.SQLException: Server configuration denies access to data source I will post my server.xml context section, web.xml, test.jsp, and DBTest.java files: *********************************** server.xml *********************************** <Context path="/MySqlDataSource" docBase="MySqlDataSource" debug="5" reloadable="true" cros...

error mysql when I clean all the other null account in mysql database that located in table user
Dear all, I tried to clean the default information in the mysql database that located in user table. When use the >mysql -uroot -proot it is working, but when I clean and delete some data in the user table by calling the following SQL statement: DELETE FROM user WHERE User= ' '; DELETE FROM user WHERE User=' ' AND HOST='localhost'; DELETE FROM user WHERE User='root' AND Password=' '; after calling the above SQL statement, then I try to use >mysql -uroot -proot It displays the following error: ERROR 1045 (28000): Access denied for user 'root&...

How to keep update one Mysql database with another MySQL database?
I have two Mysql databases. One is the master and the other is slave. I need to keep update with slave database to keep some tables the same as master database. The first idea in my mind is to write a php script to read datas from these tables and write into slave database. But I still think it is a little complex to judge which code I need to write into slave databse. So, any good idea to do this job? Any tool I can use? -- Water Lin http://blog.waterlin.org Water Lin wrote: > I have two Mysql databases. One is the master and the other is slave. > > I need to keep update wi...

Is MySQL File Format Identical to SQL File Format?
A client has asked me to move some data into SQL file format, and I was wondering if I could use MySQL to do it. Is MySQL file format identical to SQL file format? Thanks in advance for the info. -Vik "Vik Rubenfeld" <vikr@mindspring.com.invalid> wrote in message news:vikr-1635F5.17284216012006@news.west.earthlink.net... > A client has asked me to move some data into SQL file format, and I was > wondering if I could use MySQL to do it. Is MySQL file format identical > to SQL file format? Thanks in advance for the info. > > > -Vik SQL stand...

Error with MySQL Database
Hi! I'd like to update my server. I have a Moodle System and I have done a backup of its MySQL database(version: 4.0.21) "mysqldump -u root -p - A > moodle.sql". Then I have tried importing database in other server ( 5.0.24a-Debian) "mysql -u root -p moodle < moodle.sql but I have had an error: ERROR 1064 (42000) at line 1132369: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'release int(2) unsigned default '0', display int(4) unsigned NOT NULL default ' at lin...

java.sql.SQLException: No suitable driver when connecting to a DB2 database using driver COM.ibm.db2.jdbc.DB2XADataSource
Hi, I want to get connection to a DB2 database using the driver COM.ibm.db2.jdbc.DB2XADataSource. I have also included 'db2java.zip' in the classpath. However I am getting the exception java.sql.SQLException: No suitable driver at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at Conn.main(Conn.java:44) The code that I am using (with try/catch removed) is as follows: String url = "jdbc:db2:sample"; String driver = "COM.ibm.db2.jdbc.DB2XADataSource"; String dbuser = "db2u...

MySQL Database error
I am trying to get JSP working with MySQL on redhat 9.0 I have installed MySQL server, Tomcat + Apache web serve, JVM and etc... If I just create a simple page with sample SQL Query it runs fine, but if I try using <jsp:useBean id="user" scope="session" class="user"/> <jsp:setProperty name="user" property="*"/> <jsp:useBean id="db" scope="session" class="DB"/> <jsp:setProperty name="db" property="cString" value="jdbc:mysql://127.0.0.1:3306/worctest?user...

JDBC/mysql error...
Hi everyone, I got an error, when i tried to connect mysql with java. Any suggestions ? Thank you. Bug Report ========== OS : RedHat Fedora Core 6 JSDK version : javac 1.5.0_12 [Linux version] MySQL version : mysql Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0 MySQL/JConnector version : mysql-connector-java-5.0.7 Query used for creating the db ============================== drop database movies; create database movies; use movies; create table movie ( id int not null auto_increment, title va...

Error Code :17410 Error State :null Error Message :No more data to read from socket Stack Trace : java.sql.SQLException: No more data to read from socket
Hi, I see the following errors in the logs: Error Code :17410 Error State :null Error Message :No more data to read from socket Stack Trace : java.sql.SQLException: No more data to read from socket at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:156) at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:803) at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:731) at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:690) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:372) at oracle.jdbc.ttc7...

Apache2 + MySQL Authentication = MySQL Syntax Error
Hello All, I am trying to setup Apache2 with MySQL Authentication. I can get it to work fine if I do not use group based authentication, i.e.; only look at the table for the user id and password. If I add the group directives I cannot login (it just keeps asking for the username and password) and I get a MySQL syntax error in my http error log. --------------------------------------- Here is my setup: OS = Mandrake 10.1 Apache = 2.0.50-7.2.101mdk MySQL Auth Mod = 2.0.50_1.11-3mdk MySQL = 4.0.20-3.1.101mdk Note that this was all installed via Mandrake RPMs. ---------------...

MATLAB to MySQL
Hello, I'm connecting MATLAB 2009b to MySQL 5.1. I wonder if it's possible to get any error messages from MySQL displayed in MATLAB? For example access denied or any other error message after executing mysql-command from MATLAB. Calling "handles.conn.Message" after typing a wrong password displays only: "JDBC Driver Error: com.mysql.jdbc.Driver. Driver Not Found/Loaded." Which is misleading. if isconnection(handles.conn) % do something; else disp(sprintf('Unable to connect: %s', handles.conn.Message)); Meanwhile a failure attempt to login to mysql...

setting lock table on mysql via java mysql-connector-java-3.1.7-bin.jar
Hi, I'm using MySQL database with Java and the connector (in subject line). I have read and searched the group prior to posting, and find no clear answer. I would like to lock my table for writing, so would it be like this? I have heard the suggestion, that executeUpdate in MySQL does a table lock automatically, is this true? Please feel free to instruct/assist.... Thanks.. try{ Statement stmt = connection.createStatement(); stmt.execute("LOCK TABLES databaseName.tableName WRITE"); int rowNumber = stmt.executeUpdate(sql); } catch(SQLException ex) { S...

ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
When i try to start my mysql server, i get this error ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) I usualy start it witn /bin/mysql I use Fedora core 3 Cristi ...

java.sql.Date value truncated using JDBC in MS-SQL Server
Hi, I'm trying to write a java.sql.Date to a database and the minutes/ seconds etc. are not being written. I've seen and tested many examples found via searches and still have come up with nothing. Here's what I have: java.sql.Date formation - recommended constructor forms: java.sql.Date entryDttm = new java.sql.Date(System.currentTimeMillis()); OR java.sql.Date entryDttm = new java.sql.Date(new java.util.Date().getTime()); // prepared statement insert pstmt.setDate(1, entryDttm); // what is written to database 2007-02-07 12:00:00.000 // what needs to be w...

SQL ERROR while executing the code java.sql.SQLException: ORA-01008: not all variables bound
Hi all i'm getting the above error plz tell me how to solve that error the code snippet is as bellow while(rst.next()){ con1=DriverManager.getConnection(url2,"rcxdev1","rcxdev1"); PreparedStatement pstmt=null; pstmt=con1.prepareStatement(sb.toString()); for(int i=0;i<mp.size();i++){ if((mp.get(rst.getMetaData().getColumnLabel(i+1)).equals("NUMBER"))) { pstmt.setInt(i+1,rst.getInt(i+1)); System.out.println("number "+i+" "+rst.getInt(i+1)); } if((mp.get(rst...

MySQL Error in SQL Statement
Hi All, I have this create table statement: CREATE TABLE jos_acajoom_stats_details ( listid int(10) NOT NULL default '0', mailing_id int(11) NOT NULL default '0', subscriber_id int(11) NOT NULL default '0', sentdate datetime NOT NULL default '0000-00-00 00:00:00', html tinyint(1) NOT NULL default '0', read tinyint(1) NOT NULL default '0', UNIQUE KEY listid (listid,mailing_id,subscriber_id) ) TYPE=MyISAM; I get this error message: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your...

Java and JSP to MySQL database
Dear all, I am planning to build my web application with the following architecture: Redhat Linux 9.0 platform Apache Tomcat Web Server MySQL database JSP with Java for writing the web page But unfortunately, I encountered some problems in properly linking my JSP to the mysql database. I have installed the JDBC connector interface already. I would apperciate if anyone can give me some hints on the proper set up and configuring of the architecture. Thanks a lot. Ivan --------------060406080708000601040002 Content-Type: text/plain; charset=us-ascii; format=flowed Co...

Connecting to a MySQL Database in Java
Hi all. I am trying to connect to a MySQL Server running locally on my machine. I have written a program before using MySQL which worked fine. I have copied over the MySQL.library program from the program that I have written before and adding the details into the project that I am doing now. I have tested the old program and that connects to the MySQL Database fine. However on the new program which uses the same MySQL connector and the same logon details to the MySQL Server except it keeps on coming up with the following error and I can't see why. The error message is java.sql.SQLException...

formating mysql timediff output
Is there a way to format output of timediff in following format: day hours minutes seconds? eg: timediff(Oct 16 2007 130000 - Oct 15 110000) = 1 days 2 hours 00 minutes 00 secs crybaby wrote: > Is there a way to format output of timediff in following format: day > hours minutes seconds? > > eg: > timediff(Oct 16 2007 130000 - Oct 15 110000) = 1 days 2 hours 00 > minutes 00 secs Yes On Oct 16, 5:21 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote: > crybaby wrote: > > Is there a way to format output of timediff in following format: day &g...

jdbc connect to mysql database
I am using JBuilder and I would like to connect to a mysql database. I was able to connect to a oracle database, however it does not work with mysql! I downloaded the newest drivers from the mysql webseite and put them into my java/bin directory. As I don't know exactly which java folder Jbuilder is using I have put them in both folder - my java folder and the java folder in jbuilder. Additionally, I tried to import the file from Jbuilder, and put them into "Project", "User Home" and "Jbuilder". Whatever that exactly does, with oracle it had worked. Using mys...

what is the difference between mysql and MySQL?
As some believe the two are not equal, what is the difference? richard: >As some believe the two are not equal, what is the difference? Do you ever try and read what is written in this group? See: From: "Denis McMahon" <denismfmcmahon@gmail.com> Date: Mon, 19 May 2014 22:16:37 +0000 (UTC) Message-ID: <lldvs5$787$2@dont-email.me> -- Erick On Tue, 20 May 2014 02:24:39 -0400, richard wrote: > As some believe the two are not equal, what is the difference? I posted an explanation of this yesterday which you clearly have not read, understood...

mysql and mysql.sock
I have not installed mysql on my own before because I just use whatever is installed with Linux. I have used Mandrake in the past, but I am using Debian now. I need to go from mysql version 3.x to 4.x to use a certain application so I downloaded the binary. After the appropriate setup, it worked fine sort of, but I started getting an error regarding mysql.sock, which apparently should be in either /tmp or /var/run/mysqld/. I cannot find any reference in the mysql literature regarding how, when or where this file is generated during the installation/setup process. The only way I ge...

Web resources about - JDBC error with mysql timediff value (java.sql.SQLException: Bad format for Time) - comp.databases.mysql

Resources last updated: 2/26/2016 5:04:05 PM