I am using Crystal Reports and the ODBC driver to access OI data.

It works great for data that in on the disk, however we have data that is in memory and is accessed via calculated fields. This works fine with OI Reporter.exe. It does not work with Crystal Reports and the ODBC driver because the new OENGINE that is running can not see the same COMMON data as is running in my application.

How do I get the new OENGINE to see this data?

Jim,

Not an answer, unfortunately, but a clarification question. Are you creating symbolic fields that compute from labelled common variables? Just curious as I don't think I've ever considered that. If that's the case, then I wonder what happens if you used @USERx variables instead?

I take it that you are launching Crystal Reports without launching the application. Therefore the common variables don't get assigned in advance. While I think I understand the problem, it at least seems like a reasonable consequence. Am I missing something?

dbakke@srpcs.com

SRP Computer Solutions, Inc.

You could also use OE's startup proc to load these common variables for you…(field 32 in your environment record - see the ENVIRON_CONSTANTS insert)

The Sprezzatura Group

World leaders in all things RevSoft

]] Are you creating symbolic fields that compute from labelled common variables? ] I take it that you are launching Crystal Reports without launching the application. «

I have the app running when I run CR. So the variables are set, however when the calculated field code executes (and crashes) the debuger shows the variables as null.

Hmmm, can I tell one OE to use the common variables from another OE?

Jim,

Hmmm, can I tell one OE to use the common variables from another OE?

No, not directly. You could however:

1) Write them out to a temporary record in a table or something and pick them up from there. The OE's are both in separate processes which makes sharing memory between them difficult (though not impossible), or…

2) If you know the ServerName of the main OE (use the /SN= switch when starting OI) you could write a function to connect to it and grab the data that way. There's some sample Basic+ code we put up here last week that allows you to do this, or check the OI on-line help (OpenEngine chapter).

The Sprezzatura Group

World leaders in all things RevSoft

(2) Sounds interesting. Let me look into that.

Thanks for the ideas.

Following emailed to Mike (anyone else have any suggestions?)

I am trying to use the ODBC driver with Crystal Reports and have hit a problem and wondered if you might have any suggestions.

We have calculated fields in our OI databases that are used in the OI Reporter. These calculated fields are used to report on the values of in memory variables. We schedule manufacturing facilities so these variables would be things like start and finish times of operations.

If I use Crystal Reports via ODBC then the OENGINE that is used by Crystal Reports does not have these in memory variables initialized and hence the calculated field will crash when RBASIC accesses these variables.

One solution would be to write a OE startup procedure to load these values from one OE to the one being started via ODBC. I think I can do that by using the /SN switch and then accessing that OE via RBASIC.

However if the user runs a report, and then changes something in our product, and then runs the report again without closing Crystal Reports, then the report would not reflect the changes made as the variables are only loaded on OE startup.

Is there anyway to have the ODBC driver use the existing running OE and hence use the correct variables?

If not do you have any other suggestions?

I have posted this on the Works forum as well as emailing it to you.

Slightly separate topic. If I have a user running the runtime and they want to use Crystal reports, I assume they need a copy of the ODBC driver. Do they also need a multi-user copy of OI as more than on OE is running?

Jim,

In the LH ODBC config dialog there's a prompt to enter the serverName information, so you could set this to the same as the /SN= argument you use with OI

The Sprezzatura Group

World Leaders in all things RevSoft

Cool, let me try that.

That seems not to work.

I get a new OE.

I have /SN=W_TACTIC in my shortcut that starts OI.

I have W_TACTIC

Sorry for the double post the last one got sent to soon.

That seems not to work.

I get a new OE, it does not use the existing one.

I have /SN=W_TACTIC in my shortcut that starts OI. And when I look at the OE EnigineInfo, Queue information says name W_TACTIC type PIPE; I assume that is correct.

The Server Name is set to W_TACTIC in the ODBC data source setup.

Any ideas?

I have W_TACTIC

Jim,

Try "\\.\W_TACTIC" in the ODBC config instead

The Sprezzatura Group

World leaders in all things RevSoft

That seems to work, however I am now getting the following error:

Query Engine Error: "HY000Revelation Software IncRevelation ODBC DriverSQLGetData internal error"

Any ideas?

Jim,

What does your SQL statement look like. Does it contain quotes? If so, can you remove them.

Sean

It does, but Mike sent me a patch that had removed that problem.

I am testing the ODBC driver with Crystal Reports, so that our users can user Crystal Reports. It's not acceptable for them to have to edit SQL statements.

]] Query Engine Error: "HY000Revelation Software IncRevelation ODBC DriverSQLGetData internal error" «

Not all fields when included in the report cause this error. The ones that do however, do it all the time.

By way of example two fields START and FINISH, if I add one to the report it crashes the other does not.

The code they call (they are calculated fields) is the almost exactly the same (they access a different place in a array via an equate). They are both DATETIME, 21 chars.

Any ideas?

Jim,

Total guess here but are any of your columns that cause a crash potentially reserved words in SQL? Have you experimented with using the same symbolic code with other column names and/or replacing the symbolic code that seems to cause problems with simpler code (i.e. @ANS=Hello") to rule out the column name issue?

dbakke@srpcs.com

SRP Computer Solutions, Inc.

I do not know how to remove quotes in Crystal Reports.

I only have an evaluation version of Crystal Reports (that is about to expire) and I need to make sure our users can access OI data via ODBC using Crystal Reports.

What can I try to fix the following error?

Query Engine Error: "HY000Revelation Software IncRevelation ODBC DriverSQLGetData internal error"

Jim,

This error happens when you get invalid data against it's declared type - (extreme example … sending back "abcde" for a DATE type).

Can you isolate the column that's causing the problem?

Do you have any user-defined conversions in your result set that could be sending back bum data?

Can you run the SQL statement in the OI32 'SQL' window and see what you get back and if it makes any sense?

Have you tried the same statement in another tool (like MS Excel, Access etc) to see if they produce the same results (I would expect so but … )

You could also try switching on the ODBC tracing log (look in the ODBC config applet) - this should show you EVERY ODBC call made by Crystal Reports along with the errors - see if this gives any indication of what is happening?

The Sprezzatura Group

World leaders in all things RevSoft

I'll try all this right now.

Many thanks.

This is what causes the crash in ODBC driver:

Calculated field

DateTime DT

Call Funtion to get value returned in @ANS

If @ANS is non-integer (eg 111.12356) ODBC driver does not work.

Any chance of getting a fix for this?

Jim-

ODBC *strictly* enforces datatyping. Have you tried VARCHAR instead?

Mike

But isn't a non-interger value valid for DateTime?

This has stopped my testing of Crystal Reports.

Should a non-integer value retuned from a datetime calculated field cause the odbc driver to crash?

Jim-

Should have a quick patch tomorrow for this. Will email directly.

Thanks-

Mike

Mike you are a hero.

View this thread on the Works forum...