Embedded SQL in Design
Deskin, Bob
Bob.Deskin@Cognos.COM
Fri, 22 Mar 2002 12:55:12 -0500
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C1D1CA.B6A8CF80
Content-Type: text/plain;
charset="ISO-8859-1"
As well, there are things that you can do with native SQL that you can't do
with native PowerHouse or that would be very complex. But I look at that as
just another example of PowerHouse's flexibility. If you can't do what you
want with design statements, you can use procedure code. If you can't do it
with procedure code, there's SQL or even external subroutines.
Bob Deskin
PowerHouse Web Product Manager, Application Development Tools, Cognos Inc.
bob.deskin@cognos.com (613) 738-1338 ext 7268 FAX: (613) 727-1178
3755 Riverside Drive P.O. Box 9707 Stn. T, Ottawa ON K1G 4K9 CANADA
-----Original Message-----
From: Edis, Bob [mailto:BEdis@usbnc.org]
Sent: Friday, March 22, 2002 12:42 PM
To: PowerHouse List (E-mail)
Subject: RE: Embedded SQL in Design
G'day Lorry
Depending on the platform you are using embedded SQL can dramatically
improve performance of you PowerHouse code. It generally is only available
if you are accessing a SQL aware DBMS (usually a RDBMS). An example is
accessing an Oracle 8 database with PowerHouse. This uses an inefficient
ODBC connection (SQLNet) that without using embedded SQL offers poor
performance. On the other hand an application accessing Oracle Rdb may not
benefit much from embedded SQL as PowerHouse uses (or at least use to) a
more direct database connection method than ODBC.
Regards,
Blue
-----Original Message-----
From: Lorry Litman [mailto:LLitman@exchange.hsc.mb.ca]
Sent: Friday, March 22, 2002 11:36 AM
To: 'Fry, Mark'; Deskin, Bob; powerh-l@lists.swau.edu
Subject: RE: Embedded SQL in Qdesign
Hi,
I've not used and am not that familiar with the embedded SQL, could someone
explain the why and when one would use this?
Is this to overcome some things that can't be done or is it more effecient
or ...?
Thanx
Lorry Litman
-----Original Message-----
From: Fry, Mark [mailto:Mark.Fry@COGNOS.com]
Sent: Friday, March 22, 2002 4:43 AM
To: Deskin, Bob; powerh-l@lists.swau.edu
Subject: RE: Embedded SQL in Qdesign
...and another example (not from the manual this time) is embedding SQL DML
directly into a procedure without declaring an SQL cursor, as in...
PROCEDURE PREUPDATE RECOVERABLE
BEGIN
SQL IN <databasename> &
UPDATE PERSONNEL &
SET SALARY = 1000000 &
WHERE EMPLOYEE_CODE = 'MF'
END
(<sigh>, if only!!!) :-)
Best Regards,
Mark Fry
Mark.Fry@Cognos.com
-----Original Message-----
From: Deskin, Bob [ mailto:Bob.Deskin@cognos.com
<mailto:Bob.Deskin@cognos.com> ]
Sent: 21 March 2002 19:21
To: powerh-l@lists.swau.edu
Subject: RE: Embedded SQL in Qdesign
I don't think a few examples are going to help in the generalized case. You
can download the manuals for free from www.cognos.com/powerhouse. But here's
an example from the manual.
> SET LIST SQL
> SQL DECLARE EMPLIST CURSOR FOR &
> SELECT EMPLOYEE, FIRST_NAME, LAST_NAME, &
> EMPLOYEES.BRANCH,BRANCH_NAME &
> FROM EMPLOYEES, BRANCHES
> SCREEN EMPBRANCHC
> CURSOR EMPLIST &
> WHERE (EMPLOYEES.BRANCH = BRANCHES.BRANCH) &
> PRIMARY KEY EMPLOYEE
> __ Sql after substitutions are applied:
__ SELECT EMPLOYEE, FIRST_NAME, LAST_NAME,
__ EMPLOYEES.BRANCH, BRANCHES.BRANCH,
__ BRANCH_NAME
__ FROM EMPLOYEES, BRANCHES
__ where EMPLOYEES.BRANCH = BRANCHES.BRANCH
Bob Deskin
PowerHouse Web Product Manager, Application Development Tools, Cognos Inc.
bob.deskin@cognos.com (613) 738-1338 ext 7268 FAX: (613) 727-1178
3755 Riverside Drive P.O. Box 9707 Stn. T, Ottawa ON K1G 4K9 CANADA
-----Original Message-----
From: John Hunter [ mailto:jthunter@nbnet.nb.ca
<mailto:jthunter@nbnet.nb.ca> ]
Sent: Thursday, March 21, 2002 2:18 PM
To: powerh-l@lists.swau.edu
Subject: Embedded SQL in Qdesign
Hi Gang,
Could some kind soul please show me an example of an embedded SQL in
Qdesign? I would RTFM but, alas, I don't have one. I think I'm close, but
am still running into problems. My platform is Powerhouse 820 on VMS.
Thanks,
-John
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
<http://lists.swau.edu/mailman/listinfo/powerh-l>
This list is closed, thus to post to the list you must be a subscriber.
This message may contain privileged and/or confidential information. If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so. Thank you.
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
<http://lists.swau.edu/mailman/listinfo/powerh-l>
This list is closed, thus to post to the list you must be a subscriber.
This message may contain privileged and/or confidential information. If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so. Thank you.
This message may contain privileged and/or confidential information. If you
have received this e-mail in error or are not the intended recipient, you
may not use, copy, disseminate or distribute it; do not open any
attachments, delete it immediately from your system and notify the sender
promptly by e-mail that you have done so. Thank you.
------_=_NextPart_001_01C1D1CA.B6A8CF80
Content-Type: text/html;
charset="ISO-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
<TITLE>RE: Embedded SQL in Qdesign</TITLE>
<META content="MSHTML 5.00.3315.2870" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=821125417-22032002>As
well, there are things that you can do with native SQL that you can't do with
native PowerHouse or that would be very complex. But I look at that as just
another example of PowerHouse's flexibility. If you can't do what you want with
design statements, you can use procedure code. If you can't do it with procedure
code, there's SQL or even external subroutines.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=821125417-22032002>
<P><FONT face=Arial size=2>Bob
Deskin
</FONT><BR><FONT face=Arial size=2>PowerHouse Web Product Manager, Application
Development Tools, Cognos Inc.</FONT> <BR><FONT face=Arial
size=2>bob.deskin@cognos.com (613) 738-1338 ext 7268 FAX: (613) 727-1178</FONT>
<BR><FONT face=Arial size=2>3755 Riverside Drive P.O. Box 9707 Stn. T, Ottawa ON
K1G 4K9 CANADA</FONT> </P></SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Edis, Bob
[mailto:BEdis@usbnc.org]<BR><B>Sent:</B> Friday, March 22, 2002 12:42
PM<BR><B>To:</B> PowerHouse List (E-mail)<BR><B>Subject:</B> RE: Embedded SQL
in Design<BR><BR></DIV></FONT>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=283083717-22032002>G'day Lorry</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=283083717-22032002></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=283083717-22032002>Depending on the platform you are using embedded SQL
can dramatically improve performance of you PowerHouse code. It
generally is only available if you are accessing a SQL aware DBMS (usually a
RDBMS). An example is accessing an Oracle 8 database with
PowerHouse. This uses an inefficient ODBC connection (SQLNet) that
without using embedded SQL offers poor performance. On the other hand an
application accessing Oracle Rdb may not benefit much from embedded SQL as
PowerHouse uses (or at least use to) a more direct database connection method
than ODBC.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=283083717-22032002></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=283083717-22032002>Regards,</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=283083717-22032002>Blue</SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Lorry Litman
[mailto:LLitman@exchange.hsc.mb.ca]<BR><B>Sent:</B> Friday, March 22, 2002
11:36 AM<BR><B>To:</B> 'Fry, Mark'; Deskin, Bob;
powerh-l@lists.swau.edu<BR><B>Subject:</B> RE: Embedded SQL in
Qdesign<BR><BR></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=536323017-22032002>Hi,</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=536323017-22032002></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=536323017-22032002>I've not used and am not that familiar with the
embedded SQL, could someone explain the why and when one would use
this?</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=536323017-22032002>Is
this to overcome some things that can't be done or is it more effecient or
...?</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=536323017-22032002></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=536323017-22032002>Thanx</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=536323017-22032002> Lorry
Litman</SPAN></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Fry, Mark
[mailto:Mark.Fry@COGNOS.com]<BR><B>Sent:</B> Friday, March 22, 2002 4:43
AM<BR><B>To:</B> Deskin, Bob; powerh-l@lists.swau.edu<BR><B>Subject:</B>
RE: Embedded SQL in Qdesign<BR><BR></DIV></FONT>
<P><FONT size=2>...and another example (not from the manual this time) is
embedding SQL DML directly into a procedure without declaring an SQL
cursor, as in...</FONT></P>
<P><FONT size=2>PROCEDURE PREUPDATE RECOVERABLE</FONT> <BR><FONT
size=2>BEGIN</FONT> <BR><FONT size=2> SQL IN <databasename>
&</FONT> <BR><FONT size=2> UPDATE PERSONNEL
&</FONT> <BR><FONT size=2> SET SALARY = 1000000
&</FONT> <BR><FONT size=2> WHERE EMPLOYEE_CODE =
'MF'</FONT> <BR><FONT size=2>END</FONT> </P>
<P><FONT size=2>(<sigh>, if only!!!) :-)</FONT> </P>
<P><FONT size=2>Best Regards,</FONT> </P>
<P><FONT size=2>Mark Fry</FONT> <BR><FONT
size=2>Mark.Fry@Cognos.com</FONT> </P>
<P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From:
Deskin, Bob [<A
href="mailto:Bob.Deskin@cognos.com">mailto:Bob.Deskin@cognos.com</A>]</FONT>
<BR><FONT size=2>Sent: 21 March 2002 19:21</FONT> <BR><FONT size=2>To:
powerh-l@lists.swau.edu</FONT> <BR><FONT size=2>Subject: RE: Embedded SQL
in Qdesign</FONT> </P><BR>
<P><FONT size=2>I don't think a few examples are going to help in the
generalized case. You</FONT> <BR><FONT size=2>can download the manuals for
free from www.cognos.com/powerhouse. But here's</FONT> <BR><FONT size=2>an
example from the manual.</FONT> </P>
<P><FONT size=2>> SET LIST SQL</FONT> <BR><FONT size=2>> SQL DECLARE
EMPLIST CURSOR FOR &</FONT> <BR><FONT size=2>> SELECT EMPLOYEE,
FIRST_NAME, LAST_NAME, &</FONT> <BR><FONT size=2>>
EMPLOYEES.BRANCH,BRANCH_NAME &</FONT> <BR><FONT size=2>> FROM
EMPLOYEES, BRANCHES</FONT> <BR><FONT size=2>> SCREEN EMPBRANCHC</FONT>
<BR><FONT size=2>> CURSOR EMPLIST &</FONT> <BR><FONT size=2>>
WHERE (EMPLOYEES.BRANCH = BRANCHES.BRANCH) &</FONT> <BR><FONT
size=2>> PRIMARY KEY EMPLOYEE</FONT> <BR><FONT size=2>> __ Sql after
substitutions are applied:</FONT> <BR><FONT size=2>__ SELECT EMPLOYEE,
FIRST_NAME, LAST_NAME,</FONT> <BR><FONT size=2>__ EMPLOYEES.BRANCH,
BRANCHES.BRANCH,</FONT> <BR><FONT size=2>__ BRANCH_NAME</FONT> <BR><FONT
size=2>__ FROM EMPLOYEES, BRANCHES</FONT> <BR><FONT size=2>__ where
EMPLOYEES.BRANCH = BRANCHES.BRANCH</FONT> </P>
<P><FONT size=2>Bob
Deskin
</FONT><BR><FONT size=2>PowerHouse Web Product Manager, Application
Development Tools, Cognos Inc.</FONT> <BR><FONT
size=2>bob.deskin@cognos.com (613) 738-1338 ext 7268 FAX: (613)
727-1178</FONT> <BR><FONT size=2>3755 Riverside Drive P.O. Box 9707 Stn.
T, Ottawa ON K1G 4K9 CANADA</FONT> </P>
<P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From:
John Hunter [<A
href="mailto:jthunter@nbnet.nb.ca">mailto:jthunter@nbnet.nb.ca</A>]</FONT>
<BR><FONT size=2>Sent: Thursday, March 21, 2002 2:18 PM</FONT> <BR><FONT
size=2>To: powerh-l@lists.swau.edu</FONT> <BR><FONT size=2>Subject:
Embedded SQL in Qdesign</FONT> </P><BR>
<P><FONT size=2>Hi Gang,</FONT> </P>
<P><FONT size=2>Could some kind soul please show me an example of an
embedded SQL in</FONT> <BR><FONT size=2>Qdesign? I would RTFM but,
alas, I don't have one. I think I'm close, but</FONT> <BR><FONT
size=2>am still running into problems. My platform is Powerhouse 820
on VMS.</FONT> </P>
<P><FONT size=2>Thanks,</FONT> <BR><FONT size=2>-John</FONT> </P><BR>
<P><FONT size=2>= = = = = = = = = = = = = = = = = = = = = = = = = = =
=</FONT> <BR><FONT size=2>Mailing list: powerh-l@lists.swau.edu</FONT>
<BR><FONT size=2>Subscribe: "subscribe" in message body to
powerh-l-request@lists.swau.edu</FONT> <BR><FONT size=2>Unsubscribe:
"unsubscribe" in message body to</FONT> <BR><FONT
size=2>powerh-l-request@lists.swau.edu</FONT> <BR><FONT size=2><A
href="http://lists.swau.edu/mailman/listinfo/powerh-l"
target=_blank>http://lists.swau.edu/mailman/listinfo/powerh-l</A></FONT>
<BR><FONT size=2>This list is closed, thus to post to the list you must be
a subscriber.</FONT> </P>
<P><FONT size=2>This message may contain privileged and/or confidential
information. If you</FONT> <BR><FONT size=2>have received this
e-mail in error or are not the intended recipient, you</FONT> <BR><FONT
size=2>may not use, copy, disseminate or distribute it; do not open
any</FONT> <BR><FONT size=2>attachments, delete it immediately from your
system and notify the sender</FONT> <BR><FONT size=2>promptly by e-mail
that you have done so. Thank you.</FONT> </P>
<P><FONT size=2>= = = = = = = = = = = = = = = = = = = = = = = = = = =
=</FONT> <BR><FONT size=2>Mailing list: powerh-l@lists.swau.edu</FONT>
<BR><FONT size=2>Subscribe: "subscribe" in message body to
powerh-l-request@lists.swau.edu</FONT> <BR><FONT size=2>Unsubscribe:
"unsubscribe" in message body to powerh-l-request@lists.swau.edu</FONT>
<BR><FONT size=2><A href="http://lists.swau.edu/mailman/listinfo/powerh-l"
target=_blank>http://lists.swau.edu/mailman/listinfo/powerh-l</A></FONT>
<BR><FONT size=2>This list is closed, thus to post to the list you must be
a subscriber.</FONT> </P>
<P><FONT size=2>This message may contain privileged and/or confidential
information. If you have received this e-mail in error or are not
the intended recipient, you may not use, copy, disseminate or distribute
it; do not open any attachments, delete it immediately from your system
and notify the sender promptly by e-mail that you have done so.
Thank you.</FONT></P></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>
<BR>
<P><FONT SIZE=2 FACE="Arial">This message may contain privileged and/or confidential information. If you have received this e-mail in error or are not the intended recipient, you may not use, copy, disseminate or distribute it; do not open any attachments, delete it immediately from your system and notify the sender promptly by e-mail that you have done so. Thank you.</FONT></P>
------_=_NextPart_001_01C1D1CA.B6A8CF80--