• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1034
  • Last Modified:

Crystal Reports - updating database via SQL statement

This relates to the question here:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27380729.html

Can someone explain the technicals of this approach.
Where is the sql statement going in the report?  Are there specific scenarios where this does or doesn't work?

I had previously believed that the drivers used in CR were specifically  read only on the selected tables. So this is a new thing for me.
0
peter57r
Asked:
peter57r
  • 2
  • 2
  • 2
2 Solutions
 
GJParkerCommented:
Hi Peter

Crystal Reports will run any SQL statement against a database as long the SQL is valid, the user account being used has the priveleges to execute the statement and most importantly it must return a recordset i.e.

The following statement would run from a CR command as long as the conditions above are met

DROP table tblEmployee
GO
SELECT * FROM tblAddress

In the past I have used a command in a subreport in the main report header to insert a record into an Audit table in the database every time the report is run i.e.

INSERT INTO tblAudt (ReportName, RunDate, Param1, Param2)
VALUES ('Report1', GetDate(), '{?Param1}', '{?Param2}')
GO
Select 1

The technique will only work for databases that support running multiple statements, these are usually server databases such as MS SQL Server, Oracle etc this won't work for an MS Access database.

HTH
0
 
peter57rAuthor Commented:
Very interesting .

It has set me thinking as to why I thought what I thought and I'm fairly sure it was based on 'authoritative' comments in a particular CR specialist site.  There is an extremely remote  ((:-)) possibility that I have misinterpreted such comments and I might have a search to see if I can find it again, although it will be quite a long time ago that I saw it.

So the situation is that if I can write the code in SQL Server Management Studio I should be able to run it in a CR 'Add Command' box - provided it returns something.  
0
 
GJParkerCommented:
Yes as I understand it that is correct, the only difficult comes when you want to include paramter values in the command as some DB's / connections require you to add the single quotes for string parameters and some will add handle the SQL without them.

0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
James0628Commented:
FWIW, I'm not aware of any inherent limitations on updating tables from CR, but I've never tried to update "regular" tables either (never needed to).  I did have one report that executed an MS SQL stored procedure that created a temp table, inserted rows into it, did a couple of UPDATE's to set some columns in the temp table, and then did a couple of DELETE's to delete some unnecessary rows from the temp table.  So, table updates are possible, in general.  Based on that, my thinking has been like GJParker, that it's possible to update regular tables, if you have the permissions.  But I've never actually tried it.

 James
0
 
peter57rAuthor Commented:
I haven't found the document that sent me off this way, but I can now recall that the situation was a bit different to this in that there was a potential requirement to update the database based on the results calculated in the report.  Clearly that is not what we are talking about here and unfortunately I have, over time,  mistakenly generalised that experience to a 'no updates' position.
I have never had anyone else ask me for a database update feature with a report so I've never had to review my thinking.

Very useful lesson learnt.

0
 
James0628Commented:
Yeah, that would be a different situation.  Funny how things gets stuck in your head a certain way sometimes (well, my head anyways :-).

 James
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now