Question

Can the DatabasePlugin be used with SpreadSheetPlugin ? I run the query

%DATABASE_SQL{description="staff" sql="select StaffName from staffdetails where staffid='3'" format="$StaffName" separator="$nop"}%

and get the output Chengs.

But when I want to compare it using the $EXACT command in SpreadSheetPlugin using the command:

%CALC{"$EXACT(%DATABASE_SQL{description="staff" sql="select StaffName from staffdetails where staffid='3'" format="$StaffName" separator="$nop"}%,Chengs)"}%

I get the error $EXACT(No such DB staff,Abc) at /usr/lib/perl5/5.8/CGI/Carp.pm line 314. at /usr/lib/perl5 etc...

looks like the Database command can not be used inside the Spreadsheet command.

How do I achieve this ?

Environment

-- ChengappaCB - 10 Dec 2008

Answer

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.

Continuing the experiment, I changed the Plugin order and put Database Plugin first (followed by Spreadsheet Plugin). Now I don't get the error but the output is

%CALC{"$EXACT(Chengs

Now it looks as if it is executing the Database Plugin part but not the Spreadsheet Plugin part. What am I missing ?

-- ChengappaCB - 10 Dec 2008

Ah, yes, the plugin order should do it.

The CALC expects all parameters to be on one and the same line. That is, your CALC breaks if your DB query introduces newlines. Try a query that does not contain newlines.

-- PeterThoeny - 10 Dec 2008

As I have given the parameter separator="$nop", the DB query does not introduce new lines. For example, the command

%DATABASE_SQL{description="staff" sql="select !StaffName from staffdetails where staffid='3'" format="$StaffName" separator="$nop"}% Test for newline

gives the output:

Chengs Test for newline

without a newline between 'Chengs' and 'Test for newline' (verified from the source page of the html page). Any suggestions ? (I am wondering if the use of $NOP() in CALC will help but not sure how to go about it.

-- ChengappaCB - 11 Dec 2008

What is the exact DATABASE_SQL - in CALC statement you use?

What is the HTML output?

Another approach worth a try: You could stuff an escaped CALC inside the format of DATABASE_SQL. Example: format="$percntCALC{$dollarEXACT($StaffName, Chengs)}$percnt"

-- PeterThoeny - 11 Dec 2008

Oh! This seems to be working. I wasn't aware that 'format' can be used this way. Now my

%DATABASE_SQL{description="staff" sql="select StaffName from staffdetails where StaffId='3'" format="$percntCALC{$dollarEXACT($StaffName,Chengs)}$percnt" separator="$nop"}% 

gives an output "1" which is what I wanted. Thank you very much !

-- ChengappaCB - 11 Dec 2008

Glad it worked out! With this solution you do not need the special plugn execution order because the CALC is escaped and executes later.

-- PeterThoeny - 11 Dec 2008