I assume you have are having problems getting your RC$variable to take as the SQL
string.. We do a lot of this but they have to be carefully constructed since you are
within a scripting language.. Example, special characters that you might need in your
query may cause your script to break.. Good examples of these might be less thans and
greater thans.. You must use escape characters when you come up with one of these to
ensure that you don't break your script.
To help you out with some of this I have included a small sample snip where I had to
custom build a where clause based on a variable passed.. Note the last set where I had
to include the \ escape character to keep from breaking the script.
Hope this helps.
Danny
<RC$whereclause = WHERE [ID] Like '%%%%%%' >
<RCif RC$fromdate ! NULL and RC$todate = NULL>
<RC$whereclause = RC$whereclause and [Date]=#RC$fromdate#>
<RCendif>
<RCif RC$fromdate = NULL and RC$todate ! NULL>
<RC$whereclause = RC$whereclause and [Date]=#RC$todate#>
<RCendif>
<RCif RC$fromdate ! NULL and RC$todate ! NULL>
<RC$whereclause = RC$whereclause and [Date]\>=#RC$fromdate#>
<RC$whereclause = RC$whereclause and [Date]\<=#RC$todate#>
<RCendif>
On 12/Sep/2002 04:26:52, M. Skeoch \(CDS\) wrote:
> Ahhhhhh... I see.
>
> In the past I have built each call statement then bound it to a RC$tag.
> Depending on what you want, it calls the appropriate one for the RCQ.
> However, you are right, this requires multiple IF statements - but only
> for each template.
>
> Matt.
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [<A
>HREF="mailto:[EMAIL PROTECTED]";>mailto:[EMAIL PROTECTED]</A>] On Behalf
> Of Ray Noel
> Sent: September 12, 2002 3:32 AM
> To: [EMAIL PROTECTED]
> Subject: [sambar] Database scripting. {03}
>
> Yes Matt,
>
> Shot myself in the foot here. Bad example, I can't understand it either
> :-)
>
> But suppose the user wanted one of four or five types of reports which
> could be ordered on 4 or 5 different fields.
> What I would like to be able to do is examine each variable separately
> and
> create the SQL string dynamically, then pass the SQL string to the
> query.
> For example, queries on an asset register might be for many purposes,
> annual stocktake, warranty period expiry, manufacturers recall, etc.
> Each of the above reports might require different fields and might be
> ordered differently, which is easily catered for if you can build the
> SQL
> string to suit each purpose without the necessity for multiple nested
> <RCif>'s.
>
> Hopefully that is nearer what I was trying to say earlier...
>
> Regards
> Ray
>
>
> At 04:03 PM 12/09/2002, you wrote:
>
> >#Hello to all list members,
> >
> >#I guess this database scripting question is really for Tod, but maybe
> >it's
> >#already easily achievable with the existing Sambar database scripting.
> >#How can I code a query based on as many as six user-selected options
> >#without a complex nested <RCif> structure and multiple hard-coded SQL
> >#statements?
> >
> >Are you looking to drop or add any of the statements if the statement
> is
> >empty or not selected?
> >
> >#I think this sort of thing is referred to as Query-by-Example. It's
> >easily
> >#achievable using other scripting languages like PHP, where the query
> >string
> >#is built incrementally and executed once and I'm hoping it might be a
> >#simple programming operation for Tod to give Sambar scripters the
> >option of
> >#either hard-coding the string or using a <RC$sqlstring> variable that
> >can
> >#be created incrementally, based on contents of other <RC$> variables
> >passed
> >#to the .stm script.
> >
> >#An example
> >#<RC$sqlstatement = "select workstationid, supplier, serialnumber, ram,
> >hdd
> > from machine
> > where location = 'RC$location' and
> supplier
> >=
> >#'RC$supplier' and ram = 'RC$ram' and hdd = 'RC$hdd'">
> >
> >#<RCQtest sql="$RC$sqlstatement"
> > format="<TR><TD>%s<TD>%s</TD><TD>%s</TD><TR>" >
> >
> >This example works (with the exception of the front $ on
> >$RC$sqlsatement) - is this a current or future example?
> >
> >#The above query was difficult to code and maintain
> >
> >Why is the above example difficult to maintain and code?
> >
> >#and I do believe the suggested additional query method would
> >#**significantly enhance** Sambar Server functionality. What do you
> >think #list members, Tod?
> >
> >I guess I don't understand what you mean :(
> >
> >I've been using Sambar query to do everything including very complex
> and
> >long procedures without running into any problems.
> >
> >Matt.
> >-------------------------------------------------------
> >To unsubscribe please go to <A TARGET="_blank"
>HREF="http://www.sambar.ch/list/";>http://www.sambar.ch/list/</A>
> -------------------------------------------------------
> To unsubscribe please go to <A TARGET="_blank"
>HREF="http://www.sambar.ch/list/";>http://www.sambar.ch/list/</A>
> -------------------------------------------------------
> To unsubscribe please go to <A TARGET="_blank"
>HREF="http://www.sambar.ch/list/";>http://www.sambar.ch/list/</A>
>
>
>
>
>
-------------------------------------------------------
To unsubscribe please go to http://www.sambar.ch/list/
