retrieve sql from database

[healey_mark]

Our organization has alot of SQL, and we prefer to be able to store our
application SQL in a DB; we find it easier to maintain that way.

What I would like to do:
<cfquery name="getstatement" datasource="#datasource#">
SELECT code from sqlcode where name='statement1'
</cfquery>
<cfquery name="aQuery" datasource="#datasource#">
#getstatement.code#
</cfquery>

The SQL code would be something like:
select dept_id, name
from departments
where name like <cfqueryparam cfsqltype="cf_sql_varchar" value="#NameSearch#"
maxlength="20">

The problem I have is getting the <cfqueryparam> to evaluate in the above
example.

Does anyone have a solution for this problem? Note that I cannot use stored
procedures.

[MikerRoo]

You can try playing with the Evaluate() function but I believe you will find
that it's not possible to execute a tag like <cfqueryparam> this way.

If reverting to plain SQL, remember the PreserveSingleQuotes() function.

To store SQL and still be able to use parameterized queries, you'll might
pre-parse the SQL like so:

Create 2 tables:
1) Main SQL has at least these columns:
1.a) ID
1.b) Pre where-clause SQL text.
1.c) Post where-clause SQL text.

2) Parameterized Where-Clause table has at least these columns:
2.a) ID
2.b) FKey into table 1
2.c) SQL column name and operator, (EG. "name = ")
2.d) cfsqltype value
2.e) CF variable name
2.xxx ) Additional columns for additional attributes like maxlength.

Using these two tables you can build a parameterized SQL call.
Of course, fancy sub-selects, etc. could get messy.

-- MikeR

[healey_mark]

Thanks MikerRoo,

I came to the same conclusion: evaluate() doesn't work. I am left with
either a manual parsing solution (as you described), writing the SQL to a file
and including it, or using cfobject to work with the underlying java code.

I will give this more thought; I am mentioning my other ideas for the benefit
of anyone else who's facing a similar problem.

[MikerRoo]

Hey healey_mark,

It looks like you saw the correct reply to this post?

Because now the reply has been overwritten with my post on another thread.
Can you confirm that you saw the correct reply at one point ?

Thanks,
-- MikeR

[healey_mark]

I saw the reply--fortunately the entire text is emailed when you make your post.

I'm reproducing it here in case anyone else is following the thread:

You can try playing with the Evaluate() function but I believe you will find
that it's not possible to execute a tag like <cfqueryparam> this way.

If reverting to plain SQL, remember the PreserveSingleQuotes() function.

To store SQL and still be able to use parameterized queries, you'll might
pre-parse the SQL like so:

Create 2 tables:
1) Main SQL has at least these columns:
1.a) ID
1.b) Pre where-clause SQL text.
1.c) Post where-clause SQL text.

2) Parameterized Where-Clause table has at least these columns:
2.a) ID
2.b) FKey into table 1
2.c) SQL column name and operator, (EG. "name = ")
2.d) cfsqltype value
2.e) CF variable name
2.xxx ) Additional columns for additional attributes like maxlength.

Using these two tables you can build a parameterized SQL call.
Of course, fancy sub-selects, etc. could get messy.

-- MikeR

[MikerRoo]

Thank you.