Tracing iSeries SQL Queries from a web page

["Brian Piotrowski" <bpiotrowski@xxxxxxxxxxxxxxx>]

Hi All,

 

Not sure if this is a question for the midrange group, but I thought I'd
throw it out and see what comes back...

 

I'm having an issue with one of our ASP web pages that is accessing
AS/400 data.  I suspect there is a "lost in translation" issue when the
SQL command is passed to the AS/400.

 

I have a physical file that has many fields, of which two are my query
targets - krdate (type "L") and krtime (type "T").  I have an SQL query
constructed as such in ASP:

 

KLSD="select krcode,count(krcode) CD from spsl.kls_data where kplcd='" &
request.form("plantcode") & "' and char(krdate)='" & sdate & "' and
(krtime between '06:30:00' and '15:30:59') group by krcode"

 

When I run the ASP page, pass it a value of 01/12/2006 and query the
value of KLSD, I get:

 

select krcode,count(krcode) CD from spsl.kls_data where kplcd='2' and
char(krdate)='12/01/06' and (krtime between '06:30:00' and '15:30:59')
group by krcode

 

(I have everyone enter the data MM/DD/YYYY, but our databases are in a
DD/MM/YYYY format - I need to parse the date to get it in the proper
format).

 

If I take this result and plug it into my handy SQL interpreter on the
AS/400, I get back some resulting data.  However, when it is run through
the program, I get a "no records" message back from my ASP page
indicating the query results are blank.

 

As I mentioned, I suspect there is a disconnect between what is supposed
to be passed to the interpreter and what is actually passed.  However,
I'm not sure how to trace the problem on the iSeries side to see what
exactly was being passed to the SQL interpreter from the webpage.

 

I am using the IBMDA400.DataSource.1 connector through a headless
connection on the webpage.  I have another routine that uses the same
connector in the same page (but obtains different data), and I get back
the expected results.  I'm wondering if it has something to do with the
"L" and "T" fields?

 

Anyone have any ideas on how I can trace the input / output on our
iSeries server?

 

Thanks,


Brian.

 

-=-=-=-=-=-=-=-=-=-=-=-=-=-

Brian Piotrowski

Specialist - I.T.

Simcoe Parts Service, Inc.

PH: 705-435-7814

FX: 705-435-6746

-=-=-=-=-=-=-=-=-=-=-=-=-=-