.png)
This tutorial is a follow on to Working with %Query
It was displaying the content of the input stream chopped in fixed-size chunks.
But often those streams are structured and have well-defined separators (e.g HL7)
So as a side subject of this tutorial, this chapter shows how to break a stream into PIECES.
It is exactly the same idea as the $PIECE() function for strings with some add-ons.
The query takes these input parameters:
- idfrom = first ID to show
- idto = last ID to show - missing shos al higher IDs available
- maxtxt = maximum size of output text from the stream. Default = 25
- separator = characters as know from $Piece function
- startpiece = first piece to display
- endpiece = last piece to display - if missing only the first is displayed
- grouped = number of pieces in output text. limited by maxtxt - default = 1
- before = number of characters before the limiter from the previous piece
Implementation
The base for the Query was shown in the previous chapters.
Again the Source Object is kept local while processing the related Stream.
And as for the fixed-sized chunks also the Stream Object is kept while
processing the resulting output lines.
To find the pieces and count them method FindAt() scans the whole Stream
as a first action and builds a list of start points. It is used when the pieces
are collected into their groups by line.
The Streàm-Function used is ReadSQL(start, end). It is somewhat sensitive
and honors unprecise values with no result or ERROR. So besides locating all
pieces correctly composing precise and meaningful limits is the major challenge
for this part of the exercise.
It is all packaged in the method Piece() of the example code
To enable multiple reentrances in the Fetch method all parameters are kept in
the common JSON object of the customized Query.
Here are some examples
- Record #3 was deleted
- Record#4 has no stream
- PieceCnt shows the total pieces in the first row and number of pieces following
- PieceId shows the number of the first Piece displayed
Some variations to the previous Tutorials:
- the common variable qHandle that hat holds all requirements changed its name to qj to demonstrate independence from docu and for less typing.
- the layout of the output in ROWSPEC is changed to also show the number of the starting piece, the total number of pieces in the first row, and the number of pieces in the following rows.
The first example shows objects 1..5 with max 33 chars
separated by || pieces 2..4 grouped by 2 with 3 previous characters
[SQL]USER>>call rcc.Q3(1,5,33,'||',2,4,2,3)
7. call rcc.Q3(1,5,33,'||',2,4,2,3)
Dumping result #1
ID City Name Age pcCnt PieceId PieceTxt
1 Bensonh Kovalev 16 52 2 ty.||Scope ||An AmAn LoIcA On
_ _ _ _ 2 4 On||Iso
2 Queensb Yeats 15 52 2 00.||LoI||IesMorphSy
_ _ _ _ 2 4 hSy||Re Uni
4 Newton Evans 61 0 2
5 Hialeah Zemaiti 47 52 2 ks.||TwoToLy Wa||LoGraphIc Theti
_ _ _ _ 2 4 eti||Ism LoToTri AticViaOpOp
7 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0064s/2159/18029/0ms
execute time(s)/globals/lines/disk: 0.0060s/186/21480/0ms
cached query class: %sqlcq.USER.cls86
---------------------------------------------------------------------------
Next example:
All records from 5 to end, maximum 33 characters, separated with || pieces 50 ..55 and 1 by line
USER>>call rcc.Q3(5,,33,'||',50,55,1)
9. call rcc.Q3(5,,33,'||',50,55,1)
Dumping result #1
ID City Name Age pcCnt PieceId PieceTxt
5 Hialeah Zemaiti 47 52 50 ||LoOp Am TriAmGeo LookMuch ReAble
_ _ _ _ 1 51 ||AnIAn I GeoIc Ly Copter A IcA
_ _ _ _ 1 52 ||***Done***
6 Elmhurs Jenkins 29 52 50 ||Re PhotoTech Ies
_ _ _ _ 1 51 ||A
_ _ _ _ 1 52 ||***Done***
7 Islip Drabek 61 52 50 ||Thetic Status Cycle AmAm To Tech
_ _ _ _ 1 51 ||Ly SoundCo CoCo AmCo Am ToRePus
_ _ _ _ 1 52 ||***Done***
8 Islip Kovalev 88 52 50 ||IcGeoType PhysicalLyIsm Ies To T
_ _ _ _ 1 51 ||ComI An GeoRange On AnOnTo Two P
_ _ _ _ 1 52 ||***Done***
12 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0065s/2158/18091/0ms
execute time(s)/globals/lines/disk: 0.0082s/245/28753/0ms
cached query class: %sqlcq.USER.cls103
---------------------------------------------------------------------------
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.
.png)