Solved

JDBC with Progress Database cando function and PreparedStetement

Posted on 2006-07-11
28
364 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

757 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

18 Experts available now in Live!

Get 1:1 Help Now