[groovy-user] groovy.sql.Sql Date paremeters

[alexey_baranov]

Why should I use java.sql.Timestamp wrapper when want to pass java.util.Date
parameter as in pure Java?

[code]
sql.execute('insert into table1 values(?)', [new java.sql.Timestamp(new
Date())])
[/code]

insted of simple

[code]
sql.execute('insert into table1 values(?)', [new Date()])
[/code]

Is it possible to automatically convert in this case?

[John Bito]

My recollection is that the jdbc drivers for several databases will not
accept java.util.Date in a parameter value - I believe the spec requires
that they accept java.sql.Date. I don't remember clearly how that came
about - I think it was because of the difference in resolution:
java.util.Date is an instant resolving to a fraction of a second while
java.sql.Date is a period covered by a calendar day.

If you mean a calendar day, an alternative would be to implement a property
for your object that might look like the one below.

def java.sql.Date getCurrentDate() {
return new java.sql.Date(System.currentTimeMillis())
}

Or if you mean an instant of time:

def java.sql.Timestamp getCurrentTime() {
return new java.sql.Timestamp(System.currentTimeMillis())
}

All jdbc drivers provide some coercion for parameters, allowing String
objects to be supplied for NUMBER columns, for instance. If Groovy is to
provide additional coercion, some specification would be in order (at least
an analysis of why java.util.Date is handled specially). The main pitfall
that I anticipate is that some vendor JDBC drivers already have mechanisms
to tailor the mapping between database types and Java, and even without that
they don't behave consistently.

If there's broader interest in enhanced type coercion for groovy.sql, I'd
consider spending some more time on it next month, after my current project
completes.

Of course, raising this as a possible enhancement in jira is a reasonable
first step.

Best Regards,
John

Post by alexey_baranov
Why should I use java.sql.Timestamp wrapper when want to pass
java.util.Date
parameter as in pure Java?
[code]
sql.execute('insert into table1 values(?)', [new java.sql.Timestamp(new
Date())])
[/code]
insted of simple
[code]
sql.execute('insert into table1 values(?)', [new Date()])
[/code]
Is it possible to automatically convert in this case?
--
http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25746598.html
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

[Tom Nichols]

Well, if you pass a java.util.Date, how does the framework know if you
_actually_ want a SQL Date (i.e. a day) or a Timestamp? Without
experimenting (and again, it might be vendor-dependent) you could
assume Timestamp if the driver automatically casts to the less-precise
SQL Date in those instances. That is, if you could insert a Timestamp
value for a Date column, Groovy SQL might be able to always cast
java.util.Date to Timestamp, and let the JDBC driver handle the loss
in precision. Or maybe you could inspect the type metadata for
inserts at least. But I wouldn't be surprised if there are gotchas to
either approach.

-Tom

My recollection is that the jdbc drivers for several databases will not accept java.util.Date in a parameter value - I believe the spec requires that they accept java.sql.Date.  I don't remember clearly how that came about - I think it was because of the difference in resolution: java.util.Date is an instant resolving to a fraction of a second while java.sql.Date is a period covered by a calendar day.
If you mean a calendar day, an alternative would be to implement a property for your object that might look like the one below.
def java.sql.Date getCurrentDate() {
  return new java.sql.Date(System.currentTimeMillis())
}
def java.sql.Timestamp getCurrentTime() {
  return new java.sql.Timestamp(System.currentTimeMillis())
}
All jdbc drivers provide some coercion for parameters, allowing String objects to be supplied for NUMBER columns, for instance.  If Groovy is to provide additional coercion, some specification would be in order (at least an analysis of why java.util.Date is handled specially).  The main pitfall that I anticipate is that some vendor JDBC drivers already have mechanisms to tailor the mapping between database types and Java, and even without that they don't behave consistently.
If there's broader interest in enhanced type coercion for groovy.sql, I'd consider spending some more time on it  next month, after my current project completes.
Of course, raising this as a possible enhancement in jira is a reasonable first step.
Best Regards,
John

Post by alexey_baranov
Why should I use java.sql.Timestamp wrapper when want to pass java.util.Date
parameter as in pure Java?
[code]
sql.execute('insert into table1 values(?)', [new java.sql.Timestamp(new
Date())])
[/code]
insted of simple
[code]
sql.execute('insert into table1 values(?)', [new Date()])
[/code]
Is it possible to automatically convert in this case?
--
View this message in context: http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25746598.html
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
   http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Tim Yates]

Maybe adding .asTimestamp() and .asSqlDate methods to the Date class would
be the RFE to go for?

Tim

Post by Tom Nichols
Well, if you pass a java.util.Date, how does the framework know if you
_actually_ want a SQL Date (i.e. a day) or a Timestamp? Without
experimenting (and again, it might be vendor-dependent) you could
assume Timestamp if the driver automatically casts to the less-precise
SQL Date in those instances. That is, if you could insert a Timestamp
value for a Date column, Groovy SQL might be able to always cast
java.util.Date to Timestamp, and let the JDBC driver handle the loss
in precision. Or maybe you could inspect the type metadata for
inserts at least. But I wouldn't be surprised if there are gotchas to
either approach.
-Tom

Post by John Bito
My recollection is that the jdbc drivers for several databases will not

accept java.util.Date in a parameter value - I believe the spec requires
that they accept java.sql.Date. I don't remember clearly how that came
java.util.Date is an instant resolving to a fraction of a second while
java.sql.Date is a period covered by a calendar day.

Post by John Bito
If you mean a calendar day, an alternative would be to implement a

property for your object that might look like the one below.

Post by John Bito
def java.sql.Date getCurrentDate() {
return new java.sql.Date(System.currentTimeMillis())
}
def java.sql.Timestamp getCurrentTime() {
return new java.sql.Timestamp(System.currentTimeMillis())
}
All jdbc drivers provide some coercion for parameters, allowing String

objects to be supplied for NUMBER columns, for instance. If Groovy is to
provide additional coercion, some specification would be in order (at least
an analysis of why java.util.Date is handled specially). The main pitfall
that I anticipate is that some vendor JDBC drivers already have mechanisms
to tailor the mapping between database types and Java, and even without that
they don't behave consistently.

Post by John Bito
If there's broader interest in enhanced type coercion for groovy.sql, I'd

consider spending some more time on it next month, after my current project
completes.

Post by John Bito
Of course, raising this as a possible enhancement in jira is a reasonable

first step.

Post by John Bito
Best Regards,
John

Post by alexey_baranov
Why should I use java.sql.Timestamp wrapper when want to pass

java.util.Date

Post by John Bito

Post by alexey_baranov
parameter as in pure Java?
[code]
sql.execute('insert into table1 values(?)', [new java.sql.Timestamp(new
Date())])
[/code]
insted of simple
[code]
sql.execute('insert into table1 values(?)', [new Date()])
[/code]
Is it possible to automatically convert in this case?
--

http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25746598.html

Post by John Bito

Post by alexey_baranov
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

[Tom Nichols]

That doesn't seem like a bad idea either, although it's not much less
code than "new Timestamp( someDate )" so is it really worth it?
That's what the GDK method would have to do anyway. Aren't the
"as..." methods in this case just something to trick your mind into
feeling like you're re-using something rather than creating something
new and throwing it away.

Off topic:
What's the logic for creating asTimestamp() vs using the asType(..)
method with a case for the Timestamp class? I guess in the first
example you don't need an import, but again, is it worth it just for
that?

-Tom

Maybe adding .asTimestamp() and .asSqlDate methods to the Date class would be the RFE to go for?
Tim

Post by Tom Nichols
Well, if you pass a java.util.Date, how does the framework know if you
_actually_ want a SQL Date (i.e. a day) or a Timestamp?  Without
experimenting (and again, it might be vendor-dependent) you could
assume Timestamp if the driver automatically casts to the less-precise
SQL Date in those instances.  That is, if you could insert a Timestamp
value for a Date column, Groovy SQL might be able to always cast
java.util.Date to Timestamp, and let the JDBC driver handle the loss
in precision.  Or maybe you could inspect the type metadata for
inserts at least.  But I wouldn't be surprised if there are gotchas to
either approach.
-Tom

My recollection is that the jdbc drivers for several databases will not accept java.util.Date in a parameter value - I believe the spec requires that they accept java.sql.Date.  I don't remember clearly how that came about - I think it was because of the difference in resolution: java.util.Date is an instant resolving to a fraction of a second while java.sql.Date is a period covered by a calendar day.
If you mean a calendar day, an alternative would be to implement a property for your object that might look like the one below.
def java.sql.Date getCurrentDate() {
  return new java.sql.Date(System.currentTimeMillis())
}
def java.sql.Timestamp getCurrentTime() {
  return new java.sql.Timestamp(System.currentTimeMillis())
}
All jdbc drivers provide some coercion for parameters, allowing String objects to be supplied for NUMBER columns, for instance.  If Groovy is to provide additional coercion, some specification would be in order (at least an analysis of why java.util.Date is handled specially).  The main pitfall that I anticipate is that some vendor JDBC drivers already have mechanisms to tailor the mapping between database types and Java, and even without that they don't behave consistently.
If there's broader interest in enhanced type coercion for groovy.sql, I'd consider spending some more time on it  next month, after my current project completes.
Of course, raising this as a possible enhancement in jira is a reasonable first step.
Best Regards,
John

Post by alexey_baranov
Why should I use java.sql.Timestamp wrapper when want to pass java.util.Date
parameter as in pure Java?
[code]
sql.execute('insert into table1 values(?)', [new java.sql.Timestamp(new
Date())])
[/code]
insted of simple
[code]
sql.execute('insert into table1 values(?)', [new Date()])
[/code]
Is it possible to automatically convert in this case?
--
View this message in context: http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25746598.html
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
   http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
   http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[alexey_baranov]

Unfortunately because of my poor English, I'm not able to understand all.
Just in case, I'l try to explain again in another way.

I was very young in the Java world. However, as far as I could understand
when people say java.util.Date they mean single moment in the time, not
date. Thus, java.util.date-is the logical equivalent of the DB timestamp, in
spite of the different terms.

When I do

"insert into table1 (?)", [new Date ()] for timestamp field, naturally
assume that it will work. This is intuitive and predictable, is not it?

In my opinion, in the groove should be added to type coercion

groovy.sql.Sql (java.util.date) -> java.sql.Connection (new
java.sql.Timestamp (param))


thanks for your time.

Post by John Bito
My recollection is that the jdbc drivers for several databases will not
accept java.util.Date in a parameter value - I believe the spec requires
that they accept java.sql.Date. I don't remember clearly how that came
java.util.Date is an instant resolving to a fraction of a second while
java.sql.Date is a period covered by a calendar day.
If you mean a calendar day, an alternative would be to implement a property
for your object that might look like the one below.
def java.sql.Date getCurrentDate() {
return new java.sql.Date(System.currentTimeMillis())
}
def java.sql.Timestamp getCurrentTime() {
return new java.sql.Timestamp(System.currentTimeMillis())
}
All jdbc drivers provide some coercion for parameters, allowing String
objects to be supplied for NUMBER columns, for instance. If Groovy is to
provide additional coercion, some specification would be in order (at least
an analysis of why java.util.Date is handled specially). The main pitfall
that I anticipate is that some vendor JDBC drivers already have mechanisms
to tailor the mapping between database types and Java, and even without that
they don't behave consistently.
If there's broader interest in enhanced type coercion for groovy.sql, I'd
consider spending some more time on it next month, after my current project
completes.
Of course, raising this as a possible enhancement in jira is a reasonable
first step.
Best Regards,
John

Post by alexey_baranov
Why should I use java.sql.Timestamp wrapper when want to pass
java.util.Date
parameter as in pure Java?
[code]
sql.execute('insert into table1 values(?)', [new java.sql.Timestamp(new
Date())])
[/code]
insted of simple
[code]
sql.execute('insert into table1 values(?)', [new Date()])
[/code]
Is it possible to automatically convert in this case?
--
http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25746598.html
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

[John Bito]

Perhaps someone would argue that we should encourage the use of Calendar
objects in Sql parameters. Since groovy.sql is not currently providing any
type coercion (beyond the intrinsic behavior of jdbc drivers), my argument
is that an enhancement should consider coercion in general, rather than
implementing a case specifically for Date.

Post by alexey_baranov
Unfortunately because of my poor English, I'm not able to understand all.
Just in case, I'l try to explain again in another way.
I was very young in the Java world. However, as far as I could understand
when people say java.util.Date they mean single moment in the time, not
date. Thus, java.util.date-is the logical equivalent of the DB timestamp, in
spite of the different terms.
When I do
"insert into table1 (?)", [new Date ()] for timestamp field, naturally
assume that it will work. This is intuitive and predictable, is not it?
In my opinion, in the groove should be added to type coercion
groovy.sql.Sql (java.util.date) -> java.sql.Connection (new
java.sql.Timestamp (param))
thanks for your time.

Post by John Bito
My recollection is that the jdbc drivers for several databases will not
accept java.util.Date in a parameter value - I believe the spec requires
that they accept java.sql.Date. I don't remember clearly how that came
java.util.Date is an instant resolving to a fraction of a second while
java.sql.Date is a period covered by a calendar day.
If you mean a calendar day, an alternative would be to implement a property
for your object that might look like the one below.
def java.sql.Date getCurrentDate() {
return new java.sql.Date(System.currentTimeMillis())
}
def java.sql.Timestamp getCurrentTime() {
return new java.sql.Timestamp(System.currentTimeMillis())
}
All jdbc drivers provide some coercion for parameters, allowing String
objects to be supplied for NUMBER columns, for instance. If Groovy is to
provide additional coercion, some specification would be in order (at least
an analysis of why java.util.Date is handled specially). The main

pitfall

Post by John Bito
that I anticipate is that some vendor JDBC drivers already have

mechanisms

Post by John Bito
to tailor the mapping between database types and Java, and even without that
they don't behave consistently.
If there's broader interest in enhanced type coercion for groovy.sql, I'd
consider spending some more time on it next month, after my current project
completes.
Of course, raising this as a possible enhancement in jira is a reasonable
first step.
Best Regards,
John

Post by alexey_baranov
Why should I use java.sql.Timestamp wrapper when want to pass java.util.Date
parameter as in pure Java?
[code]
sql.execute('insert into table1 values(?)', [new java.sql.Timestamp(new
Date())])
[/code]
insted of simple
[code]
sql.execute('insert into table1 values(?)', [new Date()])
[/code]
Is it possible to automatically convert in this case?
--

http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25746598.html

Post by John Bito

Post by alexey_baranov
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

--
http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25763257.html
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

[alexey_baranov]

I see no reason why can not be implemented both coercions at the same time.

java.util.Calendars of how I managed to understand is a service wrapper over
a java.util.Date that allows me to isolate the day, month and year, etc.
Both classes break down the time to fractions of seconds, and are logicaly
similar to DB timestamp.

Post by John Bito
Perhaps someone would argue that we should encourage the use of Calendar
objects in Sql parameters. Since groovy.sql is not currently providing any
type coercion (beyond the intrinsic behavior of jdbc drivers), my argument
is that an enhancement should consider coercion in general, rather than
implementing a case specifically for Date.

Post by alexey_baranov
Unfortunately because of my poor English, I'm not able to understand all.
Just in case, I'l try to explain again in another way.
I was very young in the Java world. However, as far as I could understand
when people say java.util.Date they mean single moment in the time, not
date. Thus, java.util.date-is the logical equivalent of the DB timestamp, in
spite of the different terms.
When I do
"insert into table1 (?)", [new Date ()] for timestamp field, naturally
assume that it will work. This is intuitive and predictable, is not it?
In my opinion, in the groove should be added to type coercion
groovy.sql.Sql (java.util.date) -> java.sql.Connection (new
java.sql.Timestamp (param))
thanks for your time.

Post by John Bito
My recollection is that the jdbc drivers for several databases will not
accept java.util.Date in a parameter value - I believe the spec

requires

Post by John Bito
that they accept java.sql.Date. I don't remember clearly how that came
java.util.Date is an instant resolving to a fraction of a second while
java.sql.Date is a period covered by a calendar day.
If you mean a calendar day, an alternative would be to implement a property
for your object that might look like the one below.
def java.sql.Date getCurrentDate() {
return new java.sql.Date(System.currentTimeMillis())
}
def java.sql.Timestamp getCurrentTime() {
return new java.sql.Timestamp(System.currentTimeMillis())
}
All jdbc drivers provide some coercion for parameters, allowing String
objects to be supplied for NUMBER columns, for instance. If Groovy is

to

Post by John Bito
provide additional coercion, some specification would be in order (at least
an analysis of why java.util.Date is handled specially). The main

pitfall

Post by John Bito
that I anticipate is that some vendor JDBC drivers already have

mechanisms

Post by John Bito
to tailor the mapping between database types and Java, and even without that
they don't behave consistently.
If there's broader interest in enhanced type coercion for groovy.sql,

I'd

Post by John Bito
consider spending some more time on it next month, after my current project
completes.
Of course, raising this as a possible enhancement in jira is a

reasonable

Post by John Bito
first step.
Best Regards,
John

Post by alexey_baranov
Why should I use java.sql.Timestamp wrapper when want to pass java.util.Date
parameter as in pure Java?
[code]
sql.execute('insert into table1 values(?)', [new

java.sql.Timestamp(new

Post by John Bito

Post by alexey_baranov
Date())])
[/code]
insted of simple
[code]
sql.execute('insert into table1 values(?)', [new Date()])
[/code]
Is it possible to automatically convert in this case?
--

http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25746598.html

Post by John Bito

Post by alexey_baranov
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email

--
http://www.nabble.com/groovy.sql.Sql-Date-paremeters-tp25746598p25763257.html
Sent from the groovy - user mailing list archive at Nabble.com.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email