Home > Archive > Cobol > November 2007 > Embedded SQL and MS ACCESS Dates
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Embedded SQL and MS ACCESS Dates
|
|
| Pete Dashwood 2007-11-04, 7:55 am |
| Is anybody using PC COBOLs to access a MS Access database ?
I haven't done it for quite some time now and the last time I did was Access
97.
I am trying to INSERT records as part of a load, into an ACCESS 2003 DB,
from COBOL.
The fields defined as DateTime on the database are causing an SQLSTATE
22005 -
"[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast
specification (null)"
With Access 97 I remember using floating point to update Date fields (the
field contained the date as 8 digits on the left of the point , and the time
as 4 digits on the right of the point) and this worked fine; it doesn't with
ACCESS 2003.
The irony is that I can do it easily with C# as I can ensure the field being
inserted is a Date type, but COBOL doesn't have a picture DATE as far as I
know...
Anyway, it is necessary to do it from COBOL.
So far I have tried the following, all to no avail:
1. Set the Host Variable to be pic x(26) and used every date string you can
imagine...
YYYYMMDD
YYYY/MM/DD
DDMMYYYY
DD/MM/YYYY
2. Used hyphens as separators instead of slashes.
3. Added times as strings HH:MM:SS after the date string.
4. Tried using the SQL DATE function in the VALUES of the INSERT...
INSERT INTO (a,b,c...) VALUES (:a, :b, Date(:c)...)
No joy. (Same result every time, so at least the syntax is OK... :-))
I'm sure this should be simple, but a search of the web finds thousands of
people having similar problems with inserting Date data onto Access DBs.
Please don't tell me to use a third party Load system (many of them have the
same problem, apparently... and anyway, I need for this to work from
COBOL. )
Any assistance greatly appreciated.
I will solve this, but it would take less time if I had some help... :-)
Pete.
--
"I used to write COBOL...now I can do anything."
| |
|
|
|
| Bugger, it didn't show what I wanted...
Database Access > 3. Data Types
Date and Time Data Types
COBOL does not have date/time data types so SQL date/time columns are
converted to character representations.
If a COBOL output host variable is defined as PIC X(n), for a SQL timestamp
value, where n is greater than or equal to 19, the date and time are
specified in the format yyyy-mm-dd hh:mm:ss.ff..., where the number of
fractional digits is driver-defined.
For example:
1994-05-24 12:34:00.000OpenESQL
Because OpenESQL can access any relational database and each database has
different ways of specifying dates and times, a standard way is provided of
specifying dates and times in input host variables. If you are using this
method, you must use the option DETECTDATE in the SQL directive when
compiling your program.
a.. To specify a date, move the date into the host variable in the form
{dyyyy-mm-dd}.
b.. To specify a time, move the time into the host variable in the form
{thh:mm:ss}.
c.. To specify a date and time, move the date and time into the host
variable in the form {tsyyyy-mm-dd hh:mm:ss}.
For example:
$set sql(dbman=odbc, detectdate)
01 Hire-Date pic x(26).
move "{d'1965-11-02'} to Hire-Date
exec sql
insert into emp (HireDate) values (:Hire-Date)
end-exec
| |
| Pete Dashwood 2007-11-04, 7:55 am |
|
--
"I used to write COBOL...now I can do anything."
"me" <null@null.com> wrote in message
news:Y6KdnSziwauKXLDanZ2dneKdnZydnZ2d@bt
.com...
> Bugger, it didn't show what I wanted...
>
> Database Access > 3. Data Types
>
> Date and Time Data Types
> COBOL does not have date/time data types so SQL date/time columns are
> converted to character representations.
>
> If a COBOL output host variable is defined as PIC X(n), for a SQL
> timestamp value, where n is greater than or equal to 19, the date and time
> are specified in the format yyyy-mm-dd hh:mm:ss.ff..., where the number of
> fractional digits is driver-defined.
>
> For example:
>
> 1994-05-24 12:34:00.000OpenESQL
> Because OpenESQL can access any relational database and each database has
> different ways of specifying dates and times, a standard way is provided
> of specifying dates and times in input host variables. If you are using
> this method, you must use the option DETECTDATE in the SQL directive when
> compiling your program.
>
> a.. To specify a date, move the date into the host variable in the form
> {dyyyy-mm-dd}.
>
> b.. To specify a time, move the time into the host variable in the form
> {thh:mm:ss}.
>
> c.. To specify a date and time, move the date and time into the host
> variable in the form {tsyyyy-mm-dd hh:mm:ss}.
>
> For example:
>
> $set sql(dbman=odbc, detectdate)
> 01 Hire-Date pic x(26).
> move "{d'1965-11-02'} to Hire-Date
> exec sql
> insert into emp (HireDate) values (:Hire-Date)
> end-exec
>
| |
| Pete Dashwood 2007-11-04, 7:55 am |
| Sorry about accidental blank post... :-)
I found this stuff from the link you posted. Thanks.
Unfortunately, it isn't useful for me although I read it with interest, in
case I'm ever using MicroFocus COBOL.
In this instance I need a solution for Fujitsu, but sometimes looking at
other stuff can jog an idea.
Thanls for the link and for your comments. I appreciate someone actually
trying to help :-)
Pete.
TOP POST NO MORE BELOW FROM ME...
"me" <null@null.com> wrote in message
news:Y6KdnSziwauKXLDanZ2dneKdnZydnZ2d@bt
.com...
> Bugger, it didn't show what I wanted...
>
> Database Access > 3. Data Types
>
> Date and Time Data Types
> COBOL does not have date/time data types so SQL date/time columns are
> converted to character representations.
>
> If a COBOL output host variable is defined as PIC X(n), for a SQL
> timestamp value, where n is greater than or equal to 19, the date and time
> are specified in the format yyyy-mm-dd hh:mm:ss.ff..., where the number of
> fractional digits is driver-defined.
>
> For example:
>
> 1994-05-24 12:34:00.000OpenESQL
> Because OpenESQL can access any relational database and each database has
> different ways of specifying dates and times, a standard way is provided
> of specifying dates and times in input host variables. If you are using
> this method, you must use the option DETECTDATE in the SQL directive when
> compiling your program.
>
> a.. To specify a date, move the date into the host variable in the form
> {dyyyy-mm-dd}.
>
> b.. To specify a time, move the time into the host variable in the form
> {thh:mm:ss}.
>
> c.. To specify a date and time, move the date and time into the host
> variable in the form {tsyyyy-mm-dd hh:mm:ss}.
>
> For example:
>
> $set sql(dbman=odbc, detectdate)
> 01 Hire-Date pic x(26).
> move "{d'1965-11-02'} to Hire-Date
> exec sql
> insert into emp (HireDate) values (:Hire-Date)
> end-exec
>
| |
| Rene_Surop 2007-11-04, 7:55 am |
| On Nov 4, 3:53 am, "Pete Dashwood"
<dashw...@removethis.enternet.co.nz> wrote:
> Is anybody using PC COBOLs to access a MS Access database ?
>
> I haven't done it for quite some time now and the last time I did was Access
> 97.
>
> I am trying to INSERT records as part of a load, into an ACCESS 2003 DB,
> from COBOL.
>
> The fields defined as DateTime on the database are causing an SQLSTATE
> 22005 -
> "[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast
> specification (null)"
>
> With Access 97 I remember using floating point to update Date fields (the
> field contained the date as 8 digits on the left of the point , and the time
> as 4 digits on the right of the point) and this worked fine; it doesn't with
> ACCESS 2003.
>
> The irony is that I can do it easily with C# as I can ensure the field being
> inserted is a Date type, but COBOL doesn't have a picture DATE as far as I
> know...
>
> Anyway, it is necessary to do it from COBOL.
>
> So far I have tried the following, all to no avail:
>
> 1. Set the Host Variable to be pic x(26) and used every date string you can
> imagine...
> YYYYMMDD
> YYYY/MM/DD
> DDMMYYYY
> DD/MM/YYYY
>
> 2. Used hyphens as separators instead of slashes.
>
> 3. Added times as strings HH:MM:SS after the date string.
>
> 4. Tried using the SQL DATE function in the VALUES of the INSERT...
>
> INSERT INTO (a,b,c...) VALUES (:a, :b, Date(:c)...)
>
> No joy. (Same result every time, so at least the syntax is OK... :-))
>
> I'm sure this should be simple, but a search of the web finds thousands of
> people having similar problems with inserting Date data onto Access DBs.
>
> Please don't tell me to use a third party Load system (many of them have the
> same problem, apparently... and anyway, I need for this to work from
> COBOL. )
>
> Any assistance greatly appreciated.
>
> I will solve this, but it would take less time if I had some help... :-)
>
> Pete.
> --
> "I used to write COBOL...now I can do anything."
I not really sure about Fujitsu Cobol, but on Microfocus NetExpress
the formatis as follows;
03 SQLdatefield SQL type is timestamp.
03 wSQLdate pic x(29).
with proper SQL date values formatted as: "yyyy-mm-dd hh:mm:ssss"
| |
| Pete Dashwood 2007-11-04, 6:55 pm |
|
"Rene_Surop" <infodynamics_ph@yahoo.com> wrote in message
news:1194181803.514565.249040@e9g2000prf.googlegroups.com...
> On Nov 4, 3:53 am, "Pete Dashwood"
> <dashw...@removethis.enternet.co.nz> wrote:
>
> I not really sure about Fujitsu Cobol, but on Microfocus NetExpress
> the formatis as follows;
>
> 03 SQLdatefield SQL type is timestamp.
>
> 03 wSQLdate pic x(29).
>
> with proper SQL date values formatted as: "yyyy-mm-dd hh:mm:ssss"
>
Thanks Rene. Doesn't help unfortunately... :-)
Pete.
--
"I used to write COBOL...now I can do anything."
| |
| Doug Miller 2007-11-04, 6:55 pm |
| In article <5p5q5cFpdk59U1@mid.individual.net>, "Pete Dashwood"
<dashwood@removethis.enternet.co.nz> wrote:
>Is anybody using PC COBOLs to access a MS Access database ?
>
>I haven't done it for quite some time now and the last time I did was Access
>97.
>
>I am trying to INSERT records as part of a load, into an ACCESS 2003 DB,
>from COBOL.
>
>The fields defined as DateTime on the database are causing an SQLSTATE
>22005 -
>"[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast
>specification (null)"
Is this of any help?
http://www.devnewsgroups.net/group/...xml/topic14659.
aspx
--
Regards,
Doug Miller (alphag at milmac dot com)
It's time to throw all their damned tea in the harbor again.
| |
| Pete Dashwood 2007-11-04, 6:55 pm |
|
"Doug Miller" <spambait@milmac.com> wrote in message
news:8glXi.150$3Z2.100@nlpi069.nbdc.sbc.com...
> In article <5p5q5cFpdk59U1@mid.individual.net>, "Pete Dashwood"
> <dashwood@removethis.enternet.co.nz> wrote:
>
> Is this of any help?
>
> http://www.devnewsgroups.net/group/...xml/topic14659.
> aspx
>
I dunno... it returns a 404 when I access it :-)
Thanks anyway, Doug. Problem is now solved (see separate thread) but it took
me about 13 hours to run it down... (I guess I'm getting old :-))
Pete.
--
"I used to write COBOL...now I can do anything."
| |
| Doug Miller 2007-11-04, 9:55 pm |
| In article <5p75opFpsn2cU1@mid.individual.net>, "Pete Dashwood" <dashwood@removethis.enternet.co.nz> wrote:
>
>
>"Doug Miller" <spambait@milmac.com> wrote in message
>news:8glXi.150$3Z2.100@nlpi069.nbdc.sbc.com...
>
>I dunno... it returns a 404 when I access it :-)
Works for me. Did you observe that it had wrapped to a second line?
>
>Thanks anyway, Doug. Problem is now solved (see separate thread) but it took
>me about 13 hours to run it down... (I guess I'm getting old :-))
Hmmm... for some reason, I'm not seeing that separate thread. What did the
issue turn out to be?
--
Regards,
Doug Miller (alphag at milmac dot com)
It's time to throw all their damned tea in the harbor again.
| |
| Jeff Campbell 2007-11-04, 9:55 pm |
| Pete Dashwood wrote:
> Is anybody using PC COBOLs to access a MS Access database ?
>
> I haven't done it for quite some time now and the last time I did was Access
> 97.
>
> I am trying to INSERT records as part of a load, into an ACCESS 2003 DB,
> from COBOL.
>
> The fields defined as DateTime on the database are causing an SQLSTATE
> 22005 -
> "[Microsoft][ODBC Microsoft Access Driver]Invalid character value for cast
> specification (null)"
>
> With Access 97 I remember using floating point to update Date fields (the
> field contained the date as 8 digits on the left of the point , and the time
> as 4 digits on the right of the point) and this worked fine; it doesn't with
> ACCESS 2003.
>
> The irony is that I can do it easily with C# as I can ensure the field being
> inserted is a Date type, but COBOL doesn't have a picture DATE as far as I
> know...
>
> Anyway, it is necessary to do it from COBOL.
>
> So far I have tried the following, all to no avail:
>
> 1. Set the Host Variable to be pic x(26) and used every date string you can
> imagine...
> YYYYMMDD
> YYYY/MM/DD
> DDMMYYYY
> DD/MM/YYYY
>
> 2. Used hyphens as separators instead of slashes.
>
> 3. Added times as strings HH:MM:SS after the date string.
>
> 4. Tried using the SQL DATE function in the VALUES of the INSERT...
>
> INSERT INTO (a,b,c...) VALUES (:a, :b, Date(:c)...)
>
> No joy. (Same result every time, so at least the syntax is OK... :-))
>
> I'm sure this should be simple, but a search of the web finds thousands of
> people having similar problems with inserting Date data onto Access DBs.
>
> Please don't tell me to use a third party Load system (many of them have the
> same problem, apparently... and anyway, I need for this to work from
> COBOL. )
>
> Any assistance greatly appreciated.
>
> I will solve this, but it would take less time if I had some help... :-)
>
> Pete.
Have a look at
<http://www.codefixer.com/codesnippe..._access_sql.asp>
<http://www.hotscripts.com/Detailed/55202.html>
Both show format as YYYY-MM-DD.
The second makes a point of using the hash mark delimiters.
HTH,
Jeff
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
| |
| Robert 2007-11-04, 9:55 pm |
| On Mon, 5 Nov 2007 00:53:14 +1300, "Pete Dashwood" <dashwood@removethis.enternet.co.nz>
wrote:
>1. Set the Host Variable to be pic x(26) and used every date string you can
>imagine...
> YYYYMMDD
> YYYY/MM/DD
> DDMMYYYY
> DD/MM/YYYY
>
>2. Used hyphens as separators instead of slashes.
>
>3. Added times as strings HH:MM:SS after the date string.
>
>4. Tried using the SQL DATE function in the VALUES of the INSERT...
>
>INSERT INTO (a,b,c...) VALUES (:a, :b, Date(:c)...)
>
>No joy. (Same result every time, so at least the syntax is OK... :-))
The Date() function returns today's date. It should have ignored :c and inserted today's
date. Try changing the column type from DateTime to Date. If that works, the next step is
inserting your dates rather than the system date. Functions DateValue() and CDate() take a
string as input and output a date type (not datetime). The string format depends on your
regional settings.
To go the other way, convert date to string, use the Format() function.
| |
| Pete Dashwood 2007-11-04, 9:55 pm |
|
"Robert" <no@e.mail> wrote in message
news:gnssi39tr1p69naln9liaf2agpt5so74td@
4ax.com...
> On Mon, 5 Nov 2007 00:53:14 +1300, "Pete Dashwood"
> <dashwood@removethis.enternet.co.nz>
> wrote:
>
>
>
> The Date() function returns today's date.
I should have checked it before trying it :-) Just tired.
>It should have ignored :c and inserted today's
> date. Try changing the column type from DateTime to Date.
Changing the DB format was going to be my next avenue of investigation.
Obviously, as a last ditch I could simply load date strings and convert them
to DATE types later, but I didn't really want to do that...
The problem is now solved (see separate thread)
>If that works, the next step is
> inserting your dates rather than the system date. Functions DateValue()
> and CDate() take a
> string as input and output a date type (not datetime). The string format
> depends on your
> regional settings.
Yes, I found out about all this during a number of hours browsing the Web...
:-)
>
> To go the other way, convert date to string, use the Format() function.
>
Yep, read about that too.
Thanks for your post, Robert. What I was really looking for with this post
was someone who is actually using Fujitsu COBOL to access an ACCESS database
and who could simply give me a copy of the Host Variables used.
Nevertheless, despite the time (which I can ill afford right now) it has
been an edifying exercise and I am grateful to all who contributed.
Pete.
--
"I used to write COBOL...now I can do anything."
>
|
|
|
|
|