![](/sites/default/files/inline/images/images/image(5723).png)![](/sites/default/files/inline/images/25q-logo1.jpg) My previous article introduced you to SQL based **Basic Class Query** where a clever wizard created all the required code for you and your essential contribution was an SQL statement. Now we enter the real **Custom Class Query** that** **provides more freedom but requires a deeper understanding of the mechanic behind the scene. The full code example is again on [**GitHub**](https://github.com/rcemper/Tutorial-QUERY) Some things haven't changed: * demo data are the same * consumption of the query is also unchanged * all handling of ODBC / JDBC protocol is still generated. So what is different? * You need a header **QUERY** statement to declare your input parameters but * also the record layout for your output. ROWSPEC * you have to provide an **Execute** method to initialize your query and consume your input parameters * a **Close** method to clean up your environment * and a **Fetch** method that does the Job. * it is called row by row until you set AtEnd=1. (it is passed by reference) * and you return variable Row (also passed by reference) as $LB() structure * so it is obvious that the resulting Row is shorter than MAXSTRING * and this is our challenge to present your stream so we take our choice: as before we use the Studio's Query Wizard ![](/sites/default/files/inline/images/images/image(5659).png) we have 3 parameters: idfrom (first ID), isto (last ID), maxtxt (maxim text from stream) ![](/sites/default/files/inline/images/images/image(5660).png) and **new** the layout of our output (ROWSPEC), similar to above ![](/sites/default/files/inline/images/images/image(5662).png) for our Stream, we need to overwrite %String defaults to match ODBC / JDBC The type needs to be `%String(EXTERNALSQLTYPE = "LONGVARCHAR", MAXLEN = "")` This is the generated code framework:
Query Q1(
idfrom <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">1</span>,
idto <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">0</span>,
maxtxt <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">25</span>) <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Query</span>
(ROWSPEC = <span class="hljs-string">"ID:%Integer,City:%String,
Name:%String,Age:%Integer,
Stream:%String(EXTERNALSQLTYPE=""LONGVARCHAR"", MAXLEN = """")"</span>)
{
}
<span class="hljs-keyword">ClassMethod</span> Q1Execute(
ByRef qHandle <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Binary</span>,
idfrom <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">1</span>,
idto <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">0</span>,
maxtxt <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">25</span>) <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Status</span>
{
<span class="hljs-keyword">Quit</span> <span class="hljs-built_in">$$$OK</span>
}
<span class="hljs-keyword">ClassMethod</span> Q1Close(ByRef qHandle <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Binary</span>) <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Status</span> [ PlaceAfter = Q1Execute ]
{
<span class="hljs-keyword">Quit</span> <span class="hljs-built_in">$$$OK</span>
}
<span class="hljs-keyword">ClassMethod</span> Q1Fetch(
ByRef qHandle <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Binary</span>,
ByRef Row <span class="hljs-keyword">As</span> <span class="hljs-built_in">%List</span>,
ByRef AtEnd <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">0</span>) <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Status</span> [ PlaceAfter = Q1Execute ]
{
<span class="hljs-keyword">Quit</span> <span class="hljs-built_in">$$$OK</span>
}
we still need to add `CONTAINID=1` and <font color="#000000">[ </font><font color="#000080">SqlName </font><font color="#000000">= </font><font color="#008000">Q1</font><font color="#000000">, </font><font color="#000080">SqlProc </font><font color="#000000">]</font>
</p>
<span class="hljs-keyword">ClassMethod</span> Q1Execute(
ByRef qHandle <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Binary</span>,
idfrom <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">1</span>,
idto <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">0</span>,
maxtxt <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">25</span>) <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Status</span>
{
<span class="hljs-keyword">set</span> qHandle={}
<span class="hljs-keyword">set</span> qHandle.id=<span class="hljs-number">0</span>
<span class="hljs-keyword">set</span> qHandle.idfrom=idfrom
<span class="hljs-keyword">set</span> qHandle.idto=idto
<span class="hljs-keyword">set</span> qHandle.obj=<span class="hljs-number">0</span>
<span class="hljs-keyword">set</span> qHandle.stream=<span class="hljs-number">0</span>
<span class="hljs-keyword">set</span> qHandle.maxtxt=maxtxt
<span class="hljs-keyword">Quit</span> <span class="hljs-built_in">$$$OK</span>
}
<span class="hljs-comment">/// that's where the music plays</span>
<span class="hljs-comment">/// called for evey row delivered</span>
<span class="hljs-keyword">ClassMethod</span> Q1Fetch(
ByRef qHandle <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Binary</span>,
ByRef Row <span class="hljs-keyword">As</span> <span class="hljs-built_in">%List</span>,
ByRef AtEnd <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Integer</span> = <span class="hljs-number">0</span>) <span class="hljs-keyword">As</span> <span class="hljs-built_in">%Status</span> [ PlaceAfter = Q1Execute ]
{
<span class="hljs-comment">/// first access</span>
<span class="hljs-keyword">if</span> qHandle.id<qHandle.idfrom <span class="hljs-keyword">set</span> qHandle.id=qHandle.idfrom
<span class="hljs-comment">///</span>
nextrec
<span class="hljs-keyword">if</span> qHandle.idto,qHandle.idto<qHandle.id <span class="hljs-keyword">set</span> AtEnd=<span class="hljs-number">1</span>
<span class="hljs-keyword">if</span> qHandle.id><span class="hljs-symbol">^rcc</span>.TUD <span class="hljs-keyword">set</span> AtEnd=<span class="hljs-number">1</span>
<span class="hljs-keyword">if</span> AtEnd <span class="hljs-keyword">quit</span> <span class="hljs-built_in">$$$OK</span>
<span class="hljs-keyword">if</span> 'qHandle.obj {
<span class="hljs-keyword">set</span> obj=<span class="hljs-keyword">##class</span>(rcc.TU).<span class="hljs-built_in">%OpenId</span>(qHandle.id)
,qHandle.obj=obj
,qHandle.stream=<span class="hljs-number">0</span>
}
<span class="hljs-keyword">if</span> 'obj <span class="hljs-keyword">set</span> qHandle.id=qHandle.id+<span class="hljs-number">1</span> <span class="hljs-keyword">goto</span> nextrec
<span class="hljs-keyword">if</span> 'qHandle.stream <span class="hljs-keyword">set</span> qHandle.stream=qHandle.obj.Stream
<span class="hljs-keyword">set</span> text=qHandle.stream.<span class="hljs-keyword">Read</span>(qHandle.maxtxt)
<span class="hljs-keyword">set</span> Row=<span class="hljs-built_in">$lb</span>(qHandle.id,qHandle.obj.City,qHandle.obj.Name,qHandle.obj.Age,text)
<span class="hljs-comment">/// row completed</span>
<span class="hljs-keyword">set</span> qHandle.id=qHandle.id+<span class="hljs-number">1</span>
<span class="hljs-keyword">set</span> qHandle.stream=<span class="hljs-number">0</span>
<span class="hljs-keyword">set</span> qHandle.obj=<span class="hljs-number">0</span>
<span class="hljs-keyword">Quit</span> <span class="hljs-built_in">$$$OK</span>
}
[SQL]USER>>call rcc.Q1(<span class="hljs-number">4</span>,<span class="hljs-number">7</span>)
<span class="hljs-number">8.</span> call rcc.Q1(<span class="hljs-number">4</span>,<span class="hljs-number">7</span>)
Dumping result #<span class="hljs-number">1</span>
ID City Name Age Stream
<span class="hljs-number">4</span> Newton Evans <span class="hljs-number">61</span>
<span class="hljs-number">5</span> Hialeah Zemaiti <span class="hljs-number">47</span> Resellers of premise-base
<span class="hljs-number">6</span> Elmhurs Jenkins <span class="hljs-number">29</span> Enabling individuals and
<span class="hljs-number">7</span> Islip Drabek <span class="hljs-number">61</span> Building shareholder valu
<span class="hljs-number">4</span> Rows(<span class="hljs-keyword">s</span>) Affected
statement prepare time(<span class="hljs-keyword">s</span>)/globals/lines/disk: <span class="hljs-number">0.0003</span><span class="hljs-keyword">s</span>/<span class="hljs-number">11</span>/<span class="hljs-number">685</span>/<span class="hljs-number">0</span>ms
execute time(<span class="hljs-keyword">s</span>)/globals/lines/disk: <span class="hljs-number">0.0010</span><span class="hljs-keyword">s</span>/<span class="hljs-number">18</span>/<span class="hljs-number">2156</span>/<span class="hljs-number">0</span>ms
cached query <span class="hljs-keyword">class</span>: <span class="hljs-built_in">%sqlcq.USER.cls</span>59
---------------------------------------------------------------------------
Getting just the beginning of our stream is not always sufficient.
Follow me on to the next chapter for the extension of this example that
will show and control more result lines.
Just a reminder:
All test data are generated using the System method %Populate
So your output will be different. I suggest you run our tests also with other parameters
than the shown examples to get the full power of this tool.
The full code example is again available on GitHub
The full code example is again available on GitHub
The Video is available now. See section Custom Class Query
For immediate access, you can use Demo Server WebTerminal
and the related system Management Portal on Demo Server SMP
I hope you liked it so far and I can count on your votes.
</body></html>