Ingres Stored Procedure - Dynamic SQL
Lasrado, Lester (CSS)
Hi,
Can anyone help me out with an example of a stored procedure using dynamic where clause in the SQL select query.
Basically I would like to construct a where_clause based on parameters passed and use it to query the database.
where_clause = “D in (” + :list_passed_to_storedproc + “)”;
Select A,B,C
From ABC
Where :where_clause;
Thanks,
Lester
This email and any attachment to it are confidential. Unless you are the intended recipient, you may not use, copy or disclose either the message or any information contained in the message. If you are not the intended recipient, you should delete this email and notify the sender immediately.
Any views or opinions expressed in this email are those of the sender only, unless otherwise stated. All copyright in any Capita material in this email is reserved.
All emails, incoming and outgoing, may be recorded by Capita and monitored for legitimate business purposes.
Capita exclude all liability for any loss or damage arising or resulting from the receipt, use or transmission of this email to the fullest extent permitted by law.
This message has been scanned for malware by Websense. www.websense.com
Bodo Bergmann
This is not possible.
Database procedures cannot have a dynamic where clause,
the database items used (tables, columns, etc.) must exist at the time the procedure is created.
Bodo.
Bodo Bergmann | Sr. Software Engineer | OpenROAD Worldwide Development | Actian Germany GmbH | Ohmstr. 12 | 63225 Langen | GERMANY | +49 6103 9881 0
--
You received this message because you are subscribed to the Google Groups "OpenROAD Users Mailing List" group.
To post to this group, send email to openroa...@googlegroups.com.
To unsubscribe from this group, send email to openroad-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/openroad-users?hl=en.
Chris Clark
1. use a session temporary table (they are actually called GTTs in
the docs) as a parameter to the procedure, see
http://docs.actian.com/ingres/9.3/connectivity-guide/1761-how-database-procedures-are-called
2. if you need something more dynamic than an IN list, you could
dynamically construct the DDL for the database procedure and then
invoke it. However it may be more appropriate to simple do this
client side and not use a stored procedure.
This is assuming an Ingres backend.
Chris
Bodo Bergmann wrote:
>
> This is not possible.
>
> Database procedures cannot have a dynamic where clause,
>
> the database items used (tables, columns, etc.) must exist at the time
> the procedure is created.
>
> Bodo.
>
> Bodo Bergmann | Sr. Software Engineer | OpenROAD Worldwide Development
> | Actian Germany GmbH | Ohmstr. 12 | 63225 Langen | GERMANY | +49 6103
> 9881 0
>
> *From:* openroa...@googlegroups.com
> [mailto:openroa...@googlegroups.com] *On Behalf Of *Lasrado,
> Lester (CSS)
> *Sent:* Friday, December 09, 2011 5:36 PM
> *To:* openroa...@googlegroups.com
> *Subject:* [openroad-users] Ingres Stored Procedure - Dynamic SQL
>
> Hi,
>
> Can anyone help me out with an example of a stored procedure using
> dynamic where clause in the SQL select query.
>
> Basically I would like to construct a where_clause based on parameters
> passed and use it to query the database.
>
> where_clause = �D in (� + :list_passed_to_storedproc + �)�;
Lasrado, Lester (CSS)
My Client here is SQL Server Reporting Services (an RDL file), hence I
am afraid will not be able to do the following.
However can create a session temporary table in the stored procedure
based on the IN list passed and use this table in a sub-query/join.
But not sure if stored procedures allow to create tables (session on
normal)?
Also is there any documentation/examples on stored procedures that we
can refer as we will be working on this more in the near future.
Thanks,
Lester
Chris
> where_clause = "D in (" + :list_passed_to_storedproc + ")";
>
> Select A,B,C
>
> From ABC
>
> Where :where_clause;
>
> Thanks,
>
> Lester
>
--
You received this message because you are subscribed to the Google
Groups "OpenROAD Users Mailing List" group.
To post to this group, send email to openroa...@googlegroups.com.
To unsubscribe from this group, send email to
openroad-user...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/openroad-users?hl=en.
This email and any attachment to it are confidential. Unless you are the intended recipient, you may not use, copy or disclose either the message or any information contained in the message. If you are not the intended recipient, you should delete this email and notify the sender immediately.
Chris Clark
> My Client here is SQL Server Reporting Services (an RDL file), hence I
> am afraid will not be able to do the following.
> However can create a session temporary table in the stored procedure
> based on the IN list passed and use this table in a sub-query/join.
>
It sounds like you are using an Ingres DBMS with a 3rd party (in this
case Microsoft) reporting tool and not using OpenROAD. I'm not clear
using a dbproc has an advantages here over dynamic SQL.
This seems more like a SQL Server Reporting Services question, "how can
dynamic SQL be issued in an RDL file?"
I know very little about that reporting tool and this may not be the
best mailing list for that question, I found an article that claims
dynamic SQL is possible which may be a good starting point
http://msdn.microsoft.com/en-us/library/aa237477(v=sql.80).aspx
<http://msdn.microsoft.com/en-us/library/aa237477%28v=sql.80%29.aspx>
> But not sure if stored procedures allow to create tables (session on
> normal)?
>
Most DBMS do not support DDL in dbprocs (Ingres included sadly).
> Also is there any documentation/examples on stored procedures that we
> can refer as we will be working on this more in the near future.
>
There are some examples with dbprocs in the Ingres documentation set. We
have the html docs (as I linked to in my previous email) but downloading
the PDFs and using the "Bookshelf" is probably the best way to search
them, the bookshelf offers a way to search all the docs locally. The
Ingres SQL training course (and I think the DBA one) covers dbprocs too
so you might want to check in with the local Actian office and see if
there are any sessions near by, however I'm not clear a dbproc is what
you want in a report.
Are you tied to SQL Server Reporting Services? OpenROAD with a
QueryObject may be more appropriate here (this would be my default
approach to take if you already have OpenROAD). One of our partners,
Jaspersoft have a JDBC/Java based tool that can also handle dynamic SQL
if you are looking for a reporting appliance.
One word of warning if constructing SQL dynamically, be aware of how the
query is constructed (i.e. is a SQL inject attack possible?)
Chris