Arrays

--0-1229734773-1314176707=:10439
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi, I would like to create a function in Mysql that returns an Array o=
f Numbers. I am trying to run a big amount of stream of data on Mysql and I=
can't=A0afford the time to store the data into a table and retrieve it lat=
er. Is there any solutions?    Best regards,=0AJavad Bakhshi,=0ACom=
puter Science M.Sc=0ADepartment of IT, Uppsala University=0A
--0-1229734773-1314176707=:10439--

Re: Arrays

>>>> 2011/08/24 02:05 -0700, javad bakhshi >>>>
Hi, I would like to create a function in Mysql that returns an Array of Numbers. I am trying to run a big amount of stream of data on Mysql and I can't afford the time to store the data into a table and retrieve it later. <<<<<<<<
Are there any arrays at all in MySQL?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Arrays

----- Original Message -----
> From: "Halász Sándor"
>
> Hi, I would like to create a function in Mysql that returns an Array
> of Numbers. I am trying to run a big amount of stream of data on
> Mysql and I can't afford the time to store the data into a table and
> retrieve it later. <<<<<<<<
> Are there any arrays at all in MySQL?

Arrays; no. Functions can return simple numeric/string values; I'm not sure=
wether or not you can return a recordset from a stored procedure.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Arrays

--0-320754327-1314392333=:58471
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=A0Thanks guys for the help. but my problem seems to stand unsolved.=
________________________________=0AFrom: Johan De Meersman ivamp@tuxera.be>=0ATo: Hal=E1sz S=E1ndor =0ACc: mysql@lists.m=
ysql.com=0ASent: Friday, August 26, 2011 7:04 PM=0ASubject: Re: Arrays=0A=
=0A----- Original Message -----=0A> From: "Hal=E1sz S=E1ndor" >=0A>=0A> Hi, I would like to create a function in Mysql that returns an Ar=
ray=0A> of Numbers. I am trying to run a big amount of stream of data on=0A=
> Mysql and I can't afford the time to store the data into a table and=0A> =
retrieve it later. <<<<<<<<=0A> Are there any arrays at all in MySQL? =
Arrays; no. Functions can return simple numeric/string values; I'm not sure=
wether or not you can return a recordset from a stored procedure. =0A=
--=0ABier met grenadyn=0AIs als mosterd by den wyn=0ASy die't drinkt, is ee=
ne kwezel=0AHy die't drinkt, is ras een ezel --=0AMySQL General Mailin=
g List=0AFor list archives: http://lists.mysql.com/mysql=0ATo unsubscribe:=
=A0 =A0 http://lists.mysql.com/mysql?unsub=3Djavadbakhshi@yahoo.com
--0-320754327-1314392333=:58471--

Re: Arrays

--90e6ba6e8ea887a19b04ab70ba4b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

The only solution you have:

let your server-side procedure populate a temporary table and select from
that when the procedure is complete. Depending on the thresholds you set i=
n
your mysql configuration, that memory table might very well reside in RAM
anyhow. I doubt that speed will be an issue.

- michael dykman

2011/8/26 javad bakhshi

>
>
> Thanks guys for the help. but my problem seems to stand unsolved.
>
>
>
> ________________________________
> From: Johan De Meersman
> To: Hal=E1sz S=E1ndor
> Cc: mysql@lists.mysql.com
> Sent: Friday, August 26, 2011 7:04 PM
> Subject: Re: Arrays
>
> ----- Original Message -----
> > From: "Hal=E1sz S=E1ndor"
> >
> > Hi, I would like to create a function in Mysql that returns an Array
> > of Numbers. I am trying to run a big amount of stream of data on
> > Mysql and I can't afford the time to store the data into a table and
> > retrieve it later. <<<<<<<<
> > Are there any arrays at all in MySQL?
>
> Arrays; no. Functions can return simple numeric/string values; I'm not su=
re
> wether or not you can return a recordset from a stored procedure.
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Djavadbakhshi@yahoo.com




--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--90e6ba6e8ea887a19b04ab70ba4b--

Re: Arrays

>>>> 2011/08/26 13:58 -0700, javad bakhshi >>>>
Thanks guys for the help. but my problem seems to stand unsolved.
<<<<<<<<
Right, no arrays. Nothing is left but table. I used a temporary table, but note that MySQL also does not let table be returned, or passed in. The table-name will be *sigh* global.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Arrays

--20cf307abe757b7e0a04ab83520a
Content-Type: text/plain; charset=ISO-8859-1

Another approach to this is to create a concatenated string from the PKs of
the result set and then parse that within a stored procedure elsewhere in
your Front End (FE). For more information on this, visit
www.artfulsoftware.com and check out the Queries page.

Arthur

--20cf307abe757b7e0a04ab83520a--

Re: Arrays

----- Original Message -----
> From: "Halász Sándor"
>
> passed in. The table-name will be *sigh* global.

No: when you use /create temporary table/, the table is local to your conne=
ction. You can perfectly open a dozen connections and have them all create =
a temporary table called intermediate_results, they'll not interfere with o=
ne another. Temporary tables also get automatically dropped when the connec=
tion closes - although it's of course best practice to do it yourself as so=
on as you don't need it any longer :-)

Also, do not confuse /create temporary table/ with /create table ... engine=
=3Dmemory/ - while both are in memory, the latter creates a global table wh=
ose metadata (so not the actual data, just the structure) even survives a r=
eboot.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Arrays

--=_fce2d6c4-cbff-44be-84a3-1cef3a0994ab
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

----- Original Message -----

> From: "Arthur Fuller"

> Another approach to this is to create a concatenated string from the
> PKs of the result set and then parse that within a stored procedure
> elsewhere in your Front End (FE). For more information on this,
> visit www.artfulsoftware.com and check out the Queries page.

That works; but string constructs are touchy at the best of times, and get exponentially complexer with your dataset. If you're going that route anyway, just construct XML with all the data you need to return - the application is almost guaranteed to have some way of handling it these days, and I'm reasonably sure that MySQL also has some functionality for it.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--=_fce2d6c4-cbff-44be-84a3-1cef3a0994ab--

Re: Arrays

--90e6ba6e89828c84b604ab914205
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

2011/8/27 Hal=E1sz S=E1ndor

> >>>> 2011/08/26 13:58 -0700, javad bakhshi >>>>
> Thanks guys for the help. but my problem seems to stand unsolved.
> <<<<<<<<
> Right, no arrays. Nothing is left but table. I used a temporary table, bu=
t
> note that MySQL also does not let table be returned, or passed in. The
> table-name will be *sigh* global.
>


I think you missed one key word: temporary as in
CREATE TEMPORARY TABLE ...

Temporary tables are only visible to the connection that creates them and
they are automatically dropped when that connection closes,. Concurrent
connections can create temporary tables with the exact same name with no
fear of them conflicting or affecting each other's data. They work like a
charm even in high load, multi-concurrency environments.


--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--90e6ba6e89828c84b604ab914205--

Re: Arrays

>>>> 2011/08/28 09:36 +0200, Johan De Meersman >>>>
No: when you use /create temporary table/, the table is local to your connection. You can perfectly open a dozen connections and have them all create a temporary table called intermediate_results, they'll not interfere with one another. Temporary tables also get automatically dropped when the connection closes - although it's of course best practice to do it yourself as soon as you don't need it any longer :-)
<<<<<<<<
No, I meant that in MySQL there is no means of passing a table-name to a routine, nor returning one; therefore, the only means of handling a table within a routine and without is with a global name; that is, one used but not declared within the routine--and "CREATE [TEMPORARY] TABLE" never declares a variable local to a routine. As for the returned array, if there were one, that would be stored in a variable that is lost with the connection.

I wrote an edit-distance function, and two procedures, that use temporary table for array. The procedures s outcome was in the temporary tables. In routines there are table-use restrictions, more in functions than procedures, and I so wrote my function because of them.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Arrays

On 8/27/2011 11:18, wrote:
>>>>> 2011/08/26 13:58 -0700, javad bakhshi>>>>
> Thanks guys for the help. but my problem seems to stand unsolved.
> <<<<<<<<
> Right, no arrays. Nothing is left but table. I used a temporary table, but note that MySQL also does not let table be returned, or passed in. The table-name will be *sigh* global.
>
>

If the table remains an active part of the connection, you always have
access to it. It is possible to pass the name of the table into a stored
procedure and use a prepared statement to do nearly anything you want to
do with the data.

What is it you are trying to do with this array in the first place? It
sounds as though trying to process this data using the set-oriented
functions of the SQL language is causing you more of a problem than the
fact that the language doesn't have an array data type.

There are normally many ways to solve any problem. Perhaps if you shared
the problem you are trying to solve, you can see how many different ways
the members of the list can solve it without resorting to an array?

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Arrays

--0-1913014913-1314957088=:24731
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi again, Thanks for the tips. My problem is:=0AI have a Function in M=
ysql that has some arguments in the signature as=A0follows:  =0ACREATE =
FUNCTION Myfunction( type TINYINT, sec SMALLINT, vid INTEGER,   way TIN=
YINT, quid INTEGER, day TINYINT )=0ARETURNS CHAR(50)=0ABEGIN   DECLARE =
result CHAR(50);=0ADECLARE Temp DECIMAL(9,1);   =A0 SELECT Table1( vid,=
day, way) INTO Temp;   =A0 IF Temp IS NOT NULL THEN=0ASELECT CONCAT_WS=
(',',sec, 0, quid, Temp) into result;     =A0 ELSE SELECT CON=
CAT_WS(',',sec, 0, quid, 0 ) into result;     =A0 END IF;=0ARETURN=
result; =A0 =A0   END; =0AThe problem seems to be solved by using=
CHAR but I really want to have a sequence of Integers as the result not CH=
AR. The reason that I dont use a table to insert the result into it and ret=
rieve it later is that this=A0process takes more time that It should.  =
Any thoughts?  Best regards,=0AJavad _______________________=
_________=0AFrom: Shawn Green (MySQL) =0ATo: =0AC=
c: javad bakhshi ; Johan De Meersman ra.be>; "mysql@lists.mysql.com" =0ASent: Monday, Aug=
ust 29, 2011 4:51 PM=0ASubject: Re: Arrays On 8/27/2011 11:18,=A0 wrot=
e:=0A>>>>> 2011/08/26 13:58 -0700, javad bakhshi>>>>=0A>=A0 Thanks guys fo=
r the help. but my problem seems to stand unsolved.=0A> <<<<<<<<=0A> Right,=
no arrays. Nothing is left but table. I used a temporary table, but note t=
hat MySQL also does not let table be returned, or passed in. The table-name=
will be *sigh* global.=0A> =0A> If the table remains an active part =
of the connection, you always have access to it. It is possible to pass the=
name of the table into a stored procedure and use a prepared statement to =
do nearly anything you want to do with the data. What is it you are tr=
ying to do with this array in the first place?=A0 It sounds as though tryin=
g to process this data using the set-oriented functions of the SQL language=
is causing you more of a problem than the fact that the language doesn't h=
ave an array data type. There are normally many ways to solve any prob=
lem. Perhaps if you shared the problem you are trying to solve, you can see=
how many different ways the members of the list can solve it without resor=
ting to an array? -- Shawn Green=0AMySQL Principal Technical Support E=
ngineer=0AOracle USA, Inc. - Hardware and Software, Engineered to Work Toge=
ther.=0AOffice: Blountville, TN -- MySQL General Mailing List=0AFor li=
st archives: http://lists.mysql.com/mysql=0ATo unsubscribe:=A0 =A0 http://l=
ists.mysql.com/mysql?unsub=3Djavadbakhshi@yahoo.com
--0-1913014913-1314957088=:24731--

Re: Arrays

Hello Javad,

On 9/2/2011 05:51, javad bakhshi wrote:
> Hi again,
>
> Thanks for the tips. My problem is:
> I have a Function in Mysql that has some arguments in the signature as
> follows:
>
> CREATE FUNCTION Myfunction( type TINYINT, sec SMALLINT, vid INTEGER,
> way TINYINT, quid INTEGER, day TINYINT )
> RETURNS CHAR(50)
> BEGIN
> DECLARE result CHAR(50);
> DECLARE Temp DECIMAL(9,1);
> SELECT Table1( vid, day, way) INTO Temp;
> IF Temp IS NOT NULL THEN
> SELECT CONCAT_WS(',',sec, 0, quid, Temp) into result;
>
> ELSE
>
> SELECT CONCAT_WS(',',sec, 0, quid, 0 ) into result;
>
> END IF;
> RETURN result;
> END;
>
>
> The problem seems to be solved by using CHAR but I really want to have a
> sequence of Integers as the result not CHAR. The reason that I dont use
> a table to insert the result into it and retrieve it later is that this
> process takes more time that It should. Any thoughts?

Actually, that's not 'the problem' you are attempting to solve at all.
That is an attempt at a 'solution' to the problem.

If I stare very hard into my crystal ball and use all of my
psychic-SQL-fu, all I can tell is that you are attempting to build some
kind of comma-separated value. However, why you need this list and what
it is trying to solve is beyond me.

Please step back from the SQL end of things for a second and talk to us
about the actual problem you are trying to solve. Are you generating
nuclear launch codes? Are you indexing someone's genome? Are you trying
to build a web page? All of this SQL coding is designed to be a single
step in some larger process. If you share that larger purpose with us,
we may be able to suggest a more efficient approach than arrays to solve
your larger problem.

Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Arrays

--0-1400701486-1314968983=:88968
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi, None of the above. :) .This is just an academic research on a benc=
hmark. I just want to access my database with this function and return 4 In=
teger values. I=A0separated=A0the numbers with comma to process on it later=
as an string and split the result using string functions which did work. I=
was just wondering if its possible with mysql to return a sequence of numb=
ers or not. I think to solve my problem among the ways mentioned before I w=
ill create a table with Engine=3DMemory to speed up things. I don't think a=
ny other way is possible.  =0AThanks,=0AJavad =0A_________________=
_______________=0AFrom: Shawn Green (MySQL) =0ATo=
: javad bakhshi =0ACc: "mysql@lists.mysql.com" ql@lists.mysql.com>=0ASent: Friday, September 2, 2011 2:18 PM=0ASubject: Re=
: Arrays Hello Javad, On 9/2/2011 05:51, javad bakhshi wrote:=0A>=
Hi again,=0A> =0A> Thanks for the tips. My problem is:=0A> I have a Functi=
on in Mysql that has some arguments in the signature as=0A> follows:=0A> =
=0A> CREATE FUNCTION Myfunction( type TINYINT, sec SMALLINT, vid INTEGER,=
=0A> way TINYINT, quid INTEGER, day TINYINT )=0A> RETURNS CHAR(50)=0A> BEGI=
N=0A> DECLARE result CHAR(50);=0A> DECLARE Temp DECIMAL(9,1);=0A> SELECT Ta=
ble1( vid, day, way) INTO Temp;=0A> IF Temp IS NOT NULL THEN=0A> SELECT CON=
CAT_WS(',',sec, 0, quid, Temp) into result;=0A> =0A> ELSE=0A> =0A> SELECT C=
ONCAT_WS(',',sec, 0, quid, 0 ) into result;=0A> =0A> END IF;=0A> RETURN res=
ult;=0A> END;=0A> =0A> =0A> The problem seems to be solved by using CHAR bu=
t I really want to have a=0A> sequence of Integers as the result not CHAR. =
The reason that I dont use=0A> a table to insert the result into it and ret=
rieve it later is that this=0A> process takes more time that It should. Any=
thoughts? Actually, that's not 'the problem' you are attempting to so=
lve at all. That is an attempt at a 'solution' to the problem. If I st=
are very hard into my crystal ball and use all of my psychic-SQL-fu, all I =
can tell is that you are attempting to build some kind of comma-separated v=
alue. However, why you need this list and what it is trying to solve is bey=
ond me. Please step back from the SQL end of things for a second and t=
alk to us about the actual problem you are trying to solve. Are you generat=
ing nuclear launch codes? Are you indexing someone's genome? Are you trying=
to build a web page? All of this SQL coding is designed to be a single ste=
p in some larger process. If you share that larger purpose with us, we may =
be able to suggest a more efficient approach than arrays to solve your larg=
er problem. Regards,=0A-- Shawn Green=0AMySQL Principal Technical Supp=
ort Engineer=0AOracle USA, Inc. - Hardware and Software, Engineered to Work=
Together.=0AOffice: Blountville, TN
--0-1400701486-1314968983=:88968--

Re: Arrays

>>>> 2011/09/02 06:09 -0700, javad bakhshi >>>>
Hi, None of the above. :) .This is just an academic research on a benchmark. I just want to access my database with this function and return 4 Integer values. I separated the numbers with comma to process on it later as an string and split the result using string functions which did work. I was just wondering if its possible with mysql to return a sequence of numbers or not. I think to solve my problem among the ways mentioned before I will create a table with Engine=Memory to speed up things. I don't think any other way is possible.
<<<<<<<<
The string takes less time than table (you have not shown your table code) only because of the problem s size. If you had thousands of numbers the string at first would be only slower, but with enough it would not work at all, because you would reach MySQL s string-size limit.

A benchmark of repeatedly only 4 integers is not much of one.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org