Post by markl on Jan 4, 2008 11:43:13 GMT -5
This code queries a table named EMPLOYEE within a database named Sample.db3 There are two parsing functions used, one to get the field headers and the other to read and file the two dimensional array with table data. The array is globally defined, so the data is available after you disconnect from your database.
ENTERING AN ARRAY ELEMENT OUTSIDE OF THE DIMENSIONED ARRAY SIZE WILL CAUSE A CRASH as no error checking is incorporated.
The Sample.db3 sqlite database should be placed within the rbp base directory. Otherwise the code will need to be updated to include a full path.
To get the database, download SharpPlus Sqlite Developer here www.sharpplus.com/download
code starts below
================================================
'-----------------------------------------------------------------
end
ENTERING AN ARRAY ELEMENT OUTSIDE OF THE DIMENSIONED ARRAY SIZE WILL CAUSE A CRASH as no error checking is incorporated.
The Sample.db3 sqlite database should be placed within the rbp base directory. Otherwise the code will need to be updated to include a full path.
To get the database, download SharpPlus Sqlite Developer here www.sharpplus.com/download
code starts below
================================================
GLOBAL columns,rows 'DIM array element size
GLOBAL griddata$,columnHDR$ 'DIM data containers
GLOBAL n,i,y 'DIM counters
'Initialize intial numberic variables to zero
Columns=0
rows=0
y=0
n=0
i=0
'-------------------------------------------------------
'-Parsing a character string from an SQlite table query-
'-------------------------------------------------------
' The Sample.db3 database is a demo created from the
' install of SharpPlus SqliteDeveloper and copied into
' the rbp folder for access
'-------------------------------------------------------
sqliteconnect #mySQLdb, "Sample.db3"
'select all fields in table
query$ = "select * from EMPLOYEE order by LAST_NAME asc;"
#mySQLdb execute(query$)
rows = #mySQLdb ROWCOUNT() 'Get the number of rows for the array
DIM columnHDR$(1,rows) 'Dimension column headers array
columninfo$ = #mySQLdb COLUMNNAMES$() 'Get the number of columns for the array
a$ = columninfo$
ix = 1
while (word$(a$,ix,",") <> "")
columnHDR$(1,ix) = word$(a$,ix,",")
'print word$(a$,ix,",")
ix = ix + 1
wend
columns = ix-1
DIM griddata$(columns,rows) 'Dimension the array used to fill the grid from the data collected above
while #mySQLdb hasanswer()
y=y+1
result$ = #mySQLdb nextrow$("|")
ix = 1
while (word$(result$,ix,"|") <> "")
griddata$(ix,y) = word$(result$,ix,"|")
'print griddata$(ix,y)
ix = ix + 1
wend
wend
#mySQLdb disconnect()
'The following section of code displays data elements
'Used for data verification only
'------------------------------------------------------------------
Print "The following displays an array element"
print ""
input "Enter a column number (1-11): "; dx
input "Enter a row number (1-42): "; dy
print ""
print "The array element you selected contains : ";
print griddata$(dx,dy)
print ""
Print "The grid will print to verify accuracy"
print ""
'-------------------------------------------------------------------
html "<table align=center cellpadding=5 border=2 style=""background-color:#9CEF10"">"
'==================================================
'<!-- add columns in grid -->
'Use the for next function to add columns
'==================================================
html " <tr><td class=""grdhdr""> "
for i = 1 to columns
html "</td><td class=""grdhdr"">"
print columnHDR$(1,i) 'Fill the column headers from the Array
next i
html "</td></tr> "
'==================================================
'<!-- add rows in grid -->
'Use the for next function to add rows
'==================================================
for i = 1 to rows
html " <tr><td class=""grdhdr"">"
print "row-"+str$(i)
for n = 1 to columns
html "</td><td class=""grdbdy"">"
print griddata$(n,i) 'Print the field data from the array
next n
html "</td></tr> "
next i
html "</table> "
'HTML styling & beautification for grid display
'------------------------------------------------------
html "<style>
.grdhdr{background-color:#5858A8;
font-family:arial,sans-serif;
font-weight:bold;
font-size:12px;
text-align:center;
color:#F8FAFA }
.grdbdy {background-color:#EFEBCD;
font-family:arial,sans-serif;
font-weight:bold;
font-size:10px;
text-align:left;
color:#602400 };
</style>"
'-----------------------------------------------------------------
end