How and where do I place stored procedures in a Win environment? 1

[MustangManny]

I use MySQL-front as a GUI.
A bit of a novice at this.
I do not run my own SQL server,mY Webhost does.
Will they have to make the stored procedure for me?

Thank you.

 

[sleipnir214]

The may not be able to. MySQL does not support stored procedures in versions older than 5.x. Version 5.x is available, but only as an alpha release, so I doubt your hosting provider is using it.


You create stored procedures and stored functions using "CREATE PROCEDURE" and "CREATE FUNCTION" queries, repectively. This is documented in the MySQL online manual section on stored procedures and functions

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[MustangManny]

Thank you. Am I correct to assume that writing a stored procedure; literally typing the statement would be done , where? I don't see a place to do so(enter, or type) on my MySQL GUI.

Please forgive my newbieness.

Thanks again!

 

[sleipnir214]

You should have a screen in which you can type a ad-hoc SQL query and run it. You'll do it there.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[PROFESSORSPARKIE]

As long as the output does not need to be to fancy I get fairly good procedure like results from MySql 3.2 using redirected script files. I just test out the needed statements and save them in a .scr file.

A sample load of a database that I show students is:

C:>MYSQL <A:CUST_LOAD.SCR >lpt1:

USE INV_RPT_SPACE;
DROP TABLE PART;
CREATE TABLE PART
(
REC_CODE CHAR(1),
PART_ID CHAR(6),
PART_NAME CHAR(20),
UNIT_COST FLOAT(9,2),
UNIT_SELL FLOAT(9,2),
ON_HAND INT,
ON_ORDER INT,
ORDER_AMT INT
);
EXPLAIN PART;
INSERT INTO PART
VALUES('2', '127912','GADGETS ', 9.12,14.98, 20, 5, 75);
INSERT INTO PART
VALUES('2', '324171','DEALIES ', 0.78, 0.98, 100, 450, 800);
INSERT INTO PART
VALUES('2', '299887','DILLIES ', 0.33, 0.49, 500, 500, 750);
INSERT INTO PART
VALUES('2', '210329','DSCBS ', 0.79, 1.29, 79, 129, 350);
SELECT * FROM PART;
SHOW TABLES;
EXIT


A sample print report would look something like this:
The [select char(27,38,108,49,79)] turns my HP printer into landscape format,

C:>MYSQL <A:CUST_QUERY_REPORT.SCR >lpt1:

USE INV_RPT_SPACE;
SELECT CHAR(27,38,108,49,79) as ' ',
' INVENTORY STATUS REPORT' as " "
FROM PART GROUP BY REC_CODE;
SELECT ' Date=' as ' ',CURDATE() as ' ','Time=' as ' ',CURTIME() as ' '
FROM PART GROUP BY REC_CODE;
SELECT ' ' FROM PART GROUP BY REC_CODE;
SELECT PART_ID,
RPAD(PART_NAME,20,' ') AS 'DESCRIPTION ',
LPAD(FORMAT(ON_HAND,0),5,' ') AS ON_HAN,
LPAD(FORMAT(ON_ORDER,0),5,' ') AS ON_ORD,
LPAD(FORMAT(ORDER_AMT,0),5,' ') AS O_AMT,
LPAD(FORMAT(UNIT_COST,2),6,' ') AS U_COST,
LPAD(FORMAT(UNIT_SELL,2),6,' ') AS U_SELL,
LPAD(FORMAT(ON_HAND * UNIT_COST,2),12,' ') AS ' TOTAL COST',
LPAD(FORMAT(ON_HAND * UNIT_SELL,2),12,' ') AS ' TOTAL_SELL'
FROM PART
ORDER BY PART_ID;
SELECT " ",
" ------------ ------------" as " ";

SELECT " TOTALS " as ' ',
LPAD(FORMAT(SUM(ON_HAND * UNIT_COST),2),12,' ') AS ' ',
LPAD(FORMAT(SUM(ON_HAND * UNIT_SELL),2),12,' ') AS ' '
FROM PART;
EXIT
SELECT CHAR(27,38,108,48,79) as ' ';

Although I can’t get fancy formated reports they do for internal use in a bind.
I hope this may be of some help.