SQL tools -- understanding "bound"

[tom kroto]

apparently the word "bound" has several meanings.

I downloaded from the Census Department a small excel table. It did not have many rows but it had 52 columns. From what I could read the default SQL can handle is 32. I got this message "not bound" which I was able to research. I cut the census table to 26 columns. I tried various ideas and got a better idea of the whole process.

But searching on the word "bound" I found various topics covered. Can you give me just a simplified definition of bound. It seems to be an important word with many different uses. Thank you.

[Michael Mattias]

"Bound" in ODBC programming terms means, associating a variable in your progam with either a parameter used by an SQL statement for input (most often in a WHERE clause) or a column returned by by a SELECT statement.

eg : Bind some variables to an input parameter and the output columns of a SELECT statement:

  stmt = " Select name, address from customer where cust_no = ?"
  ' bind the program variable "MyCustNo&"   to the first input parameter 
   SQLbindParameter   ( param_input, parm#1, VARPTR(MyCustNo&) ...) 
  ' bind the two output columns to "myName$" and "MyAddress" 
  SQLbindParameter   ( param_output, parm#1, STRPTR(MyName$)...
  SQLbindParameter   ( param_Output, parm#2, STRPTR(MyAddress$)...

Because your parameters are bound to variables, you can now execute this statement many times, using your program variables directly:

  

  FOR I = 1 to 10 
      MyCustNo   = I     ' set the value of the input parameter variable
      SQL_Execute
      SQL_FETCH_A_ROW     ' we only expect one row from this statement 
      PRINT MyName$, MyAddress$  
        ' because you BOUND MyName$ and MyAddress$ to the output columns 
       ' the FETCH deposits those columns directly in your variables and 
       ' you can just use those immediately after a successful fetch 

  NEXT

I KNOW SQL tools comes with examples of this.

On your multiple columns problem, if I recall correctly there are two (or more) versions of the SQL Tools' functions to execute statements and fetch results... and the "quick" versions have some limitations, and I'm pretty sure 'number of columns' is one of those. Check out the "Not Quick" version(s) of those functions.

MCM

[Eric Pearson]

Tom --

> From what I could read the default [SQL Tools] can handle is 32.

Correct... if you use the general-purpose SQL_Init function. If you use the SQL_Initialize function instead of SQL_Init you can write programs that deal with tables containing up to 999 columns. It's up to you. SQL Tools defaults to a max of 32 columns as a memory-saving measure; 32 is plenty for most databases, and if we always allocated memory for 999 columns it would be very inefficient. See SQL_Initialize in the Help File for lots more information.

> Can you give me just a simplified definition of bound.

Please see Result Column Binding (Basic) in the SQL Tools Help File.

> It seems to be an important word with many different uses.

Not really; it has a very specific meaning. Basically, a program must "bind" each column of a result set (the output that results from a SELECT statement) before it can access the data in that column. SQL Tools provides "auto-binding" so you don't usually have to worry about it, but in this case...

1) By using SQL_Init you told SQL Tools to expect no more than 32 columns per table.
2) Your program then accessed a table with 52 columns.
3) When you tried to get data from columns 33-52, SQL Tools reported the error "column not bound" for each unbound column.

Use SQL_Initialize instead of the generic SQL_Init function -- and specify the maximum number of columns that you want to use -- and the problem will disappear.

Does that help?

-- Eric Pearson, Perfect Sync, Inc.

[tom kroto]

yes okay

yes I understand now. I've examined the sample from Michael as well as your description and I understand.

[Stuart McLachlan]

Another meaning of Bound

> It seems to be an important word with many different uses.

Not really; it has a very specific meaning. Basically, a program must "bind" each column of a result set (the output that results from a SELECT statement) before it can access the data in that column. SQL Tools provides "auto-binding" so you don't usually have to worry about it, but in this case...

There is a second common use of "bound" when talking about data applications developed in environments such as MS Access and .Net - the concept of "bound controls" versus "unbound controls".

In these environments, you can "bind" data from a data source directly to a control on a form. Any changes to the data in the control are automatically passed back to the database.

[Eric Pearson]

Ah, you're right of course, I had forgotten about that. Thanks!

-- Eric