How to Fill an Array with Column Data from a DB

Keywords: 	   filling an array

---

>i am trying my first array
>(for the Nth time). when
>constructing the array, do i
>have to have all of the data
>before assigning values? 

Not necessarily. But you would want a good idea of the size of array you would need to create.

>say i am working with a database
>and i am querying table and
>column names to put them into
>an array. do i need to assign
>the names to a variable and
>then put the values into an
>array or can i put the values
>directly into the array as i
>am getting them from the
>database? 

You can put them directly into the array. .

Array elements are referenced with their subscripts enclosed in square brackets. If an array has more than one dimension, the subscripts are separated with commas.

Eg:

arrayvar[1] 
arrayvar[1, 1] 
arrayvar[0, 5, 2]

Array subscripts are 0-based. I.e., the first element in an array is array[0].

> how would i keep
>relative info associated? if
>i want to find all of the
>columns in a table, i would
>want them to be addressed by
>array[2,"col"] where "col"
>would be the first column to
>the last column, realizing
>that i have to count through
>the entries. right? how
>would i access info that is
>referential, as in things that
>are associated with others in
>different areas of the
>database?

It sounds like you will only need a single dimension array to store all the column names.

ODBC Example:

AddExtender("wwodb34i.dll")
DSN ="Test"
TABLE = "Users"

;THIS ALLOCATES A SQL ENVIRONMENT HANDLE
henv = qAllocEnv()

If henv == -1
  ;THIS RETURNS LAST CODE SET BY LAST SQL FUNCTION
  retcode = qLastCode()  
  Message("qAllocEnv failed", retcode)
  Exit
Endif


;ALLOCATES A SQL CONNECTION HANDLE - MAXIMUM 
;OF 10 OPEN CONNECTION HANDLES
hdbc = qAllocConnect(henv) 

If hdbc == -1
  retcode = qLastCode()
  Message("qAllocConnect failed", retcode)
  Exit
Endif


;CONNECTS TO THE "SAMPLE" DATA SOURCE
retcode = qConnect(hdbc, DSN, "", "") 

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qConnect failed", retcode)
  Exit
Endif


;THIS ALLOCATES A SQL STATEMENT HANDLE
hstmt = qAllocStmt(hdbc)  

If hstmt == -1
  retcode = qLastCode()
  Message("qAllocStmt failed", retcode)
  Exit
Endif

;GETNUMBER OF COLUMNS IN THE TABLE
cSQL =StrCat("SELECT * FROM ",TABLE)

retcode = qExecDirect(hstmt,cSQL)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
   Display(2,"SQL Operation Status",qError(hstmt,2))
   exit
Else
   numcols=qNumRsltCol(hstmt) 
Endif
qFreeStmt(hstmt, 0)  

;FILLS A RESULT SET WITH A LIST OF COLUMN NAMES IN SPECIFIED ;TABLES FROM "SAMPLE" DATA SOURCE
retcode = qColumns(hstmt, @qNull, @qNull, TABLE, @qNull) 

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qColumns failed", retcode)
  Exit
Endif

;BINDS A COLUMN IN A RESULT SET TO A VARIABLE THAT YOU NAME HERE
retcode = qBindCol(hstmt, 4, "colname", 80)  


If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qBindCol failed", retcode)
  Exit
Endif


;CREATE ARRAY 
Array = ArrDimension(numcols)
count = 0

While @TRUE
  retcode = qFetch(hstmt)
  If retcode == @qNoData Then Break

  If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
    Message("qFetch failed", retcode)
    Exit
  Endif

  ;FILL THE ARRAY WITH COLUMN NAMES 
  Array[count] = colname
  count = count+1
EndWhile

;DISPLAY COLUMNS DATA SOURCE 
list = ""

For x = 0 to ArrInfo(Array,1)-1
         Col =  Array[x]
         list = StrCat(list,@Tab,col)
Next

list = StrTrim(list)
AskItemList("List of columns pulled from the array", list, @TAB, @unsorted, @single)

;SQL_CLOSE - RESETS OR FREES A SQL STATEMENT HANDLE 
;(SET WITH QALLOC)
qFreeStmt(hstmt, 0)  
qFreeStmt(hstmt, 2)  ; SQL_UNBIND

---

Article ID:   W14823