Solved

JDBC with Progress Database cando function and PreparedStetement

Posted on 2006-07-11
28
391 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
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!

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

734 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