By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,876 Members | 1,562 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,876 IT Pros & Developers. It's quick & easy.

array parameter

P: n/a
-
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_procedure(param1, param2, 'value1:value2:value3');
Jul 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
- wrote:
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_procedure(param1, param2, 'value1:value2:value3');


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.
Jul 23 '05 #2

P: n/a
-
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.

Jul 23 '05 #3

P: n/a
-
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?
Jul 23 '05 #4

P: n/a
- 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.
Jul 23 '05 #5

P: n/a
- 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.
Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.