5.0.15 Stored Procedures

------=_NextPart_000_0003_01C5E11A.B010AF90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I'm need to the procedures in MySQL.... I'm trying to build dynamic =
SQL, similar to MS SQL Server. Can anyone tell me what's wrong with =
this procedure?



DELIMITER $$

DROP PROCEDURE IF EXISTS `goes_modules`.`RPT_WorkForcePlan_SP`$$
CREATE PROCEDURE `goes_modules`.`RPT_WorkForcePlan_SP`(IN Page int, IN =
RecsPerPage int, IN Sort varchar(25), IN WhereClause varchar(2000), OUT =
RecordCount varchar(25))
BEGIN
DECLARE FirstRec varchar(10);
DECLARE LastRec varchar(10);
DECLARE CurrYear varchar(4);
DECLARE SQL1 varchar(2000);
DECLARE SQL2 varchar(2000);
DECLARE RecordCountMsg varchar(100);

-- Select CurrYear=3Dcurdate(YYYY);

CREATE TEMPORARY TABLE TempItems
(ID int AUTO_Increment,
RequestID varchar(10),
`Function` varchar(250),
Organization varchar(250),
Company varchar(250),
FirstLastName varchar(500),
Year1 int,
Year2 int,
Year3 int,
Year4 int,
Year5 int,
Year6 int,
Year7 int,
Year8 int,
Year9 int,
Year10 int,
FTETotal int);

SET SQL1 =3D 'INSERT INTO TempItems =
(RequestID,`Function`,Organization,Company,FirstLastName, '
SET SQL1 =3D SQL1 + =
'Year1,Year2,Year3,Year4,Year5,Year6,Year7,Year8,Year9,Year1 0,FTETotal) =
'
SET SQL1 =3D SQL1 + 'select distinct r.requestid, =
function,organization,company, '
SET SQL1 =3D SQL1 + 'IFNULL(CAST((select =
group_concat(rtrim(ww.firstlast) SEPARATOR '', '') from =
workforceplan_workers ww where ww.requestid =3D r.requestid) AS =
CHAR),'''') AS `FirstLastName` '
SET SQL1 =3D SQL1 + ',sum(CASE year WHEN ''2006'' THEN fte ELSE 0 END) =
as ''2006'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2007'' THEN fte ELSE 0 =
END) as ''2007'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2008'' THEN fte ELSE 0 =
END) as ''2008'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2009'' THEN fte ELSE 0 =
END) as ''2009'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2010'' THEN fte ELSE 0 =
END) as ''2010'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2011'' THEN fte ELSE 0 =
END) as ''2011'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2012'' THEN fte ELSE 0 =
END) as ''2012'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2013'' THEN fte ELSE 0 =
END) as ''2013'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2014'' THEN fte ELSE 0 =
END) as ''2014'' '
SET SQL1 =3D SQL1 + ' ,sum(CASE year WHEN ''2015'' THEN fte ELSE 0 =
END) as ''2015'' '
SET SQL1 =3D SQL1 + ' ,IFNULL(sum(fte),0) as ftetotal '
SET SQL1 =3D SQL1 + ' from workforceplan_request r left join =
workforceplan_fte rf '
SET SQL1 =3D SQL1 + ' on r.requestid =3D rf.requestid left join =
workforceplan_workers sw on sw.requestid =3D r.requestid '
SET SQL1 =3D SQL1 + ' group by r.requestid, =
function,organization,company '
IF WhereClause <> ''
SET SQL1 =3D SQL1 + ' WHERE ''''=3D'''' ' + WhereClause
END IF;
IF Sort <> ''
SET SQL1 =3D SQL1 + ' Order by ' + Sort
END IF;

CALL SQL1;

SET FirstRec =3D (Page - 1) * RecsPerPage
SET LastRec =3D Page * RecsPerPage + 1

SET RecordCount =3D count(*) from TempItems
SET RecordCountMsg =3D Page + ' pages were requested, only '

SET SQL2 =3D 'SELECT * FROM TempItems WHERE ID > ' + FirstRec
SET SQL2 =3D SQL2 + 'AND ID < ' +LastRec

CALL SQL2;

END$$

DELIMITER ;


Melissa
------=_NextPart_000_0003_01C5E11A.B010AF90--

Re: 5.0.15 Stored Procedures

--=_alternative 005180AA852570AF_=
Content-Type: text/plain; charset="US-ASCII"

"Melissa Dougherty" wrote on 11/04/2005 08:35:18
AM:

> I'm need to the procedures in MySQL.... I'm trying to build dynamic
> SQL, similar to MS SQL Server. Can anyone tell me what's wrong with
> this procedure?
>
>
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `goes_modules`.`RPT_WorkForcePlan_SP`$$
> CREATE PROCEDURE `goes_modules`.`RPT_WorkForcePlan_SP`(IN Page int,
> IN RecsPerPage int, IN Sort varchar(25), IN WhereClause
> varchar(2000), OUT RecordCount varchar(25))
> BEGIN
> DECLARE FirstRec varchar(10);
> DECLARE LastRec varchar(10);
> DECLARE CurrYear varchar(4);
> DECLARE SQL1 varchar(2000);
> DECLARE SQL2 varchar(2000);
> DECLARE RecordCountMsg varchar(100);
>
> -- Select CurrYear=curdate(YYYY);
>
> CREATE TEMPORARY TABLE TempItems
> (ID int AUTO_Increment,
> RequestID varchar(10),
> `Function` varchar(250),
> Organization varchar(250),
> Company varchar(250),
> FirstLastName varchar(500),
> Year1 int,
> Year2 int,
> Year3 int,
> Year4 int,
> Year5 int,
> Year6 int,
> Year7 int,
> Year8 int,
> Year9 int,
> Year10 int,
> FTETotal int);
>
> SET SQL1 = 'INSERT INTO TempItems (RequestID,`Function`,
> Organization,Company,FirstLastName, '
> SET SQL1 = SQL1 + 'Year1,Year2,Year3,Year4,Year5,Year6,Year7,Year8,
> Year9,Year10,FTETotal) '
> SET SQL1 = SQL1 + 'select distinct r.requestid, function,
> organization,company, '
> SET SQL1 = SQL1 + 'IFNULL(CAST((select group_concat(rtrim(ww.
> firstlast) SEPARATOR '', '') from workforceplan_workers ww where
> ww.requestid = r.requestid) AS CHAR),'''') AS `FirstLastName` '
> SET SQL1 = SQL1 + ',sum(CASE year WHEN ''2006'' THEN fte ELSE 0
> END) as ''2006'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2007'' THEN fte ELSE 0
> END) as ''2007'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2008'' THEN fte ELSE 0
> END) as ''2008'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2009'' THEN fte ELSE 0
> END) as ''2009'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2010'' THEN fte ELSE 0
> END) as ''2010'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2011'' THEN fte ELSE 0
> END) as ''2011'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2012'' THEN fte ELSE 0
> END) as ''2012'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2013'' THEN fte ELSE 0
> END) as ''2013'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2014'' THEN fte ELSE 0
> END) as ''2014'' '
> SET SQL1 = SQL1 + ' ,sum(CASE year WHEN ''2015'' THEN fte ELSE 0
> END) as ''2015'' '
> SET SQL1 = SQL1 + ' ,IFNULL(sum(fte),0) as ftetotal '
> SET SQL1 = SQL1 + ' from workforceplan_request r left join
> workforceplan_fte rf '
> SET SQL1 = SQL1 + ' on r.requestid = rf.requestid left join
> workforceplan_workers sw on sw.requestid = r.requestid '
> SET SQL1 = SQL1 + ' group by r.requestid, function,organization,company
'
> IF WhereClause <> ''
> SET SQL1 = SQL1 + ' WHERE ''''='''' ' + WhereClause
> END IF;
> IF Sort <> ''
> SET SQL1 = SQL1 + ' Order by ' + Sort
> END IF;
>
> CALL SQL1;
>
> SET FirstRec = (Page - 1) * RecsPerPage
> SET LastRec = Page * RecsPerPage + 1
>
> SET RecordCount = count(*) from TempItems
> SET RecordCountMsg = Page + ' pages were requested, only '
>
> SET SQL2 = 'SELECT * FROM TempItems WHERE ID > ' + FirstRec
> SET SQL2 = SQL2 + 'AND ID < ' +LastRec
>
> CALL SQL2;
>
> END$$
>
> DELIMITER ;
>
>
> Melissa

Your problem is that you are trying to numerically add several strings
together and expect a string output. The + operator is NOT used for string
concatenation in MySQL. You have to use the CONCAT() function instead.

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

SET SQL1 = CONCAT(SQL1, '...');

A second pitfall is that you may need to escape certain characters within
a dynamic SQL statement. I mention this now so that you can keep a watch
for it later. Your built SQL statement needs to be as valid as one written
by hand. I see you are already escaping your inner single quotes but you
also need to be on the watch for the other special characters, too.

http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 005180AA852570AF_=--

Re: 5.0.15 Stored Procedures

Would I do the same thing with the following statement.... I still getting
an error.

SET FirstRec = (Page - 1) * RecsPerPage -- i.e. SELECT (Page - 1) *
RecsPerPage INTO FirstRec ;

Also, how do I exec the variable that holds the string? If I do the CALL
SQL1; it says incorrect syntax.

Thanks,

Melissa


----- Original Message -----
From: "Marek"
To:
Sent: Friday, November 04, 2005 10:24 AM
Subject: Re: 5.0.15 Stored Procedures


> In your stored procedure there is third problem:
> AFIK
> SET RecordCount = count(*) from TempItems
> will not work, replace it with
> select count(*) into RecordCount from TempItems
> Marek
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: 5.0.15 Stored Procedures

--=_alternative 00655103852570AF_=
Content-Type: text/plain; charset="US-ASCII"

"Melissa Dougherty" wrote on 11/04/2005 01:12:40
PM:

> Would I do the same thing with the following statement.... I still
getting
> an error.
>
> SET FirstRec = (Page - 1) * RecsPerPage -- i.e. SELECT (Page - 1)
*
> RecsPerPage INTO FirstRec ;
>
> Also, how do I exec the variable that holds the string? If I do the
CALL
> SQL1; it says incorrect syntax.
>
> Thanks,
>
> Melissa
>
>
> ----- Original Message -----
> From: "Marek"
> To:
> Sent: Friday, November 04, 2005 10:24 AM
> Subject: Re: 5.0.15 Stored Procedures
>
>
> > In your stored procedure there is third problem:
> > AFIK
> > SET RecordCount = count(*) from TempItems
> > will not work, replace it with
> > select count(*) into RecordCount from TempItems
> > Marek
> >
> >
>

MySQL has two assignment operators:

If you are in a SET statement, use = or :=
If you are in a SELECT statement, use :=

Also, sorry I missed it last time, all user variables must start with an @
symbol.

http://dev.mysql.com/doc/refman/5.0/en/variables.html

localhost>SET @myvariable = 'testme';
Query OK, 0 rows affected (0.05 sec)

localhost>SELECT @myvariable := CONCAT(@myvariable, ' with a kicker');
+------------------------------------------------------+
| @myvariable := CONCAT(@myvariable, ' with a kicker') |
+------------------------------------------------------+
| testme with a kicker |
+------------------------------------------------------+
1 row in set (0.02 sec)

localhost>SELECT @myvariable;
+----------------------+
| @myvariable |
+----------------------+
| testme with a kicker |
+----------------------+
1 row in set (0.00 sec)




Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 00655103852570AF_=--

Re: 5.0.15 Stored Procedures

What is AFAIK....

I did get all the procedure to compile, all but the CALL statments. The
dynamic SQL is in a variable... now I need to run the variable.

Any suggestions for running the variable?

Melissa


----- Original Message -----
From: "Marek"
To: "Melissa Dougherty"
Sent: Friday, November 04, 2005 5:15 PM
Subject: Odp: 5.0.15 Stored Procedures


> Well
> First I repeat that AFAIK
> Next:
> 1.variables declared INSIDE stored procedures don't have this "@"
> 2.before you may call any procedure it must be in the table with stored
> procedures ie in "mysql.proc"
> 3. calling procedure you have to give parameters so for example -> call
> proc1(@a,@b...)
> 4.I'm not very sure if it is possible to create dynamic procedure being
> inside another procedure but after creating a body of such procedure you
> have to insert it into this table (mysql.proc) and , after finishing - to
> delete from this table. It comes a question if such a "dynamic" procedure
> will be available for its "creator procedure". Try to write and create a
> simple procedure to know what must be a structure of this INSERT
> statement.
> 5. As FirstRec,Page,RecsPerPage are variables and in mentioned
> statements
> there is not any query ; so your format is correct. Only if in statement
> you
> use a information from query you have to use select (with "into") .
> Example:
> select max(year1) into CurrYear from TempItems
>
> Marek
>
>
>
>
> ----- Original Message -----
> From: Melissa Dougherty
> To: ; Marek
> Sent: Friday, November 04, 2005 7:12 PM
> Subject: Re: 5.0.15 Stored Procedures
>
>
>> Would I do the same thing with the following statement.... I still
>> getting
>> an error.
>>
>> SET FirstRec = (Page - 1) * RecsPerPage -- i.e. SELECT (Page - 1) *
>> RecsPerPage INTO FirstRec ;
>>
>> Also, how do I exec the variable that holds the string? If I do the CALL
>> SQL1; it says incorrect syntax.
>>
>> Thanks,
>>
>> Melissa
>>
>>
>> ----- Original Message -----
>> From: "Marek"
>> To:
>> Sent: Friday, November 04, 2005 10:24 AM
>> Subject: Re: 5.0.15 Stored Procedures
>>
>>
>> > In your stored procedure there is third problem:
>> > AFIK
>> > SET RecordCount = count(*) from TempItems
>> > will not work, replace it with
>> > select count(*) into RecordCount from TempItems
>> > Marek
>> >
>> >
>>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Compiling Survey

There is a survey in the Developers Zone @ MySQL.com.
What compiler/IDE do you use for compiling MySQL on Windows?
There are several users that indicate that they compile the bianaries using
Visual Studio .NET
Could one of you post your procedures etc. for using Visual Studio .NET

Thanks


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Compiling Survey

Fredrick

These people may be using the .vcproj files that we are now including with
5.0. As of 5.0, we have switched to using VC 2003 for our official
binaries.

Reggie

> -----Original Message-----
> From: Fredrick Bartlett [mailto:palmtreefrb@earthlink.net]
> Sent: Monday, November 07, 2005 10:15 AM
> To: win32@lists.mysql.com
> Subject: Compiling Survey
>
> There is a survey in the Developers Zone @ MySQL.com.
> What compiler/IDE do you use for compiling MySQL on Windows?
> There are several users that indicate that they compile the bianaries
> using
> Visual Studio .NET
> Could one of you post your procedures etc. for using Visual Studio .NET
>
> Thanks
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=rykr@comcast.net



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Compiling Survey

Great,
Will it be as simple as opening the project and building it? Or will I have
to add some additional assemblies etc to my development system?
----- Original Message -----
From: "Reggie Burnett"
To: "'Fredrick Bartlett'" ;

Sent: Tuesday, November 08, 2005 6:26 AM
Subject: RE: Compiling Survey


> Fredrick
>
> These people may be using the .vcproj files that we are now including with
> 5.0. As of 5.0, we have switched to using VC 2003 for our official
> binaries.
>
> Reggie
>
>> -----Original Message-----
>> From: Fredrick Bartlett [mailto:palmtreefrb@earthlink.net]
>> Sent: Monday, November 07, 2005 10:15 AM
>> To: win32@lists.mysql.com
>> Subject: Compiling Survey
>>
>> There is a survey in the Developers Zone @ MySQL.com.
>> What compiler/IDE do you use for compiling MySQL on Windows?
>> There are several users that indicate that they compile the bianaries
>> using
>> Visual Studio .NET
>> Could one of you post your procedures etc. for using Visual Studio .NET
>>
>> Thanks
>>
>>
>> --
>> MySQL Windows Mailing List
>> For list archives: http://lists.mysql.com/win32
>> To unsubscribe: http://lists.mysql.com/win32?unsub=rykr@comcast.net
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=palmtreeFRB@earthlink.net
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Install MySQL 5.0 Error

I have Windows XP Pro version on my PC and am trying to install MySQL 5.0 on
it.
My problem is that it will not start.

I recieve error as follows

Error Nr. 1045
Access denied for user 'root@localhost' (using password NO)

If a personal firewall is running on your machine, please make sure you have
opened the TCP port 3306 for
connections. Otherwise no client application can connect to the server.
After you have opened the port please
press (Retry) to apply the security settings.

Also on the MySQL Sever Instance Configuration Wizard window
Perpare configuration is green checked
Write configuration file is gree checked
Start service is green checked
Apply security settings is red Xed

The security settings could not be applied.
Error Number 1045
Access denied for user "root@localhost' (using password: NO)

could you please point me in the correct direction to get this up and
running

Thanks


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Compiling Survey

Open the project, click compile. Later versions may introduce a type of
"configure" step that would enable the generation/use of project files for
several different systems (vc 2002, 2003, 2005, etc) but this is something I
am still planning/working on.

> -----Original Message-----
> From: Fredrick Bartlett [mailto:palmtreefrb@earthlink.net]
> Sent: Tuesday, November 08, 2005 11:01 AM
> To: Reggie Burnett; win32@lists.mysql.com
> Subject: Re: Compiling Survey
>
> Great,
> Will it be as simple as opening the project and building it? Or will I
> have
> to add some additional assemblies etc to my development system?
> ----- Original Message -----
> From: "Reggie Burnett"
> To: "'Fredrick Bartlett'" ;
>
> Sent: Tuesday, November 08, 2005 6:26 AM
> Subject: RE: Compiling Survey
>
>
> > Fredrick
> >
> > These people may be using the .vcproj files that we are now including
> with
> > 5.0. As of 5.0, we have switched to using VC 2003 for our official
> > binaries.
> >
> > Reggie
> >
> >> -----Original Message-----
> >> From: Fredrick Bartlett [mailto:palmtreefrb@earthlink.net]
> >> Sent: Monday, November 07, 2005 10:15 AM
> >> To: win32@lists.mysql.com
> >> Subject: Compiling Survey
> >>
> >> There is a survey in the Developers Zone @ MySQL.com.
> >> What compiler/IDE do you use for compiling MySQL on Windows?
> >> There are several users that indicate that they compile the bianaries
> >> using
> >> Visual Studio .NET
> >> Could one of you post your procedures etc. for using Visual Studio .NET
> >>
> >> Thanks
> >>
> >>
> >> --
> >> MySQL Windows Mailing List
> >> For list archives: http://lists.mysql.com/win32
> >> To unsubscribe: http://lists.mysql.com/win32?unsub=rykr@comcast.net
> >
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe:
> > http://lists.mysql.com/win32?unsub=palmtreeFRB@earthlink.net
> >



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org