Home | About SQeeL | Document | DownLoad | Mail Japanese
Home > Document > SQeeL Reference

Pseudo database function

  • Built-in function
    • array()
    Description
    Pseudo function
    Each element in the virtual array can access by using colname like a table.
    Each array is considered as column, and each element is considered as row.
    Syntax
    ret = array(colname,arr,colname, arr, { … })
    Parameters
    colname:column name
    arr:array
    colname, arr, { … }:
    Return Value
    1:Success
    System Return Value
    ERROR
    0Success
    Example
    //[result example]
//
// 01/01/2004 Thursday is New Year's Day
// 02/14/2004 Saturday is St. Valentain's Day
// 04/01/2004 Thursday is April Fool's Day
// 10/31/2004 Sunday   is Helloween
// 12/25/2004 Saturday is Christmas
//

run()
exit

function run()
	local data1 = { '01/01/2004', '02/14/2004', '04/01/2004', '10/31/2004', '12/25/2004' }
	local data2 = { 'Thursday', 'Saturday', 'Thursday', 'Sunday  ', 'Saturday' }
	local data3 = { 'New Year\'s Day', 'St. Valentain\'s Day', 'April Fool\'s Day', 'Helloween', 'Christmas' }
	local tab,i

	xsql select val1, val2, val3 from array('val1', data1, 'val2', data2, 'val3', data3);
	tab = xfetch(0)
	xclose()

	loop i=0;i<count(tab); i++
		print('${tab[i]["val1"]} ${tab[i]["val2"]} is ${tab[i]["val3"]}', stdout)
	endloop

endfunction
    Topへ
    directory()
    Description
    Pseudo function
    Directory information can be accessed by considering as a virtual table
    File is considered as row, and name, size, data updated, time updated, and attribute of the file are considered as column. Column name is NAME, SIZE, DATE, TIME, and ATTR respectively.
    The DATE is returned as a string with the format YYYYMMDD
    The TIME is returned as a string with the format HHMMSS
    In the ATTR column, it returns the letter 'd' indicating a directory, or returns string with length 0
    Syntax
    ret = directory(dirname)
    Parameters
    dirname:directory path
    Return Value
    0:Success
    10:Argument error
    System Return Value
    ERROR
    0Success
    10Argument error
    Example
    //[result example]
//
// c:testdir/. : file size is 0, last modified time is 20031120
// c:testdir/.. : file size is 0, last modified time is 20031120
// c:testdir/sample1.txt : file size is 410, last modified time is 20031101
// c:testdir/sample2.txt : file size is 1230, last modified time is 20031108
// c:testdir/sample3.txt : file size is 37720, last modified time is 20031115
//

run()
exit

function run()
	local dirname = 'c:testdir'
	local tab
	local i

	xsql select NAME, SIZE, DATE from directory('${dirname}', 'NAME', 'SIZE', 'DATE');
	tab = xfetch(0)
	xclose()

	loop i=0;i<count(tab); i++
		print('${dirname}/${tab[i]["NAME"]} : file size is ${tab[i]["SIZE"]}, last modified time is ${tab[i]["DATE"]}', stdout)
	endloop

endfunction
    Topへ
    fileformat()
    Description
    Sets the format of the data read from a text file by the fileread()
    If the LEN is set to the number, it returns the number specified by LEN of the string with column name specified by COLNAME.
    If the LEN is set to the the delimiter, the fileread() function accepts string charcters between delimiters as one field.
    If the pair of LEN and COLNAME is specified sequentially, it delimits the string and makes multiple fields.
    You can specify the first row to start reading by setting the START. If not specified, the START is set to 0.
    You can specify the last row to finish reading by setting the END. If not specified, the END is set to 0.
    Syntax
    ret = fileformat(start,end,colname,len,colname, len, { … })
    Parameters
    start:Start line
    end:End line
    colname:Array name (character string)
    len:Number of characters or delimiters
    colname, len, { … }:
    Return Value
    0:Error
    1:Success
    System Return Value
    ERROR
    0Success
    1Argument error
    Example
    //[result example]
//
// 01/01/2004 Thursday is New Year's Day
// 02/14/2004 Saturday is St. Valentain's Day
// 04/01/2004 Thursday is April Fool's Day
// 10/31/2004 Sunday   is Helloween
// 12/25/2004 Saturday is Christmas
//

run()
exit

function run()
	local fileName = 'c:testdata01.txt'
	local tab
	local i

	fileformat(0, 0, 'C1', ',', 'C2', ',', 'C3')
	xsql select C1, C2, C3 from fileread('${fileName}');
	tab = xfetch(0)
	xclose()

	loop i=0;i<count(tab); i++
		print('${tab[i]["C1"]} ${tab[i]["C2"]} is ${tab[i]["C3"]}', stdout)
	endloop

endfunction

/*
[testdata01.txt]
01/01/2004,Thursday,New Year's Day
02/14/2004,Saturday,St. Valentain's Day
04/01/2004,Thursday,April Fool's Day
10/31/2004,Sunday  ,Helloween
12/25/2004,Saturday,Christmas
*/
    Topへ
    fileread()
    Description
    Pseudo function
    File contents can be accessed by considering as a virtual table.
    A single row in a file is considered as a row. This row is split and each piece is considered as column.
    Column name is specified by colname.
    If the LEN is set to the integer, it reads the character string as a column name specified in colname.
    If the LEN is set to the delimiter, it reads the character strings up to the first delimiter as a single line.
    If the LEN is set to the number, it returns the number specified by LEN of the string with column name specified by COLNAME.
    If the LEN is set to the the delimiter, the fileread() function accepts string charcters between delimiters as one field.
    Syntax
    ret = fileread(filename,colname,len,colname1, len1, { … })
    Parameters
    filename:
    colname:
    len:
    colname1, len1, { … }:
    Return Value
    0:Success
    11:Argument error
    12:File open error
    System Return Value
    ERROR
    0Success
    11Argument error
    12File open error
    Example
    //[result example]
//
// 01/01/2004 Thursday is New Year's Day
// 02/14/2004 Saturday is St. Valentain's Day
// 04/01/2004 Thursday is April Fool's Day
// 10/31/2004 Sunday   is Helloween
// 12/25/2004 Saturday is Christmas
//

run()
exit

function run()
	local fileName = 'c:testdata01.txt'
	local tab
	local i

	xsql select C1, C2, C3 from fileread('${fileName}', 'C1', ',', 'C2', ',', 'C3');
	tab = xfetch(0)
	xclose()

	loop i=0;i<count(tab); i++
		print('${tab[i]["C1"]} ${tab[i]["C2"]} is ${tab[i]["C3"]}', stdout)
	endloop

endfunction

/*
[testdata01.txt]
01/01/2004,Thursday,New Year's Day     
02/14/2004,Saturday,St. Valentain's Day
04/01/2004,Thursday,April Fool's Day   
10/31/2004,Sunday  ,Helloween          
12/25/2004,Saturday,Christmas          
*/
    Topへ
    xclose()
    Description
    Frees resources used for xsql statement
    Syntax
    ret = xclose()
    Return Value
    1:Success
    System Return Value
    ERROR
    0Success
    Example
    //[result example]
//
// Jan has 31 days
// Feb has 29 days
// Mar has 31 days
// Apr has 30 days
// May has 31 days
// Jun has 30 days
// Jul has 31 days
// Aug has 31 days
// Sep has 30 days
// Oct has 31 days
// Nov has 30 days
// Dec has 31 days
//

run()
exit

function run()
	local data1 = { ' 1', ' 2', ' 3', ' 4', ' 5', ' 6', ' 7', ' 8' ,' 9', '10', '11', '12' }
	local data2 = { 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' }
	local data3 = { '31', '29', '31', '30', '31', '30', '31', '31', '30', '31', '30', '31' }
	local i, m_tab

	xsql select m_num, m_name, m_days from array('m_num', data1, 'm_name', data2, 'm_days', data3);
	m_tab = xfetch(0)
	xclose()

	loop i=0; i<count(m_tab); i++
		print('${m_tab[i]["m_name"]} has ${m_tab[i]["m_days"]} days', stdout)
	endloop

endfunction
    Topへ
    xfetch()
    Description
    Fetches a specified number of rows from the result of XSQL statement
    By calling xfetch() sequentially, you can aquire from the rows after the rows previously fetched.
    If the result rows of the function xfetch() are smaller than the FETCHSIZE, it returns the smaller result rows, instead of the FETCHSIZE
    If the FETCHSIZE is set to 0, it returns all data.
    Note that it may consume so much system resources if the result rows are too much data.
    The xfetch() returns a result as an array
    If the DIRECTION is set to 0, or not specified, it returns one row of data specified in each element.
    One row of data also stores the data in associative array. It stores column data in each element of array.
    If the DIRECTION is set to 1, it returns the result in associative array. It also stores the specified field data in each element of the array.
    To access the data, it uses the field names as keys.
    Syntax
    ret = xfetch(fetchsize,direction)
    Parameters
    fetchsize:Number of rows to fetch
    direction:Method of storing the return value
    Return Value
    0:Error
    1:Success
    System Return Value
    ERROR
    0Success
    1Argument error
    Example
    //[result example]
//
// Jan has 31 days
// Feb has 29 days
// Mar has 31 days
// Apr has 30 days
// May has 31 days
// Jun has 30 days
// Jul has 31 days
// Aug has 31 days
// Sep has 30 days
// Oct has 31 days
// Nov has 30 days
// Dec has 31 days
//

run()
exit

function run()
	local data1 = { ' 1', ' 2', ' 3', ' 4', ' 5', ' 6', ' 7', ' 8' ,' 9', '10', '11', '12' }
	local data2 = { 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' }
	local data3 = { '31', '29', '31', '30', '31', '30', '31', '31', '30', '31', '30', '31' }
	local i, m_tab

	xsql select m_num, m_name, m_days from array('m_num', data1, 'm_name', data2, 'm_days', data3);
	m_tab = xfetch(0)
	xclose()

	loop i=0; i<count(m_tab); i++
		print('${m_tab[i]["m_name"]} has ${m_tab[i]["m_days"]} days', stdout)
	endloop

endfunction
    Topへ
    xfposition()
    Description
    Gets a current position in the text file read by the fileread()
    The return value can be used as argument of the xfseek()
    Syntax
    ret = xfposition()
    Return Value
    0 or more:Location in a file
    -1:Error
    System Return Value
    ERROR
    0Success
    10Fail
    See Also
    fileread(), xfseek()
    Example
    

    

    

    Topへ
    


    xfseek()
    

    

    

    Description
    

    Moves to the postion specified by POSITION in the text file read by the fileread()
    The xfetch() after xfseek() starts fetching from the point of POSITION.
    

    

    

    Syntax
    

    ret = xfseek(position)
    

    

    

    Parameters
    

    position:Location in a file
    

    

    

    Return Value
    

    1:Success
    0:Error
    

    

    

    System Return Value
    

    ERROR
    0Success
    10xsql is not processed
    11Invalid file status
    

    

    

    See Also
    

    xsql(), fileread()
    

    

    

    Example
    

    

    

    

    Topへ
    


    xsql()
    

    

    

    Description
    

    Executes the puseudo SQL statement to access file and pseudo table.
    Sets the select clause in SQL_STATEMENT
    Sets similar table functions in FROM
    

    

    

    Syntax
    

    ret = xsql(sql_statement)
    

    

    

    Parameters
    

    sql_statement:select statement
    

    

    

    Return Value
    

    1:Success
    0:Error
    

    

    

    System Return Value
    

    ERROR
    0Success
    11Argument error
    その他Returned value by the pseudo table
    

    

    

    Example
    

    //[result example]
//
// Jan has 31 days
// Feb has 29 days
// Mar has 31 days
// Apr has 30 days
// May has 31 days
// Jun has 30 days
// Jul has 31 days
// Aug has 31 days
// Sep has 30 days
// Oct has 31 days
// Nov has 30 days
// Dec has 31 days
//

run()
exit

function run()
	local data1 = { ' 1', ' 2', ' 3', ' 4', ' 5', ' 6', ' 7', ' 8' ,' 9', '10', '11', '12' }
	local data2 = { 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec' }
	local data3 = { '31', '29', '31', '30', '31', '30', '31', '31', '30', '31', '30', '31' }
	local i, m_tab

	xsql select m_num, m_name, m_days from array('m_num', data1, 'm_name', data2, 'm_days', data3);
	m_tab = xfetch(0)
	xclose()

	loop i=0; i<count(m_tab); i++
		print('${m_tab[i]["m_name"]} has ${m_tab[i]["m_days"]} days', stdout)
	endloop

endfunction

    

    

    

    Topへ
    

    

    

    
	© Insight Technology, Inc. 1996-2003 All Rights Reserved.