JDBC with Progress Database cando function and PreparedStetement

Hi all I am working with a progress Database with a JDBC call using a prepared Statement.  When I try to use the cando function in my prepared statement I get the error "Invalid Dynamic Parameter".  I am guessing that this is either a driver issue or a JDBC issue.  In either case I need to find some manner in which to make this work.  I am open to any suggestions but will only be awarding the points if it solves the problem.
AKnibbsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MeritorConnect With a Mentor Commented:
2) find a way to call the procedure from java.

For this you can use java.sql.CallableStatement
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/CallableStatement.html
0
 
colr__Commented:
Where did you get 'cando' from? Is that a function you've defined in yoiur database? It's not a standard SQL command.
0
 
colr__Commented:
Can we se some code?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
AKnibbsAuthor Commented:
       String prcAreaSql = "Select PRC_AREA.SITE-STR from PRC_AREA WHERE CAN-DO(PRC_AREA.SITE-STR, ?) AND PRC_AREA.ENTITY = ? ";
       
        prcAreaPstmt = connection.prepareStatement(prcAreaSql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
          prcAreaPstmt.setString(1, paramEntity);
        prcAreaPstmt.setString(2, paramBranch);
        rsPrcArea = prcAreaPstmt.executeQuery();      

The cando function is a funtion defined for the progress database.  I am guessing that I have to pass the entire funtion in to the database but i'm not certain how to do that.
0
 
Ajay-SinghCommented:
could be related to database (and its jdbc driver). Which driver and database you are using here?
0
 
AKnibbsAuthor Commented:
As stated in the first post it is with a Progress Databaseand the driver is the JDBC3 Driver from openlink.
0
 
ksivananthCommented:
try this,

String prcAreaSql = "Select PRC_AREA.SITE-STR from PRC_AREA WHERE CAN-DO(PRC_AREA.SITE-STR, " + paramEntity + " ) AND PRC_AREA.ENTITY = ? ";
       
        prcAreaPstmt = connection.prepareStatement(prcAreaSql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
        prcAreaPstmt.setString(1, paramBranch);
        rsPrcArea = prcAreaPstmt.executeQuery();    
0
 
AKnibbsAuthor Commented:
Tried that and came up with Incompatible data types in expression or assignment.
0
 
ksivananthCommented:
what is the datatype of the param in the function defined?
0
 
AKnibbsAuthor Commented:
firstly when I copied the code and pasted I made a slight mistake
paramBranch should actually be going to the cando function not parmEntity, not that it matters are both paramEntitty and paramBranch are both Strings, had i obtained the incorrect logic it would have been fine instead of the error that I received.  The PRC_AREA.SITE-STR is also a String.  
0
 
ksivananthCommented:
not sure in progress how the string will be passed, usually it is single quote in DBs, so here is the change,

"Select PRC_AREA.SITE-STR from PRC_AREA WHERE CAN-DO(PRC_AREA.SITE-STR, '" + paramEntity + "' ) AND PRC_AREA.ENTITY = ? ";

>>The PRC_AREA.SITE-STR is also a String

actually I asked the datatype of the other param in cando function, you have CAN-DO(PRC_AREA.SITE-STR, ?)

here ? represents what datatype?
0
 
AKnibbsAuthor Commented:
As stated above all 3 of the variables are strings.  The question mark is filled in by the setString function which was called above.  Hopefully that clarifies what you are asking
0
 
ksivananthCommented:
did you try

Select PRC_AREA.SITE-STR from PRC_AREA WHERE CAN-DO(PRC_AREA.SITE-STR, '" + paramEntity + "' ) AND PRC_AREA.ENTITY = ?

if single qoute not works, u may have to change it with appropriate rep
0
 
AKnibbsAuthor Commented:
the point of using PreparedStatements is so that you don't have to specify the specific value that  ia going into the Statement.  what is the rep that you are talking about ?
0
 
ksivananthCommented:
>>the point of using PreparedStatements is so that you don't have to specify the specific value that  ia going into the Statement

As long as the driver supports, you can enjoy all the luxury. Sometimes you may have to loose something to get...

>>what is the rep that you are talking about ?

String representation in postgress sql
0
 
AKnibbsAuthor Commented:
Postgress is an entirely different database from progress so I'm not quite sure how that is relevant.  I am certain that the driver supports usinq the ? in questions and I also know that it is possible to use progress specific commands within a prepared statement.  I am however not certain if both can be done in the same call.

0
 
ksivananthCommented:
probably you may try wrapping the query into a procedture and then call & pass the param to the procedure
0
 
AKnibbsAuthor Commented:
What exactly do you mean by making it a procedure ?  If you are talking about writing a java procedure then you should be calling it a method and in which case I can't see how it would help as at some point this code still needs to be executed.  If you are saying it should be a procedure on the DB, I don't know how to do that and don't have administrative rights on the database.  I am looking for either a manner in which to call the cando function on it's own or the alternative is to find a manner in which to pull out the entire string from the progress database.  When calling rsPrcArea.getString("SITE-STR"); it only pulls out the amount of characters that are set to be displayed in the database as opposed to what is actually in the database.
0
 
ksivananthCommented:
do mean that the value for "paramBranch" is in DB?

I meant DB sore procedure, java there is not procedure!
0
 
AKnibbsAuthor Commented:
No the value paramBranch is a value that is entered from a user.  I am aware that there is no procedure in java that is why I made the comment about it being called a method.  Unforetunately making it a procedure in the database would put me right in the situation I am in right now would it not ?  I have a procedure in the database that I want to call and it requires a value called paramBranch, and your suggestion is to make a procedure that requires the same.  I am not certain how that will further my problem.  
0
 
ksivananthCommented:
No buddy, calling a procedure is different from executing a statement!

What I suggest is, you can create a procedure that takes two params,

paramBranch & paramEntity.

Inside the proc you are going to prepare the query and return the result.

The problem I seeing in your query is, the param for a function in the query is parameterized!
0
 
AKnibbsAuthor Commented:
As I stated I don't know how to write a progress *not postgres* function and I don't have access to the database to include such a function if I did know how to write it.  My 2 options are to either 1) find a manner to pull out the entire value from the database instead of what it is set to display or
2) find a way to call the procedure from java.

I would prefer to do the second option if it is possible but am not opposed to option 1 if I can find a manner in which to do it.


0
 
ksivananthCommented:
why don't you try with statement than prepared... as I suggested the query earlier and see if atleast that works!
0
 
AKnibbsAuthor Commented:
That query doesn't work either.  
0
 
ksivananthCommented:
what is the error u r getting?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.