Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2004
    Posts
    4

    Unanswered: Creating stored procedure from MYPHPADMIN.

    Server version: 5.5.39 OS = Windows 8.1
    I know this is odd but Im trying to create a stored procedure to check the number of records in a table and if it is at my limit add a new table to the merge.
    for testing purposes i am using 5 in reality will be much larger(10M) This will be done with a before insert trigger.
    The purpose is to temporarily get past a database file size limit without recoding entire project. eventually this will all move to a different server and all of the tables will be combined.

    here is what I have so far.
    PHP Code:
    CREATE PROCEDURE autoexpanddb() 
    BEGIN
    DECLARE mylasttablemycount INT DEFAULT 0;
    DECLARE 
    myquery VARCHAR(40);
    set mylasttable:= (select lasttable  from `dbsettings`);
    set myquery concat("set mycount:= select COUNT(*) FROM `funny",lasttable,"`");
    PREPARE stmt FROM myquery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    if 
    mycount%50 THEN
    set mylasttable
    := (mylasttable+1);
    update `dbsettingsSET lasttable mylasttable;
    DECLARE 
    alterstring VARCHAR(255) DEFAULT "alter table funnym1 union = (";
    SET mycount 1;
    WHILE 
    mycount mylasttable
        
    DO
    set alterstring: = concat(alterstring"funny",mycount",");  
    set mycount:= (mycount+1);
       
    END WHILE;
    set alterstring: = concat(alterstring"funny",mycount" insert_method = LAST");
    PREPARE stmt FROM alterstring;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    end if;
    END // 
    DELIMITER is set to //

    getting the following error
    Code:
     #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'myquery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    if mycount%5= 0 THEN
    set myl' at line 7
    Probably something simple, lack of sleep. Thank you in advance if anyone sees what I'm missing.
    Last edited by n1ghtowl; 03-29-15 at 03:07. Reason: updated code, better explanation

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •