Contents

Previous

Next

OMSQL-> introduction sqlconnect sqlexec sqlfetch sqlcolnames sqlnumrows sqltables sqldisconnect

sqlfetch

Index-> contents reference index search

Retrieving Results from a Data Source

Syntax	sqlfetch sqlfetch(numrows) sqlfetch(numrows, column sizes)
See Also	sqlconnect , sqlexec

Description
Obtains query results generated by the most recent call to sqlexec. If the data type of all columns in the result set generated by the most recent call to sqlexec are numeric-valued, sqlfetch returns a double precision matrix. If one or more of the columns in the result set are not numeric-valued, sqlfetch returns a character matrix.

Obtaining Numeric-Valued Results
If no arguments are given, the sqlfetch function returns an NR by NC matrix, where NR equals the number of rows in the result set and NC equals the number of columns in the result set. If the argument numrows is present it must be a positive integer scalar that specifies the number of rows from the result set to return.

Obtaining Character-Valued Results
If no arguments are given, the sqlfetch function returns an NR by NC matrix, where NR equals the number of rows in the result set and NC equals the sum of the column widths in the result set. If the argument numrows is present it must be a positive integer scalar that specifies the number of rows from the result set to return. If the argument column sizes is present it must be an NR by 1 integer column vector that specifies the column widths of the returned results. The value NR must equal the number of columns in the result set and each row of column sizes must be positive. If the actual width of column i in the result set is greater than column sizes(i) then the result is truncated. If the actual width of column i is greater than column sizes(i) then the result is padded.

Examples

Retrieving Numeric-Valued Results
If, at the O-Matrix prompt you enter
     sqlconnect("driver={Microsoft Access Driver (*.mdb)};Dbq=c:\omwin\omsql\Contacts.mdb;UID=;PWD=")
     sqlexec("SELECT ContactID, CallID FROM Calls WHERE CallID < 5")
     IDS = sqlfetch
O-Matrix will create the two column numeric valued matrix IDS where the first column of IDS equals the ContactID column form the Calls table, and the second column equals the CallID column from the Calls table. If you enter
     print "type = ", type(IDS), " columns = ", coldim(IDS), " rows = ", rowdim(IDS)
     print IDS
O-Matrix will print the attributes and value of the result and respond
type =  double  columns =  2  rows =  4 
{
[ 1 , 1 ]
[ 1 , 2 ]
[ 1 , 3 ]
[ 1 , 4 ]
}

Retrieving Character-Valued Results
If, at the O-Matrix prompt you enter
     sqlconnect("driver={Microsoft Access Driver (*.mdb)};Dbq=c:\omwin\omsql\Contacts.mdb;UID=;PWD=")
     sqlexec("SELECT CompanyName FROM Company")
     Companies = sqlfetch
O-Matrix will create a character matrix with 5 rows and 255 columns. Each row of the matrix will contain one row from the result set. If you enter
     print "type = ", type(Companies), " columns = ", coldim(Companies), " rows = ", rowdim(Companies)
     print Companies
O-Matrix will respond
type =  char  columns =  255  rows =  5 

Cascade Coffee Roasters                                                                                                
Fourth Coffee                                                                                                          
Health Food Store                                                                                                      
Northwind Traders                                                                                                      
Volcano Coffee Company                                                                                                 


For some queries that return large result sets it is frequently desirable to just return the first few rows of the result. If you enter the following, O-Matrix will perform the same query as above but only display the first 2 rows of the result set.
     sqlexec("SELECT CompanyName FROM Company")
     Companies = sqlfetch
     sqlfetch(2)


By default, each column in a character valued result set is returned with a length equal to the default length defined in the source table. For example, the CompanyName and Address fields in the sample Contacts database are each defined with a field size of 255. If you execute a query that returns the CompanyName and Address fields the result in O-Matrix will be a character matrix with 510 columns. The sqlfetch function can specify the desired widths. If you continue the above example by entering the following at the O-Matrix prompt,
     sqlexec("SELECT CompanyName, Address FROM Company")
     Companies = sqlfetch(5,{25,15})
O-Matrix will create the character matrix Companies that has 5 rows and 40 columns. If you continue the example by entering
     print Companies
O-Matrix will respond
Cascade Coffee Roasters  507 - 20th Ave.
Fourth Coffee            4110 Old Redmon
Health Food Store        14 Garrett Hill
Northwind Traders        722 Moss Bay Bl
Volcano Coffee Company   908 W. Capital