This function accepts an Rlist statement and returns the results of the statement in a specified format. It is similar to calling RLIST with a target of TARGET_VARIABLE$
myVariable = Select_Into( commands, format)
The function has the following parameters:
| Parameter | Description |
|---|---|
| commands | A single LIST statement or a series of SELECT statements terminating in a LIST statement. The series may be delimited by an @fm, an @vm or a semi-colon (;). |
| format | The format of the returned result. See the table below for details formats and associated return values. |
The returned results are dependent on the format parameter passed to the function.
| Format | Returned Data |
|---|---|
| CSV | Returns the data as comma-delimited columns and carriage return / line-feed delimited rows. |
| EDT | Returns an @fm-delimited list of rows and @vm-delimited list of columns, convenient for updating edit tables using their "LIST" property. |
| HTML | Returns the data in HTML format. |
| TAB | Returns the data as tab-delimited columns and carriage return / line-feed delimited rows. |
| JSON | Returns the data as a JSON string, useful for feeding AJAX grids. |
| XML | Returns XML useful for feeding a web service. |
| ADO | Returns an <idispatch> disconnected recordset object. |
| MV | Returns an @rm-delimited list of rows and @fm-delimited list of columns, with column headings. Useful for developers who want to do additional processing. |
| TXT | Same as TAB. |
| KEYS | Returns the result of a SELECT as an FM delimited list of record keys. A LIST statement returns nothing |
This function is available in OpenInsight 9.0.1 and above. It uses the RLIST callback feature. Rlist will truncate text that is too wide to fit in a column. You may need to use the JUSTLEN keyword to avoid truncating columns.
/*
Example 1
A simple example of retrieving data via Select_Into() and
placing the results into an Edit table.
*/
edit_table = @window : ".TABLE_1"
vStmt = "LIST CUSTOMERS CUSTOMER_NAME CITY STATE WITH STATE = 'NJ' "
results = Select_Into( vStmt, "EDT" )
temp = Set_Property( edit_table, "LIST", results )
/********************************************************************
When this is run from the EXAMPLES application the results
in the edit table are:
6 Al Timiter Highlands NJ
15 Benjamin Dover Little Falls NJ
26 Karen Sympathy Crymia River NJ
2 Lee Tremble Westwood NJ
1 Mike Ruane Westwood NJ
4 Barry Truman Westwood NJ
*********************************************************************/
/*
Example 2
A complex example of selecting and retrieving data.
The results will be returned in CSV format. Write to disk as a csv file, should open in Excel automatically.
*/
vStmt = "SELECT CUSTOMERS WITH STATE = 'NJ' " : @fm
vStmt := "SELECT CUSTOMERS BY FNAME" : @fm
vStmt := "LIST CUSTOMERS CUSTOMER_NAME CITY STATE"
results = Select_Into( vStmt, "CSV" )
filename = "c:\temp\test.csv"
OsWrite results on filename
Call shellexecute(0,'Open':\\00\, filename:\00\,'','',1)
/********************************************************************
When this is run from the EXAMPLES application the results variable
should contain:
"AT_ID","CUSTOMER_NAME","CITY","STATE"
"6","Al Timiter","Highlands","NJ"
"4","Barry Truman","Westwood","NJ"
"15","Benjamin Dover","Little Falls","NJ"
"26","Karen Sympathy","Crymia River","NJ"
"2","Lee Tremble","Westwood","NJ"
"1","Mike Ruane","Westwood","NJ"
********************************************************************/
Sample Output for each format
-------
CSV
-------
"@ID","CUSTOMER_NAME","INV_NOS","CITY","STATE"
"9922","ARTURO PALM","","UNION","NJ"
"7424","BEN HASSETT","","LAFAYETTE","NJ"
"4612","BERNADETTE HULBERT","","PARAMUS","NJ"
"8220","CARLTON MEADORS","","VERNON","NJ"
"7518","CELINA SOLIZ","34","CEDAR BROOK","NJ"
"6270","CLARE MCNAMEE","","LAMBERTVILLE","NJ"
"10772","ESMERALDA CHRONISTER","","ESSEX FELLS","NJ"
"9830","EVANGELINA TRAPP","","LAFAYETTE","NJ"
"8170","HEATH STIVERS","","ELWOOD","NJ"
"8772","LOLA GRIER","","WESTWOOD","NJ"
"10418","MAYNARD GROTH","","BLOOMSBURY","NJ"
-------
TAB
-------
@ID CUSTOMER_NAME INV_NOS CITY STATE
9922 ARTURO PALM UNION NJ
7424 BEN HASSETT LAFAYETTE NJ
4612 BERNADETTE HULBERT PARAMUS NJ
8220 CARLTON MEADORS VERNON NJ
7518 CELINA SOLIZ 34 CEDAR BROOK NJ
6270 CLARE MCNAMEE LAMBERTVILLE NJ
10772 ESMERALDA CHRONISTER ESSEX FELLS NJ
9830 EVANGELINA TRAPP LAFAYETTE NJ
8170 HEATH STIVERS ELWOOD NJ
8772 LOLA GRIER WESTWOOD NJ
10418 MAYNARD GROTH BLOOMSBURY NJ
-------
EDT
-------
9922ýARTURO PALMýýUNIONýNJþ7424ýBEN HASSETTýýLAFAYETTEýNJþ4612ýBERNADETTE HULBERTýýPARAMUSýNJþ8220ýCARLTON MEADORSýýVERNONýNJþ7518ýCELINA SOLIZý34ýCEDAR BROOKýNJþ6270ýCLARE MCNAMEEýýLAMBERTVILLEýNJþ10772ýESMERALDA CHRONISTERýýESSEX FELLSýNJþ9830ýEVANGELINA TRAPPýýLAFAYETTEýNJþ8170ýHEATH STIVERSýýELWOODýNJþ8772ýLOLA GRIERýýWESTWOODýNJþ10418ýMAYNARD GROTHýýBLOOMSBURYýNJ
-------
JSON
-------
[{"AT_ID":"9922","CUSTOMER_NAME":"ARTURO PALM","INV_NOS":"","CITY":"UNION","STATE":"NJ"},{"AT_ID":"7424","CUSTOMER_NAME":"BEN HASSETT","INV_NOS":"","CITY":"LAFAYETTE","STATE":"NJ"},{"AT_ID":"4612","CUSTOMER_NAME":"BERNADETTE HULBERT","INV_NOS":"","CITY":"PARAMUS","STATE":"NJ"},{"AT_ID":"8220","CUSTOMER_NAME":"CARLTON MEADORS","INV_NOS":"","CITY":"VERNON","STATE":"NJ"},{"AT_ID":"7518","CUSTOMER_NAME":"CELINA SOLIZ","INV_NOS":"34","CITY":"CEDAR BROOK","STATE":"NJ"},{"AT_ID":"6270","CUSTOMER_NAME":"CLARE MCNAMEE","INV_NOS":"","CITY":"LAMBERTVILLE","STATE":"NJ"},{"AT_ID":"10772","CUSTOMER_NAME":"ESMERALDA CHRONISTER","INV_NOS":"","CITY":"ESSEX FELLS","STATE":"NJ"},{"AT_ID":"9830","CUSTOMER_NAME":"EVANGELINA TRAPP","INV_NOS":"","CITY":"LAFAYETTE","STATE":"NJ"},{"AT_ID":"8170","CUSTOMER_NAME":"HEATH STIVERS","INV_NOS":"","CITY":"ELWOOD","STATE":"NJ"},{"AT_ID":"8772","CUSTOMER_NAME":"LOLA GRIER","INV_NOS":"","CITY":"WESTWOOD","STATE":"NJ"},{"AT_ID":"10418","CUSTOMER_NAME":"MAYNARD GROTH","INV_NOS":"","CITY":"BLOOMSBURY","STATE":"NJ"}]
-------
XML
-------
<?xml version="1.0" encoding="UTF-8"?>
<rows>
<row>
<AT_ID>9922</AT_ID>
<CUSTOMER_NAME>ARTURO PALM</CUSTOMER_NAME>
<INV_NOS><CITY>UNION </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>7424</AT_ID>
<CUSTOMER_NAME>BEN HASSETT</CUSTOMER_NAME>
<INV_NOS><CITY>LAFAYETTE </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>4612</AT_ID>
<CUSTOMER_NAME>BERNADETTE HULBERT</CUSTOMER_NAME>
<INV_NOS><CITY>PARAMUS </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>8220</AT_ID>
<CUSTOMER_NAME>CARLTON MEADORS</CUSTOMER_NAME>
<INV_NOS><CITY>VERNON </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>7518</AT_ID>
<CUSTOMER_NAME>CELINA SOLIZ</CUSTOMER_NAME>
<INV_NOS>34</INV_NOS>
<CITY>CEDAR BROOK</CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>6270</AT_ID>
<CUSTOMER_NAME>CLARE MCNAMEE</CUSTOMER_NAME>
<INV_NOS><CITY>LAMBERTVILLE </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>10772</AT_ID>
<CUSTOMER_NAME>ESMERALDA CHRONISTER</CUSTOMER_NAME>
<INV_NOS><CITY>ESSEX FELLS </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>9830</AT_ID>
<CUSTOMER_NAME>EVANGELINA TRAPP</CUSTOMER_NAME>
<INV_NOS><CITY>LAFAYETTE </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>8170</AT_ID>
<CUSTOMER_NAME>HEATH STIVERS</CUSTOMER_NAME>
<INV_NOS><CITY>ELWOOD </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>8772</AT_ID>
<CUSTOMER_NAME>LOLA GRIER</CUSTOMER_NAME>
<INV_NOS><CITY>WESTWOOD </CITY>
<STATE>NJ</STATE>
</row>
<row>
<AT_ID>10418</AT_ID>
<CUSTOMER_NAME>MAYNARD GROTH</CUSTOMER_NAME>
<INV_NOS><CITY>BLOOMSBURY </CITY>
<STATE>NJ</STATE>
</row>
</rows>
-------
ADO
-------
ADO Recordcount = 11
-------
MV
-------
@IDþCUSTOMER_NAMEþINV_NOSþCITYþSTATEÿ9922þARTURO PALMþþUNIONþNJÿ7424þBEN HASSETTþþLAFAYETTEþNJÿ4612þBERNADETTE HULBERTþþPARAMUSþNJÿ8220þCARLTON MEADORSþþVERNONþNJÿ7518þCELINA SOLIZþ34þCEDAR BROOKþNJÿ6270þCLARE MCNAMEEþþLAMBERTVILLEþNJÿ10772þESMERALDA CHRONISTERþþESSEX FELLSþNJÿ9830þEVANGELINA TRAPPþþLAFAYETTEþNJÿ8170þHEATH STIVERSþþELWOODþNJÿ8772þLOLA GRIERþþWESTWOODþNJÿ10418þMAYNARD GROTHþþBLOOMSBURYþNJ
-------
TXT
-------
@ID CUSTOMER_NAME INV_NOS CITY STATE
9922 ARTURO PALM UNION NJ
7424 BEN HASSETT LAFAYETTE NJ
4612 BERNADETTE HULBERT PARAMUS NJ
8220 CARLTON MEADORS VERNON NJ
7518 CELINA SOLIZ 34 CEDAR BROOK NJ
6270 CLARE MCNAMEE LAMBERTVILLE NJ
10772 ESMERALDA CHRONISTER ESSEX FELLS NJ
9830 EVANGELINA TRAPP LAFAYETTE NJ
8170 HEATH STIVERS ELWOOD NJ
8772 LOLA GRIER WESTWOOD NJ
10418 MAYNARD GROTH BLOOMSBURY NJ
-------
KEYS
------
* Pass a series of select statements, SELECT_INTO will return an @FM delimited list of keys
Declare Function select_into
keys = select_into( 'SELECT PERSON WITH CITY EQ "BOSTON"', 'KEYS')