× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. December 2012

Re: sql database in RPGLE program with lots of columns

I am not sure what you are using to pull the data? Scott Klements JDBC
service program? If yes, JDBC dates are returned as alpha fields. The
attached code is a routine that use to convert to a time stamp field. From
there you can convert to anything.

* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
*
GetTimestampFromAlpha
* This function gets the timestamp from JDBC character
timestamp.
* Input - Timestamp in alpha format from
JDBC
* Out -
None
* Returns -
Timestamp
* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
-
p
GetTimestampFromAlpha...
p
b
d pi
Like(StdTms)
d
InJDBCTimestamp...
d 26a
Varying
d
Value


d
AlphaStamp...
d s
26a
d
MilliSeconds...
d s 6p
0

/Free
AlphaStamp = %Replace('-'
:

InJDBCTimestamp:
11
);
AlphaStamp = %Replace('.'
:

AlphaStamp:
14
);
AlphaStamp = %Replace('.'
:

AlphaStamp:
17
);
MilliSeconds =
%Dec(%Trim(%Subst(AlphaStamp:21:6)):6:0);
AlphaStamp =
%Replace(%EditC(MilliSeconds:'X'):AlphaStamp:21);
Return
%Timestamp(AlphaStamp);



/End-Free

p
e



On Wed, Dec 12, 2012 at 2:38 PM, Gary Thompson <gthompson@xxxxxxxxxxx>wrote:

Kim,
It is normal/typical to have this kind of data type miss-match between
two different databases, especially if one is older or more of a "legacy"
design.

Whether to convert at the DB level is a decision for you to consider, but
my first thought would be to just convert at the data connection level and
leave the respective DB objects as they are.

In my environment, changing a legacy system that is still heavily used
is out of the question and means I must work with the "numeric dates"
on the legacy side and use "real date data types" on the other side.

The common denominator to convert between the two is a character
representation.

For example, a legacy CYYMMDD numeric value of 1121212 would be
"2012-12-12" in character format,
and that character format will be pretty close to what most any
SQL DB would understand.

First time it is a headache, second time, it gets better, after that
it is routine.

Hope this helps.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Kim Massar
Sent: Wednesday, December 12, 2012 1:46 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: sql database in RPGLE program with lots of columns

"Now I have a new problem, there are fields defined in the sql table as
dates ([Original_File_Date] [date] NULL) in MMDDCCYY. In our iSeries file
they are defined as numeric, in CCYYMMDD order."

Or, I guess I should just move the date fields to a date field on the
iSeries and then convert. Sometimes it takes me a while!

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Kim Massar
Sent: Wednesday, December 12, 2012 3:22 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: sql database in RPGLE program with lots of columns

Now I have a new problem, there are fields defined in the sql table as
dates ([Original_File_Date] [date] NULL) in MMDDCCYY. In our iSeries file
they are defined as numeric, in CCYYMMDD order.

1. Is this something I should not try, or will not be able to, convert
when I get the columns? (removing the slashes and reordering fields).
2. I have tried this syntax, but does not work: CRTDT =
%dec(%Char((jdbc_getCol(rs:12)):*iso-):8:0) . I get the first and second
parameter are not allowed for *Char, so I am guessing I can't define the
column number here? My reason for question #1!!


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Kim Massar
Sent: Tuesday, December 11, 2012 4:37 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: sql database in RPGLE program with lots of columns

We are on 6.1

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Charles Wilt
Sent: Tuesday, December 11, 2012 4:35 PM
To: RPG programming on the IBM i / System i
Subject: Re: sql database in RPGLE program with lots of columns

What OS version?

If 7.1, you might be able to take advantage of the new INSERT into <local
table>
SELECT FROM <remote table>

With an appropriate DRDA<-->JDBC bridge such as ArdGate (opensource)
http://sourceforge.net/projects/appserver4rpg/

There's also a similar commercial product...

Charles


On Tue, Dec 11, 2012 at 4:13 PM, Kim Massar <kmassar@xxxxxxxxx> wrote:

Writing them to a work file, physical file not qtemp

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Charles Wilt
Sent: Tuesday, December 11, 2012 3:50 PM
To: RPG programming on the IBM i / System i
Subject: Re: sql database in RPGLE program with lots of columns

What are you doing with the data once it's mapped into RPG variables?

Charles


On Tue, Dec 11, 2012 at 3:33 PM, Kim Massar <kmassar@xxxxxxxxx> wrote:

HI all,
I previously sent a question on reading an SQL db in an RPGLE
program using Scott Klement's example code, which I got to work. I
have a follow up question. I have a db with 165 columns of which I
will be getting 140 of those columns. When I read thru the result
set, do I need to list all 140 columns like this:

dow (jdbc_nextRow(rs));
RecordId = jdbc_getCol(rs:1);
SsnId = jdbc_getCol(rs:2);
Etc...

Or is there a more efficient, faster way. By faster, I mean
processor speed.

Thanks



Email Confidentiality Notice: The information contained in this
transmission is confidential, proprietary or privileged and may be
subject to protection under the law, including the Health Insurance
Portability and Accountability Act (HIPAA). The message is intended
for the sole use of the individual or entity to whom it is addressed.
If you are not the intended recipient, you are hereby notified that
any use, distribution, copying or printing of this message is
strictly prohibited and may subject you to criminal or civil
penalties. If you received this transmission in error, please
contact the sender immediately by replying to this email and then
delete the material from
any computer.
--
This is the RPG programming on the IBM i / System i (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.




Email Confidentiality Notice: The information contained in this
transmission is confidential, proprietary or privileged and may be
subject to protection under the law, including the Health Insurance
Portability and Accountability Act (HIPAA). The message is intended
for the sole use of the individual or entity to whom it is addressed.
If you are not the intended recipient, you are hereby notified that
any use, distribution, copying or printing of this message is strictly
prohibited and may subject you to criminal or civil penalties. If you
received this transmission in error, please contact the sender
immediately by replying to this email and then delete the material from
any computer.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.




Email Confidentiality Notice: The information contained in this
transmission is confidential, proprietary or privileged and may be subject
to protection under the law, including the Health Insurance Portability and
Accountability Act (HIPAA). The message is intended for the sole use of the
individual or entity to whom it is addressed. If you are not the intended
recipient, you are hereby notified that any use, distribution, copying or
printing of this message is strictly prohibited and may subject you to
criminal or civil penalties. If you received this transmission in error,
please contact the sender immediately by replying to this email and then
delete the material from any computer.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.




Email Confidentiality Notice: The information contained in this
transmission is confidential, proprietary or privileged and may be subject
to protection under the law, including the Health Insurance Portability and
Accountability Act (HIPAA). The message is intended for the sole use of the
individual or entity to whom it is addressed. If you are not the intended
recipient, you are hereby notified that any use, distribution, copying or
printing of this message is strictly prohibited and may subject you to
criminal or civil penalties. If you received this transmission in error,
please contact the sender immediately by replying to this email and then
delete the material from any computer.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.




Email Confidentiality Notice: The information contained in this
transmission is confidential, proprietary or privileged and may be subject
to protection under the law, including the Health Insurance Portability and
Accountability Act (HIPAA). The message is intended for the sole use of the
individual or entity to whom it is addressed. If you are not the intended
recipient, you are hereby notified that any use, distribution, copying or
printing of this message is strictly prohibited and may subject you to
criminal or civil penalties. If you received this transmission in error,
please contact the sender immediately by replying to this email and then
delete the material from any computer.
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.



--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.