Solved

JDBC with Progress Database cando function and PreparedStetement

Posted on 2006-07-11
28
395 Views
Last Modified: 2010-03-31
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.
0
Comment
Question by:AKnibbs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 11
  • 2
  • +2
28 Comments
 
LVL 8

Expert Comment

by:colr__
ID: 17082756
Where did you get 'cando' from? Is that a function you've defined in yoiur database? It's not a standard SQL command.
0
 
LVL 8

Expert Comment

by:colr__
ID: 17082765
Can we se some code?
0
 

Author Comment

by:AKnibbs
ID: 17082862
       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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 17083742
could be related to database (and its jdbc driver). Which driver and database you are using here?
0
 

Author Comment

by:AKnibbs
ID: 17083762
As stated in the first post it is with a Progress Databaseand the driver is the JDBC3 Driver from openlink.
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 17084138
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
 

Author Comment

by:AKnibbs
ID: 17084278
Tried that and came up with Incompatible data types in expression or assignment.
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 17084301
what is the datatype of the param in the function defined?
0
 

Author Comment

by:AKnibbs
ID: 17084494
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
 
LVL 26

Expert Comment

by:ksivananth
ID: 17084532
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
 

Author Comment

by:AKnibbs
ID: 17084878
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
 
LVL 26

Expert Comment

by:ksivananth
ID: 17084905
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
 

Author Comment

by:AKnibbs
ID: 17084976
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
 
LVL 26

Expert Comment

by:ksivananth
ID: 17085132
>>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
 

Author Comment

by:AKnibbs
ID: 17085204
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
 
LVL 26

Expert Comment

by:ksivananth
ID: 17085315
probably you may try wrapping the query into a procedture and then call & pass the param to the procedure
0
 

Author Comment

by:AKnibbs
ID: 17085380
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
 
LVL 26

Expert Comment

by:ksivananth
ID: 17085410
do mean that the value for "paramBranch" is in DB?

I meant DB sore procedure, java there is not procedure!
0
 

Author Comment

by:AKnibbs
ID: 17085448
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
 
LVL 26

Expert Comment

by:ksivananth
ID: 17085557
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
 

Author Comment

by:AKnibbs
ID: 17085641
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
 
LVL 26

Expert Comment

by:ksivananth
ID: 17086164
why don't you try with statement than prepared... as I suggested the query earlier and see if atleast that works!
0
 

Author Comment

by:AKnibbs
ID: 17092427
That query doesn't work either.  
0
 
LVL 26

Expert Comment

by:ksivananth
ID: 17095605
what is the error u r getting?
0
 
LVL 4

Accepted Solution

by:
Meritor earned 200 total points
ID: 17114214
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question