Solved

JDBC with Progress Database cando function and PreparedStetement

Posted on 2006-07-11
28
370 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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
 
LVL 26

Expert Comment

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Printing to a specific printer tray (HP 806dn printer) 3 83
Starting to learn JAVA, 7 59
eclipse package explorer vs project explorer view 2 84
servlet doXXX methods 3 37
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…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
The viewer will learn how to implement Singleton Design Pattern in Java.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now