array parameter

Bill Karwin wrote:
 >
 > What are you storing in the elements of the array?
 > What are you going to do with the values?
 > Do you need a way to separate the list into the individual elements
 > within the procedure?
 > What alternatives have you considered? E.g., inserting value1, value2,
 > value3 into a temp table so you can access them from within the stored
 > proc.

in this case, i am passing the values to the procedure to indicate which
results to select that has the string in the column.

e.g. ':apple:papaya:watermelon:... so on and so forth.

i haven't considered inserting the values into a temp table. it looks
easier though but if i were to do that, it looks kind of
'unconsolidated' (can't think of a proper term to describe it)
but if this is the preferred way, i won't hesitate to use it.
 >> Stay informed about: array parameter 

hi bill, is it a common practice to store variables into a table and
have 'get', 'set' methods?

create table config_table (
  column1 ...
  column2...
);

create procedure get_column1()
  ...
  ...

create procedure set_column1(..)
  ...
  ...


imo, a table is meant to store records with more than one row rather
than a maximum of one.

but there seems to be no other alternative that i know of to store
variables. so i reckon this is the only way to go?
 >> Stay informed about: array parameter 

- wrote:
 > hi bill, is it a common practice to store variables into a table and
 > have 'get', 'set' methods?

I think it's more common practice to define getters and setters in a
host language, e.g. Java.

SQL was never designed or intended as a complete programming language
environment.

Regards,
Bill K.
 >> Stay informed about: array parameter 

- wrote:
 > e.g. ':apple:papaya:watermelon:... so on and so forth.

So somehow you need to convert that string into:
IN ('apple', 'papaya', 'watermelon')
or some such. But unfortunately, as we have discussed recently on this
forum, MySQL does not support dynamic queries within stored procedures.
That is, there is no equivalent of EXECUTE IMMEDIATE.

 > i haven't considered inserting the values into a temp table. it looks
 > easier though but if i were to do that, it looks kind of
 > 'unconsolidated' (can't think of a proper term to describe it)
 > but if this is the preferred way, i won't hesitate to use it.

I think the more common solution is to run dynamic queries from a host
language (Java, PHP, Perl, etc.), where you can build a query
dynamically and then execute that string as a SQL statement.

Regards,
Bill K.
 >> Stay informed about: array parameter