array parameter

[-]

since mysql's stored procedure does not accept an array as a parameter,
is it a good practice to input a string delimited parameter eg.

call stored_procedur e(param1, param2, 'value1:value2: value3');

[Bill Karwin]

Re: array parameter

- wrote:[color=blue]
> since mysql's stored procedure does not accept an array as a parameter,
> is it a good practice to input a string delimited parameter eg.
>
> call stored_procedur e(param1, param2, 'value1:value2: value3');[/color]

That question is impossible to answer for the general case. Good
practice depends on what your procedure needs to do with that list of
values.

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.

Regards,
Bill K.

[-]

Re: array parameter

Bill Karwin wrote:[color=blue]
>
> 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.[/color]

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.

[-]

Re: 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?

[Bill Karwin]

Re: array parameter

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

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.

[Bill Karwin]

Re: array parameter

- wrote:[color=blue]
> e.g. ':apple:papaya: watermelon:... so on and so forth.[/color]

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.
[color=blue]
> 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.[/color]

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.