How to insert CR values into a database 4

[peterswan]

Hello,

I'm interested in retrieving several values from a database and recalculating them in Crystal, and then inserting those new values into a table. This way they can be accessed and viewed more easily later on.

Is this possible in CR 8.5 or 9.0?

Thanks,



Peter Swanson

 

[Naith]

Hi Peter,

You cannot directly affect your data from Crystal as, like all reporting solutions, it merely offers a read-access view of your data.

What you can do, is export your Crystal data to a .txt file, and externally feed the txt file into the database. I don't know which database you use, but with Oracle for example, you could take your Crystal produced txt output and use SQLoader to insert the values from the txt file into your table.

Naith

 

[peterswan]

Hi Naith,

Thanks for the response. How could I tell Crystal to do the export and which fields to export? Also, how will it know which text file should get the data?

I'm using SQL Server 2000. Will SQLoader work with this?

Thanks,



Peter

 

[synapsevampire]

You can create tables and load data from within Crystal, especially using CR 9.

With prior versions you can export to an ODBC data source, but you cannot insert into an existing table, only create a new table and populate it.

You could then have an agent running which could check for the existence of the new table and do whatever needs doing with the data, then drop the table, allowing for the next CR process to then create the next table.

Hope this helps.

-k

 

[peterswan]

Hi SynapseVampire,

So how will Crystal know where to export the data, and which fields to export?

Thanks,

Peter

 

[Naith]

Nah, SQLoader is an Oracle tool. I only really deal with Oracle backends, but I would expect SQL Server has a similar functionality of it's own. One of the guys who uses it here should be able to confirm.

If I show you what I mean, you can deduce if this is what you'd be after:

YourTable
---------
ID number(2) not null
Name varchar2(8) not null
DateStmp date

Crystal output exported to YourFile.txt
1,Bob,3Mar2003
2,Kat,3Mar2003
3,Cyd,3Mar2003
4,Gil,3Mar2003,Y
5,Jim
(The idea is to output the fields from Crystal in the order that they should be inserted into the table. i.e. don't report any additional fields (i.e. Gil's attempt to be added to YourTable will failover), and if you don't report any values for columns which exist in YourTable, ensure that column value can be null) - like in Jim's row.)

SQLoader equivalent script gist:
Insert values from c:\YourFile.txt into Database.YourField;
(You'd set this in an automated script, or in your case, a batch script)

Commit the changes in the script, and check your table - which'd be updated.

Naith

PS: Someone confirm if SQL Server 2000 has a SQLoader equivalent. Thanks.

 

[Naith]

SV - Crystal9 has read write?

 

[peterswan]

Thanks, Naith,

That sounds good. I'm assuming that once I can get the Crystal values to the text file that I can schedule a job in SQL Server to load that data.

The real question for me right now is how to get the data from Crystal into the text file. It's a main report with about 75 subreports. Obviously not every field will be entered in. But certain fields will, and I'm hoping to do so based on user selection, probably if a parameter is a certain value.

Thanks,

Peter

 

[synapsevampire]

Naith: Crystal 9 uses real SQL, so I don't see why you can't execute SQL to write to a table from within it.

I haven't tried it, but it makes sense.

Peter: The fields exported are the fields in the report. Select the export button in CR and point to an ODBC data source, it will prompt you through it.

If you need to automate this process, you'll end using external code (such as VB), or a third party product to aid in this.

-k

 

[peterswan]

OK,

Thanks for your help,



Peter

 

[chelseatech]

This thread has covered a lot of ground, and I would like to add my comments.

1) CR9 is read/write into the DB, but it does it at the start of the report. I wrote a couple of test SQL commands and one added a audit record each time the report ran, the other modified a record. But once it's done, the SQL must retrieve a record set to use in the report. So CR9 is not a solution to your problem.

2) Don and I discussed writing a UFL to execute some SQL code from within a report. It's not difficult technically, but we decided against it because we believed that it violated the CR marketing that said Cr was read only.

3) However at a site recently I saw an unusal function listed. Someone had supplied a SQLUpdate UFL as part of their App. As I said, it's not difficult to write.

4) My personal recommendation is to get your report to write a SQL INSERT statement, and export that as TXT. Then use the TXT inside a SQL tool. A lot safer all around.

Good luck Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Naith]

Good points, Chelsea.

 

[synapsevampire]

Hmmm, good points, Chelsae.

Since it sounds like your approach is to make the process manual (item 4), if they were using CR 9, couldn't they make an on demand subreport, and pass the modified values from the Main to the On Demand Subreport, and let the Subreport write the values?

Seems a whole lot cleaner than exporting to a text file...

And if they're going to use a SQL window to execute an exported text file of constructed SQL, I'd suggest they not bother with Crystal at all, modify and write within SQL.

-k

 

[chelseatech]

SynapseVampire,

Excellent suggestion. I hadn't thought of that approach, but I'm sure it would work. A subreport in CR9 is a great solution.

Pass the parameter from the main report to invoke the update in the subreport SQL command.

You could even make it on demand, and pass additional manual parameters for each call. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[rosemaryl]

Dear Peterswan,

I know this is late but I haven't been able to check in on tek-tips for a while so I am catching up.

I use Crystal 8.5 and have updated databases with Crystal Reports - sort of.

What I do when I need to do this is to base the report on a stored procedure, it is the sp which does all the work and updates the database (I perform all calcualtions/formulas in the sp that you would normally do in Crystal).

As long as a stored procedures' end result is a recordset, crystal doesn't care what you do before-hand. I have used this procedure to update a db for example, to note the last report print date/time in the db.

This means that Crystal is used to just display the information, but it does make it easily viewable.

If you needed to base the calculations on imput, then you could use parameters to pass that information.

This works really well for me.

Hope that was helpful,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[peterswan]

Thanks Rosemary,

This has been a most informative thread. I'll be sure and post here to let anyone know of our decision, and whether any recommended solution was used.

Thanks again,



Peter Swanson

 

[AnnetteH]

Has anyone successfully updated an Oracle database using Crystal 9 SQL Commands? I've been unsuccessful.
The Crystal whitepaper 'Updating a database using Crystal Reports' has a sample SQL Command for SQL Server 2000 as follows.

UPDATE "SalesTable"
SET "SalesTable"."Quota" = {?Quota}
WHERE "SalesTable"."SalesPerson" = {?Name}
SELECT * FROM "SalesTable"

I created a similar SQL Command for Oracle, both with and without ending semicolons, but it will not execute.

Using the native driver and no semicolons, I get ORA-24333 'zero iteration count.' Using the CR ODBC driver, I get 'Unrecognizable ODBC SQL escape sequence.' Replacing the parameters with constants, gives ORA-00933 'SQL command not properly ended.'

Any suggestions?

 

[synapsevampire]

Try constructing the SQL in an Oracle tool and then copy and paste it.

Make sure that the final result of the SQL returns something to Crystal.

-k

 

[AnnetteH]

The statements I'm using work fine from SQL*Plus as long as I end each statement with a semicolon. The update happens followed by the results of the select.

From Crystal, using the same statements and the native driver, I get the zero iteration count error. Using ODBC, I get invalid character unless I remove the semicolons. In which case, I get the SQL Command not ended properly error.

 

[AnnetteH]

Guess I should have said that it works OK from SQL*Plus Worksheet like this.

UPDATE "SalesTable"
SET "SalesTable"."Quota" = 1000
WHERE "SalesTable"."SalesPerson" = 'Joe';
SELECT * FROM "SalesTable";

From SQL*Plus it would have to be like this.

UPDATE "SalesTable"
SET "SalesTable"."Quota" = 1000
WHERE "SalesTable"."SalesPerson" = 'Joe';
/
SELECT * FROM "SalesTable";
/

I've tried both methods.

Ordinarily if you were going to execute multiple statements, you would put them in a Begin-End block, but you can't use the normal Select statement in a block. You have to use Select Into and put the result into some declared variables. That doesn't make sense in this case.