Jump to content

how to pass an array to mysql stored procedure


shaddf

Recommended Posts

i have this array:

test={'dog','cow','shee'};

 

how can I pass it to a stored procedure:

call sp_array(in i_arr1 text);

what data type should I use?

Iam currently looping through and multi executing the procedure in php to pass the values one by one .But is there a way to pass it to the procedure and then assign it to a cursor in sql.

Link to comment
Share on other sites

You can use list() or implode() them and comma separated

 

If happened to pass them through a loop can also build a string using concatenating assignment operator .=

 Sorry but ,i may have an array containing a sentences like "this is your day.Go for it","You are  doing it well","Keep it up!".how caan I pass that array to a stored procedure.so that I can assign it to a cursor in the procedure

Link to comment
Share on other sites

Arrays in stored procedures are not supported and can get messy.

 

What you should do is normalization in a relational database instead.

Splitting your data first and importing it into a database the way you intend to use it.

 

Split the array prior and insert each as it's own id while associating it to whatever user/group/values id another table you intend on keeping it associated to.

 

If insist on doing it as trying to save arrays, you have to come up with your own method that can work for you.

Usually can pass a comma delimited string or split the data a delimiter would possibly never use such as a double pipe ||

Pass the string and store as VARCHAR(255) to the stored procedure.

Inside the procedure you can split the string back into its elements.

 

Can also look into serialize and unserialize

Link to comment
Share on other sites

Arrays in stored procedures are not supported and can get messy.

 

What you should do is normalization in a relational database instead.

Splitting your data first and importing it into a database the way you intend to use it.

 

Split the array prior and insert each as it's own id while associating it to whatever user/group/values id another table you intend on keeping it associated to.

 

If insist on doing it as trying to save arrays, you have to come up with your own method that can work for you.

Usually can pass a comma delimited string or split the data a delimiter would possibly never use such as a double pipe ||

Pass the string and store as VARCHAR(255) to the stored procedure.

Inside the procedure you can split the string back into its elements.

 

Can also look into serialize and unserialize

Thanks for the double pipe idea.how can i split the string back and assign it to a cursor in the stored procedure.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.