Highlighted
Absent Member.
Absent Member.
4640 views

[archive] Acucobol and Crystal - connection to database

[Migrated content. Thread originally posted on 22 June 2005]

In our new Acucobol application we are developing, we want to interface with Crystal Reports in order to allow clients to create their own reports and run them from within our application (for numerous reasons). We have had no problem doing this up to this point working with ODBC. However, now we want to get it to work with MSDE and MS-SQL databases. That means we need to get properties of the database and login to allow access to the data before calling the Crystal Report to run. We are having difficulties with the properties of the database, and were wondering if anyone had any successful experiences with this. We are developing using AcuBench 5.2.1 and interfacing with Crystal Reports XI.

Method 1:
Modifying the connection properties of a database table. Trying to read connection information first, before tackling modifying.
(craxdrt11.def)
* Item
* Gets value by name.
PROPERTY-GET, 0, @Item,
"BSTR" @Name, TYPE 8
RETURNING "VARIANT", TYPE 12
Method 2:
Using the logon method of the database object.
(craxdrt11.def)
* Log on server.
METHOD, 228, @LogOnServer,
"BSTR" @pDllName, TYPE 8,
"BSTR" @pServerName, TYPE 8,
"VARIANT" @pDatabaseName, TYPE 12,
"VARIANT" @pUserID, TYPE 12,
"VARIANT" @pPassword, TYPE 12
OPTIONAL 3


Working Storage:
01 CR-REPORT USAGE HANDLE OF APPLICATION.
01 CR-VIEWER USAGE HANDLE OF CrystalActiveXReportViewer.
01 DATABASE-OBJ USAGE HANDLE OF IDatabase.
01 DB-TABLE-COLL-OBJ USAGE HANDLE OF IDatabaseTables.
01 TABLE-OBJ USAGE HANDLE OF IDatabaseTable.
01 CONNECT-PROP-OBJ USAGE HANDLE OF INameValuePairs.
01 CONNECT-PROP USAGE HANDLE OF IConnectionProperty.
01 VARIANT-VALUE USAGE HANDLE.
01 PROPERTY-VALUE PIC X(25).
01 RETURN-RESULT PIC S9(1).
01 SERVER-NAME PIC X(25).
01 U-ID PIC X(25).
01 U-ID-V USAGE HANDLE.
01 PASSWD PIC X(25).
01 PASSWD-V USAGE HANDLE.
01 DATABASE-NAME PIC X(25).
01 DATABASE-NAME-V USAGE HANDLE.
01 REPORT-PATH PIC X(255).
01 REPORT-OBJ USAGE HANDLE OF REPORT.




Program:

CREATE @APPLICATION OF @CRAXDRT HANDLE IN CR-REPORT
?
Method 1:
INQUIRE DATABASE-OBJ @TABLES
IN DB-TABLE-COLL-OBJ
INQUIRE DB-TABLE-COLL-OBJ @Item(1) IN TABLE-OBJ
INQUIRE TABLE-OBJ @ConnectionProperties
IN CONNECT-PROP-OBJ
INQUIRE CONNECT-PROP-OBJ @Item ("Initial Catalog")
IN VARIANT-VALUE
CALL 'c$getvariant' USING VARIANT-VALUE, PROPERTY-VALUE

call to c$getvariant produces - 'Invalid or missing parameter to "C$GETVARIANT" '


Method 2:
MODIFY CR-REPORT @OPENREPORT (REPORT-PATH)
RETURNING REPORT-OBJ
INQUIRE REPORT-OBJ @DATABASE IN DATABASE-OBJ
call 'c$setvariant' using database-name, database-name-v
call 'c$setvariant' using u-id, u-id-v
call 'c$setvariant' using passwd, passwd-v
MODIFY DATABASE-OBJ @LOGONSERVER
('crdb_ado.dll', server-name, database-name-v,
u-id-v, pASSwD-v)

Type mismatch error.
0 Likes
16 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

First, INameValuePairs is a class of the new enumerator class which we currently do not support.
Your inquiry to the item should however work. But you should not try to use c$getvariant, but:

77 somestring pic x(128). | number arbitrarily picked
...
PROCEDURE...
...
INQUIRE CONNECT-PROP-OBJ @Item ("Initial Catalog")
IN somestring.

The runtime does the cast for you. Have you tried this, doesn't it work?

The same goes here:

call 'c$setvariant' using database-name, database-name-v
call 'c$setvariant' using u-id, u-id-v
call 'c$setvariant' using passwd, passwd-v
MODIFY DATABASE-OBJ @LOGONSERVER
('crdb_ado.dll', server-name, database-name-v,
u-id-v, pASSwD-v)

Do not use setvariant, but pass the COBOL parameters directly. The runtime does the rest.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

That did not work by itself, but when we changed the code to this:

01 database-name pic x(25).
...
inquire CONNECT-PROP-OBJ @Item ("Initial Catalog") in connect-prop

inquire connect-prop @Value in database-name

We get the database name as expected!

Now, to move on the the other database proprerties and do the same.

Thank you Gisle.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

Well, we have interfaced Acucobol with Crystal Reports, but we have run into another obstacle. The reports are designed using a test database. The reports work correctly when run through our application using the test database but when we run it using another database on the same server - the report always reads the database from which the report was created. I understand that Crystal allows the datasource to be altered at runtime and have tried the following coding to accomplish this. There must be a step missing somewhere. Has anyone accomplished this task and can provide some insight? We have already verified that the reports do not have 'Save Data with report' checked.

Working storage variables:
01 CR-REPORT USAGE HANDLE OF APPLICATION.
01 REPORT-OBJ USAGE HANDLE OF REPORT.
01 CR-VIEWER USAGE HANDLE OF CrystalActiveXReportViewer.
01 DATABASE-OBJ USAGE HANDLE OF IDatabase.
01 DB-TABLE-COLL USAGE HANDLE OF IDatabaseTables.
01 DB-TABLE-OBJ USAGE HANDLE OF IDatabaseTable.
01 CONNECT-PROP-COLL USAGE HANDLE OF INameValuePairs.
01 CONNECT-PROP-OBJ USAGE HANDLE OF IConnectionProperty.

* create pointer to open report
MODIFY CR-REPORT @OPENREPORT (REPORT-PATH)
RETURNING REPORT-OBJ
* create pointer to database
INQUIRE REPORT-OBJ @DATABASE IN DATABASE-OBJ
* create pointer to database tables collection
INQUIRE DATABASE-OBJ @TABLES IN DB-TABLE-COLL
* create pointer to first table in database
INQUIRE DB-TABLE-COLL @Item(1) IN DB-TABLE-OBJ
* create pointer to connection properties collection of database table
INQUIRE DB-TABLE-OBJ @ConnectionProperties
IN CONNECT-PROP-COLL
* set the dll name
MODIFY DB-TABLE-OBJ @DllName = "crdb_ado.dll"
* delete all connection properties
MODIFY CONNECT-PROP-COLL @DeleteAll()
* add Provider connection property
MODIFY CONNECT-PROP-COLL @Add
("Provider" "SQLOLEDB")
* add Data Source (database server) connection property
MODIFY CONNECT-PROP-COLL @Add
("Data Source" SERVER-NAME)
* add Initial Catalog (database name) connection property
MODIFY CONNECT-PROP-COLL @Add
("Initial Catalog" DATABASE-NAME)
* add User ID connection property
MODIFY CONNECT-PROP-COLL @Add
("User ID" U-ID)
* add Password connection property
MODIFY CONNECT-PROP-COLL @Add
("Password" PASSWD)
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

Have CR support stated that you do the right thing? (I know they won't help you with ACUCOBOL-GT code, but is there a similar VB example that "should" work?)
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

Yes, CR (now owned by Business Objects for those of you looking) gave us this example which we were trying to duplicate....

Changing to SQL Server (OLE DB) at Runtime
'Declare a Connection Info Object
Dim ConnectionInfo As CRAXDRT.ConnectionProperties
'Set the Connection Info to Connection Properties of ?the table object
Set ConnectionInfo = Report.Database.Tables(1).ConnectionProperties
'Set the DLL name
Report.Database.Tables(1).DLLName = ?crdb_ado.dll?
'Clear the ConnectionProperties collection
ConnectionInfo.DeleteAll
'Add the OLE DB Provider
ConnectionInfo.Add ?Provider?, ?SQLOLEDB?
'Add the physical server name
ConnectionInfo.Add ?Data Source?, ?Server name?
'Add the database name
ConnectionInfo.Add ?Initial Catalog?, ?Database name?
'Add the user name
ConnectionInfo.Add ?User ID?, ?User name?
'Add the password
ConnectionInfo.Add ?Password?, ?Password?
'Set the fully qualified table name if different from ?the original data source
Report.Database.Tables(1).Location = ?database.owner.tablename?
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

There is one thing I notice when I browse this and look at a CR 10 def file I have.
You are using handles of the secondary interfaces directly. This might be the cause, at any rate, when there are primary interfaces, these are to be used. Please try substitute these:

01 DATABASE-OBJ USAGE HANDLE OF IDatabase.
01 DB-TABLE-COLL USAGE HANDLE OF IDatabaseTables.
01 DB-TABLE-OBJ USAGE HANDLE OF IDatabaseTable.
01 CONNECT-PROP-OBJ USAGE HANDLE OF IConnectionProperty.

with these:

01 DATABASE-OBJ USAGE HANDLE OF Database.
01 DB-TABLE-COLL USAGE HANDLE OF DatabaseTables.
01 DB-TABLE-OBJ USAGE HANDLE OF DatabaseTable.
01 CONNECT-PROP-OBJ USAGE HANDLE OF ConnectionProperty.

This one here:

01 CONNECT-PROP-COLL USAGE HANDLE OF INameValuePairs.

apparently has no primary, so you may use it as is.

You should apply this also for other part of your code, you should always use the primary when an object has one.

So, the $10000 question;
How do I reckognize one when I see one?
Answer:
Open the definiton file.
A primary always have a line DEFAULT-INTERFACE in its body, on the line after NAME.
If you look at a secondary interface, this will not have the line starting with DEFAULT-INTERFACE in its body.
Example:
Primary:

      * Database
           CLASS @Database
               CLSID, 9573BB21-A603-4A5E-A456-B377FF1C4EB1
               NAME, "Database"
               [B]DEFAULT-INTERFACE, "IDatabase"[/B]
      * Tables

Secondary:

      * IDatabase
           CLASS @IDatabase
               CLSID, AF376818-6120-4E28-96DD-63FD2DC27B7A
               NAME, "IDatabase"
      * Tables


Hope this helps.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

Thanks, that was something we did not realize (we have a CR 11 def but it is basically the same) and good to know. We changed to use the primary interface. Unfortunately, it did not fix this particular problem. We will continue to experiment, and post any successes we may have (hopefully).
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

Dear Sir:
I am a developer for a company producing accounting sofware for the construction industry. I have tried for several years to integrate my application with crystal reports. I had some success with Crystal vers 8.5 and acugt 5.2. I recently had a customer who purchased Crystal 11 and discovered they no longer supported the active-x method I was using. I cannot seem to get the syntax or logic correct in using the com method. It seems that you are doing this. I use vision files and AcuOdbc. I am ready to upgrade to version 11 of Crystal if I feel I can work out my problems. I basically want to be able to write an Acucobol program which will be able to specify a crystal rpt, supply the parameters, specify the odbc data set, present the user with the report in the viewer which then he could print if desired. Would you be willing to share with me some sample code of how you have done this. I have attached a file showing my feeble attempt to do this. This is very critical for my company and I would greatly appreciate your help.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

We wanted to post a "complete" solution after we had it working, and I believe we do now. Business Objects claims that you do not need to change the data connection for multiple tables in XI. But after experimentation and checking with some resellers/consultants we painstakenly found this was not true. And that goes for sub-reports too, if you use them.

So now we believe we have a working interface, which I will post in the attachment. Comments, suggestions, and questions are welcome.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

Originally posted by RSoftcheck
So now we believe we have a working interface, which I will post in the attachment. Comments, suggestions, and questions are welcome.


Your contribution to the forum is highly appreciated. Thanks!
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Acucobol and Crystal - connection to database

I have been working on my own application to call a crystal report. I notice in your example you do not use the command
Modify (handle) @readrecords()
Is this not necessary with cryatal 11. I am using 8.5.
I do have a problem with readrecords freezing my program about 50% of the time, otherwise works well,
thanks
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.