CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> Tips & Solutions

 Obtaining CCS Generated SQL for Select, Insert, Update and Delete

Print topic Send  topic

Author Message
mamboBROWN


Posts: 1713
Posted: 07/19/2006, 8:14 AM

I was given the answer from support. A big thanks goes out to Helen D. from support. (I wanted to store the CCS generated SQL queries for any changes to the database.)

Here is the solution that was given to me:
Quote :
Edwin,
CCS 3 utilizes special function CCBuildSQL() which builds sql query. So to print queries passed to database please use the following code
Before Execute Select event
echo $Container->DataSource->OptimizeSQL(CCBuildSQL($Container->DataSource->SQL, $Container->DataSource->Where, $Container->DataSource->Order));

But to print insert, update, delete queries use simpler way.
Before Execute Insert/Update/Delete events
echo $Container->DataSource->SQL;

Regards,
Helen


NOTE: the generated CCS SQL uses single quotes (for text fields) so if you want to put the generated SQL in a text field be careful how you create your statement. Here is the code that I used:
$db = new clsDBConnection1();
$qry = $Container->DataSource->SQL;
$SQL = 'INSERT INTO tracking(user_id,user_action_id,trk_after_change)'.
'VALUES ('.CCGetSession('UserID').',2,"'.$qry.'")';
$db->query($SQL);
$db->close;
THE DOUBLE QUOTES ARE BOLD!!
View profile  Send private message
wkempees


Posts: 1679
Posted: 07/19/2006, 8:33 AM

Edwin
I have two questions:
1. You make a new connection ($db=...) to the database BEFORE $qry=
2. $qry = $Container->DataSource->; mseems incomplete.

Is this working? And where did you put it ?

I would expect:

  
$qry = $Container->DataSource->SQL;  
  
$db = new clsDBConnection1();  
$SQL = 'INSERT INTO tracking(user_id,user_action_id,trk_after_change)'.  
'VALUES ('.CCGetSession('UserID').',2,"'.$qry.'")';  
$db->query($SQL);  
$db->close;  
  
As I read the post, you will have empty tracking records.

With respect, and greetings
Walter



_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
mamboBROWN


Posts: 1713
Posted: 07/19/2006, 12:48 PM

wkempees
Answers:
1. Yes, I do. I usually put my set/declared variables at the top. (just my preference).
2. For some reason when I cut and pasted the code it got truncated. I have fixed it and it is now correct. Thanks for pointing it out to me.
View profile  Send private message
wkempees


Posts: 1679
Posted: 07/19/2006, 1:51 PM

cheers, strenght in cooperation as always.
and my remark about doing the declaration first, is based on the fact that a new $db is declared and the assignment is on the prior one, so for readability.........
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)

if you liked this info PAYPAL me: http://donate.consultair.eu
View profile  Send private message
feha


Posts: 712
Posted: 07/19/2006, 2:41 PM

great tips mamboBrown
thanks :-)
_________________
Regards
feha

www.vision.to
feedpixel.com
View profile  Send private message
mamboBROWN


Posts: 1713
Posted: 07/20/2006, 6:23 AM

No problem, feha. I truly believe in sharing the knowledge. It helps all of us to become better programmers.
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.