Embedded SQL in Design
Edis, Bob
BEdis@usbnc.org
Fri, 22 Mar 2002 11:42:14 -0600
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_01C1D1C8.E6E694E0
Content-Type: text/plain;
charset="iso-8859-1"
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.
------_=_NextPart_001_01C1D1C8.E6E694E0
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.50.4807.2300" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=283083717-22032002>G'day
Lorry</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=283083717-22032002></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff 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 face=Arial color=#0000ff size=2><SPAN
class=283083717-22032002></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=283083717-22032002>Regards,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=283083717-22032002>Blue</SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><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 face=Arial color=#0000ff size=2><SPAN
class=536323017-22032002>Hi,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=536323017-22032002></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff 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 face=Arial color=#0000ff 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 face=Arial color=#0000ff size=2><SPAN
class=536323017-22032002></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=536323017-22032002>Thanx</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=536323017-22032002> Lorry
Litman</SPAN></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><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
target=_blank
href="http://lists.swau.edu/mailman/listinfo/powerh-l">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 target=_blank
href="http://lists.swau.edu/mailman/listinfo/powerh-l">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></BODY></HTML>
------_=_NextPart_001_01C1D1C8.E6E694E0--