• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

stored procedure works but resultset is incorrect

 
J. Kevin Robbins
Bartender
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is a weird one. I have a stored procedure that works correctly. When I call the stored procedure from WinSQL, I get the expected results like so:

That doesn't format well, but you get the idea.
Note the field "RD_GRNTIRE". This is the problem child. When I run this procedure from a DAO, all the fields return correctly except this one; it's always zeroes. I've tried using CallableStatement and PreparedStatement. I've retrieved it using getInt, getString, and every other getXXX there is. I always get zeroes.

The three fields rd_grntire, rd_timeprs, and rd_reldtme are defined the same in the stored procedure, as 5s 0. The last two return correctly, only rd_grntire is wrong. Here's my DAO:


The result is always the same:
green tires as string = 0
green tires as int = 0
green tires as string = 0
green tires as int = 0
green tires as string = 0
green tires as int = 0
green tires as string = 0
green tires as int = 0
green tires as string = 0
green tires as int = 0
green tires as string = 0
green tires as int = 0
green tires as string = 0
green tires as int = 0
green tires as string = 0
green tires as int = 0
green tires as string = 0
green tires as int = 0

I've never seen a problem like this when calling stored procedures. btw, the database is DB/2 on iseries if that makes any difference. I'm having trouble believing that the problem lies with the stored procedure since it gives the expected and correct results when called from WinSQL, but I'm willing to consider any possibility at this point. Does anyone have any ideas?
 
K. Tsang
Bartender
Posts: 3585
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Indeed looks weird.

Is the source data from a table or view? How are the data type of these so-called numeric fields defined?

You may also want to "char" it in the store procedure and use getString() in java.
 
J. Kevin Robbins
Bartender
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
They are defined as numeric (5s 0), and I agree, changing them to char is the next step because this makes no sense to me. The data is from several tables and the stored procedure is actually created a data structure that gets returned as the resultset.

Here's the RPG source of the returned data structure. I see the programmer who created this has already changed it to char, so I'll go try it again.

/copy cwprd/qrpglesrc,pcrerrord
D return_data ds occurs(9999)
D rd_press 6
D rd_ticcd 6
**D rd_grntire 5s 0
D rd_grntire 5
D rd_timeprs 5s 0
D rd_reldtme 5s 0
D rd_auto 1
D rd_dtrsn 20
D rd_qltyiss 20
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic