Open Client - Java SQL Result Set to Temp Table Error

[rherring]

Hi,

I ran a simple Progress program with a temp table in it through the Proxygen as a non Persistent procedure. The temp table mirrors the SQL table. I retrieive the record from the SQL database in my Java program and do a println to make sure the record is available. It is.

I call the Progress program (App Server 10.2A) from the Java program and try to access the temp table and it goes Ka-boom, with the errror being: SQL Result Set not available. The error in the Progress program doesn't happen until I try the FIND FIRST on the temp table. Any suggestions ?

The Proxygen created class expects a SQL result set and that is exactly what I am sending it.


Code snippets:

Java:

ResultSet vrs_po_hdr = get_po_hdr_data(Connect_To_Map_DB, stmt, vfile_id);

// get first record
vrs_po_hdr.next();
// print out value to make sure record is there

System.

out.println(vrs_po_hdr.getString("PONumber"));

//* Progress call
connecttgw.mpFlatiAppservWeb850(vrs_po_hdr);

Proxy Gen:

String com.tgw.push850datatotgw.mpFlatiAppservWeb850(ResultSet ttSqlPoHdr) throws Open4GLException, RunTime4GLException, SystemErrorException

Schema of input result set; Parameter 1 Field:headerid integer (java.lang.Integer) Field:fileid integer (java.lang.Integer) Field:ponumber character (java.lang.String) Field:postatus character (java.lang.String) Field:potype character (java.lang.String) Field:poacktype character (java.lang.String) Field:purposecode character (java.lang.String) Field:transportationmethod character (java.lang.String) Field:routing character (java.lang.String) Field:fob character (java.lang.String) Field:countryoforigin character (java.lang.String) Parameters: ttSqlPoHdr

Progress:

DEF TEMP-TABLE tt-sql-po-hdr NO-UNDO
FIELD headerid AS INT
FIELD fileid AS INT
FIELD ponumber AS CHAR
FIELD postatus AS CHAR
FIELD potype AS CHAR
FIELD poacktype AS CHAR
FIELD purposecode AS CHAR
FIELD transportationmethod AS CHAR
FIELD routing AS CHAR
FIELD fob AS CHAR
FIELD countryoforigin AS CHAR
INDEX by-tt-sql-po-hdr
fileid
headerid.

DEF INPUT PARAMETER TABLE FOR tt-sql-po-hdr .

OUTPUT TO "c:\temp\sql-po-hdr.txt".
PUT UNFORMATTED " hi there: " SKIP.

/* error occurs on find first */
FIND FIRST tt-sql-po-hdr NO-LOCK NO-ERROR.
IF AVAIL tt-sql-po-hdr THEN
DISP tt-sql-po-hdr WITH FRAME dkdkdk WIDTH 300 DOWN.

OUTPUT CLOSE.

Regards and thanks,

Rich

[rherring]

Hi,

Just a quick update. The Java code was returning an error, but that was from a different section of code. Once I commented that section out, the error went away.

However, even with trying an open query / get first combination, no records are in the temp table.

OUTPUT TO "c:\temp\sql-po-hdr.txt".
PUT UNFORMATTED " hi there: " ERROR-STATUS:GET-MESSAGE(1) SKIP.

OPEN QUERY sql-po-info FOR EACH tt-sql-po-hdr.
PUT UNFORMATTED " hi there: 1 " error-status:get-message(1) SKIP.

GET FIRST sql-po-info .
PUT UNFORMATTED " hi there: 2 " error-status:get-message(1) SKIP.

DISP tt-sql-po-hdr WITH FRAME dkdkdk WIDTH 300 DOWN.
PUT UNFORMATTED " hi there: 3 " error-status:get-message(1) SKIP.
OUTPUT CLOSE.

hi there: ** FIND FIRST/LAST failed for table tt-sql-po-hdr. (565)
hi there: 1 ** FIND FIRST/LAST failed for table tt-sql-po-hdr. (565)
hi there: 2 ** FIND FIRST/LAST failed for table tt-sql-po-hdr. (565)
** No tt-sql-po-hdr record is available. (91)


Regards,

Rich

[rherring]

ARGH.

Working with sample data sets is always fun.

This bit of info from the documentation is spot on.

Caution: Make sure the ResultSet cursor is positioned before the first row if you plan to pass the InputResultSet as an input parameter, and you want the receiving context to have access to all rows from the beginning of the InputResultSet. Only rows after the current cursor position are passed to the AppServer.

I positioned the cursor at the current record in order to see it in the Java program. Only one record was in the SQL result set. So, in effect, no records were passed to the Progress temp table. Once I removed the .Next() in the Java, the record was passed to the Progress temp table.

Regards,

Rich