%ResultSet and %DynamicQuery:SQL

[Micky Hulse]

Hello,

Sorry if noob question, but what situations should I use Dynamic SQL vs. Embedded SQL(?)?

For example, when working with our content management system, I see this a lot:

set rs = ##class(%ResultSet).%New("%DynamicQuery:SQL")

... but I also see this:

set rs = ##class(%ResultSet).%New()

When is one better than the other? Any rule of thumb for knowing when you need one over the other? The docs clearly state what the difference is:

http://docs.intersystems.com/cache20101/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_dynsql#GSQL_dynsql_intro

... but I was hoping someone could give a simple example/situation when I would use Dynamic SQL or Embedded SQL (I assume Embedded SQL is the opposite of Dynamic SQL?).

Sorry if silly question.

Thanks!
Micky

[Roberto]

Dynamic SQL is sql that you generate during run time, it is created in
your code through some resultset. Both your examples are dynamic sql.

You generally have a Prepare and Execute statement to get your dynamic
results like the following:
Set rs=##class(%ResultSet).%New() ; I'm using the %ResultSet class,
Intersystems recommend using the %SQL.Statement which is faster
Set sql="SELECT * FROM MyTable"
Set status=rs.Prepare(sql)
If (status=1) {
Set status=rs.Execute()
If (status=1) {
While (rs.Next()) {
//process your resultset here:
Set var1=rs.Data("ColName")
}
}
}

Embedded SQL is SQL that you define using &sql. The sql is already
compiled so it is optimized. I have used it to get single values using
the INTO clause. For example:

Write "Testing...",!
Set newName=""
&sql(SELECT Name Into :newName FROM MyAddressBook WHERE ID=12345)
Write "Name = " _ newName,!

Embedded SQL is usually used for simple sql statements.

Read up the docs for both. There's plenty of material in the docs.

-Roberto

On Mar 30, 4:22 pm, Micky Hulse <rgmi...@gmail.com> wrote:
> Hello,
>
> Sorry if noob question, but what situations should I use Dynamic SQL vs.
> Embedded SQL(?)?
>
> For example, when working with our content management system, I see this a
> lot:
>
> set rs = ##class(%ResultSet).%New("%DynamicQuery:SQL")
>
> ... but I also see this:
>
> set rs = ##class(%ResultSet).%New()
>
> When is one better than the other? Any rule of thumb for knowing when you
> need one over the other? The docs clearly state what the difference is:
>

> http://docs.intersystems.com/cache20101/csp/docbook/DocBook.UI.Page.c...

[Micky Hulse]

On Wed, Mar 30, 2011 at 1:22 PM, Micky Hulse <rgm...@gmail.com> wrote:
> ... but I was hoping someone could give a simple example/situation when I
> would use Dynamic SQL or Embedded SQL (I assume Embedded SQL is the opposite
> of Dynamic SQL?).

Ah, I see that I am confusing terminology... Embeded sql:

http://docs.intersystems.com/cache20101/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_esql

Sorry for my confusion.

[Micky Hulse]

Hi Roberto! Thank you so much for you help! I really appreciate it. :)

See my inline replies below.

On Wed, Mar 30, 2011 at 1:43 PM, Roberto <rcah...@gmail.com> wrote:
> Dynamic SQL is sql that you generate during run time, it is created in
> your code through some resultset. Both your examples are dynamic sql.

Thanks for that clarification. I had a feeling I was getting it wrong. :D

What does the "%DynamicQuery:SQL" argument do? Is there any difference
when that is specified?

> You generally have a Prepare and Execute statement to get your dynamic
> results like the following:
> Set rs=##class(%ResultSet).%New() ; I'm using the %ResultSet class,
> Intersystems recommend using the %SQL.Statement which is faster

Ah, good info! I tried using ##class(%SQL.Statement).%New() via a CSP
method, but I got this error:

Error: <CLASS DOES NOT EXIST>zrundate+1^csp.web.demos.dates.1 *%SQL.Statement

I am guessing that my syntax is bad or %SQL.Statement is not available
to use in 2009.1.5 of Caché.

I will keep experimenting. Thanks for pointing me in the right direction! :)

> ...<snip>...

> Embedded SQL is SQL that you define using &sql. The sql is already
> compiled so it is optimized. I have used it to get single values using
> the INTO clause. For example:

> ...<snip>...

> Embedded SQL is usually used for simple sql statements.

Ahhh, interesting! I think I understand the difference now. Thanks so much!

> Read up the docs for both. There's plenty of material in the docs.

Will do! Thanks for the examples and info, I greatly appreciate it. :)

Have an awesome day!

Cheers,
Micky

[Roberto]

Part 1...

You can do the following:

Set resultSet=##class(%ResultSet).%New() ;%ResultSet =
%Library.ResultSet class
Set resultSet.ClassName="db.Client" ;this is the name of my class
that I want to query
Set resultSet.QueryName="Query1" ;this is the name of the query
that I created in db.Client
Set status=resultSet.Execute() ;this will start this query
If (status=1) {
While (result.Next()) {
Set myVar=result.Data("Col1") ;this will loop through all
of the resultset and get Col1
}
}


Or you can do this (same thing):

Set resultSet=##class(%ResultSet).%New("db.Client:Query1")
Set status=resultSet.Execute()
If (status=1) {
While (resultSet.Next()) {
Set myVar=resultSet.Data("Col1")
}
}


Or you can do this (%DynamicQuery:SQL is built into Cache)

Set resultSet=##class(%ResultSet).%New("%DynamicQuery:SQL")
Set status=resultSet.Prepare("SELECT * FROM db.Client")
If (status=1) {
Set status=resultSet.Execute()
If (status=1) {
While (resultSet.Next()) {
Set myVar=resultSet.Data("Col1")
}
}
}

Or you can just omit %DynamicQuery:SQL and do this:
Set sql="SELECT * FROM db.Client"
Set resultSet=##class(%ResultSet).%New()
Set status=resultSet.Prepare(sql)
If (status=1) {
Set status=resultSet.Execute()
If (status=1) {
While (resultSet.Next()) {
Set myVar=resultSet.Data("Col1")
}
}
}


-Roberto

On Mar 30, 5:17 pm, Micky Hulse <mickyhulse.li...@gmail.com> wrote:
> Hi Roberto! Thank you so much for you help! I really appreciate it. :)
>
> See my inline replies below.
>

[Micky Hulse]

On Wed, Mar 30, 2011 at 5:14 PM, Roberto <rcah...@gmail.com> wrote:
> Part 1...
> ...<snip>...
> -Roberto

Whoa!!! You Rock!!!

Thanks for all the examples and explanations Roberto!!!! That is
great! Very helpful!

Bookmarking this thread for future reference.

I owe you one. :)

[Roberto Cahanap]

Part 2:

 

Intersystems recommends %SQL.Statement. This is suppose to be faster. Which looks like from our testing it is.

 

Example:

 

Set statement=##class(%SQL.Statement).%New()

Set sql=2                       ; the number of items in the array

Set sql(1)="SELECT * FROM ZENApp_Data.Customer"

Set sql(2)=" ORDER BY Location"

Set status=statement.%Prepare(.sql)

If (status=1) {

     Set resultSet=statement.%Execute()    ; this creates a %SQL.StatementResult object

     If (resultSet) {

           While (resultSet.%Next()) {

                Set myVar=resultSet.Location             ;Location is one of the column names in the table

          }

     }

}

OK. Good luck!

 

-Roberto

 

 

--
InterSystems: Advanced software technologies for breakthrough applications

Global Summit: March 20 - 23, 2011, Hilton Orlando Bonnet Creek

Caché 2010.2.3 released on March 1, 2010

[Micky Hulse]

On Wed, Mar 30, 2011 at 5:31 PM, Roberto Cahanap <rcah...@gmail.com> wrote:
> Part 2:
>  ...<snip>...
> OK. Good luck!

WOW! Thank you!!!

Very helpful... Hopefully one of these days I can pay you back. :)

Have a great day.

Cheers,
Micky

[Roberto]

No problem Mickey!

I wish somebody explained this to me 2 years ago when I first started.

One advice, learn to get information from the class definitions! You
will get a lot of information looking at that.

%ResultSet:
http://localhost:57772/csp/documatic/%25CSP.Documatic.cls?LIBRARY=samples&CLASSNAME=%25Library.ResultSet&CSPCHD=00000000000011m1I5zy000000rL$OBFIMl0ZHfueauUH_Fw--&CSPSHARE=1

%SQL.Statement:
http://localhost:57772/csp/documatic/%25CSP.Documatic.cls?LIBRARY=samples&CLASSNAME=%25SQL.Statement&CSPCHD=00000000000011m1I5zy000000rL$OBFIMl0ZHfueauUH_Fw--&CSPSHARE=1

-Roberto


On Mar 30, 8:36 pm, Micky Hulse <mickyhulse.li...@gmail.com> wrote: