cancel
Showing results for 
Search instead for 
Did you mean: 

For JDBC source, Command object not recognizing '(' character

Former Member
0 Kudos

Hello

I am trying to create a crystal report from a JDBC source query (which is actually pointing to Clearquest Oracle database). The query accepts a parameter to filter down data.

Issue I have is when I try to pass a parameter in the command object used for defining the Data Source (See attached screenshot), somehow the parsing ignores anything entered beyond the open bracket character - '('.

In the example screenshot attached, notice how everything I have entered beyond ( is ignored totally in the error message.

I somehow need to find a way to pass this parameter & for that I need the system to understand the ( character.

Any help is appreciated.

Cheers

Satish

Accepted Solutions (0)

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

You're not handling the parameter correctly.  In order to use a parameter in a command, you MUST create the parameter in the Command Editor.  You would then use it like this:

Get_Software_Defect_by_ID_wofilt({?Parameter1})

If the data type of the parameter is a string, it would look like this:

Get_Software_Defect_by_ID_wofilt('{?Parameter1}')

See my blog for more information about working with commands in Crystal: 

-Dell

Former Member
0 Kudos

Thank you for the response Dell. That was helpful to know that although my previous error screenshot didnt show the parameters,when we create the parameters it is indeed being sent.

However, I still have an issue : The parameter values are not getting passed. Only the parameter names are getting passed. This could be a limitation of JDBC source as there are plenty of example threads where this works for an ODBC source.

Attached a screenshot - As far as I can see, I stuck to the syntax you provided. Also tried a few others, but all in vain.

(Interestingly, the values are passed if the same syntax is used before the '(' character, but that makes the system not think of it as a parameter & error out stating that the specified source is not found).

Please let me know if you have any suggestions. Thanks again. Good day.

DellSC
Active Contributor
0 Kudos

I'm not familiar with the database you're using, but I would close the quotes at the end of the procedure name, not after the final '}'.  Also, most databases use double-quotes when identifying things in the database and single quotes to identify string values.  So, your call might look like this

instead:

{CALL "Public Queries/CQAdmin/Data_Pull/Satish/Get_Software_Defect_by_ID_wofilt"('{?Parameter1}','{?Parameter2}')}

-Dell

Former Member
0 Kudos

Thank you again Dell. No luck though. The backend Database is Oracle - Only the connection is JDBC instead of the conventional ODBC.

Double quoting the procedure name results in 'Resource not found' error. Double quoting the parameters doesnt make any difference - The values are not passed.

Attached screen shot of both - I have also tried various other combinations including changing the finishing quote to end of procedure vs end of call statement. Nothing makes a difference.

As I mentioned in my previous reply, the only time the value is passed instead of the parameter name is when I send it before the '(' character - Sadly, that wont help as that will not be recognized as a parameter.

Thanks for your patient reviews & answers though :-).

DellSC
Active Contributor
0 Kudos

I have not used JDBC with Oracle.  However, instead of creating a command to call the stored proc, I would try this:

1.  Go to File>>Options>>Database Tab. Under "Database Explorer" make sure that "Stored Procedures" is checked.

2.  Go to Database>>Database Expert.  Open your JDBC connection to the database.  Drill into it and see whether "Stored Procedures" is something you can drill into.  If you can, do that and select the stored procedure that you're looking for.  Crystal will add the parameters to the report and set everything up for you.

-Dell

Former Member
0 Kudos

Thanks for the response again Dell.

That's  the approach we started with, but has its limitations (which is why I started exploring command option). When I pull in Stored procedures, Crystal creates its own filters/parameters, which I have limited control on (For example, I cannot change the parameter type to multi value).

Not sure why JDBC results in this situation, but sadly thats the protocol that is supported. Stuck with this situation for now. I guess we have to work on the reports given these constraints.

Cheers

Satish

DellSC
Active Contributor
0 Kudos

You can't actually send a Crystal multi-select parameter to a stored procedure anyways.  Crystal stores the selections as an array internally and the database has no way to interpret that.  Instead, you have to convert the array to a delimited string and pass that to the stored procedure instead of the parameter.  The problem is that unless you put the stored proc into a subreport and then use the formula that converts the parameter as a link to the parameter in the subreport, there's no way to convert the array prior to calling the stored proc.

-Dell

Former Member
0 Kudos

Thank you so much Dell (& Sorry for the late response).

I finally figured out how to get this working. The solution is what you have mentioned - Have a blank parent report to fetch inputs from the user, use formulas to convert multiple values to a delimited string (Actually its a very specific format - #####_Value1,Value2,...) & pass it to a subreport for processing (which processes it as a single value in that format).

I did have a technote from the application vendor on how to do this, but I didn't believe it will be this complex & so I was looking for other ways. Looking at your comments in your previous post made me realize that could be the only way.

Honestly, its a bit complex - Especially for anyone new to Crystal. Once we understand the workings, it becomes a bit easier (I guess that's with any software though).

Once again, thank you for all your patient writing. Have a good day. I am glad to have made the reports working, the way we wanted it to.

DellSC
Active Contributor
0 Kudos

I'm glad you were able to get it working!

-Dell