1. 

   jazzc, Member

   when I click the add report button, system ask me to create a store pro, when I enter the following coding CREATE PROCEDURE sp_po() BEGIN select pono, bu, dept, dor, quote1, quote2, quote3, item1, desc1, pono1, pod1, qty1, up1, soh1, u1, q1, q1item1, q2item1, q3item1, edd1, q1total, q2total, q3total, nd, r, adj, no, am, ct, poyes, poyesp, pon, byes, byesp, bno, item2, desc2, pono2, pod2, qty2, up2, soh2, u2, q2, q1item2, q2item2, q3item2, edd2, item3, desc3, pono3, pod3, qty3, up3, soh3, u3, q3, q1item3, q2item3, q3item3, edd3, item4, desc4, pono4, pod4, qty4, up4, soh4, u4, q4, q1item4, q2item4, q3item4, edd4, item5, desc5, pono5, pod5, qty5, up5, soh5, u5, q5, q1item5, q2item5, q3item5, edd5, item6, desc6, pono6, pod6, qty6, up6, soh6, u6, q6, q1item6, q2item6, q3item6, edd6, item7, desc7, pono7, pod7, qty7, up7, soh7, u7, q7, q1item7, q2item7, q3item7, edd7, item8, desc8, pono8, pod8, qty8, up8, soh8, u8, q8, q1item8, q2item8, q3item8, edd8, remark from tb_po where pono = id AND postatus !='Approved' AND pono NOT IN (SELECT pono FROM tb_postatus) END $$, I got this error -- 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 'END' at line 6
   Your procedure has not been saved to database.
   Fix the error or revert back to original. what i want is, create a report with parameter and the parameter should be pass through via PHP
2. 

   jazzc, Member

   I fixed the above problem by using the following coding

   CREATE PROCEDURE sp_po(in_id int(6))
   
   BEGIN
   
   select pono, bu, dept, dor, quote1, quote2, quote3, item1, desc1, pono1, pod1, qty1, up1, soh1, u1, q1, q1item1, q2item1, q3item1, edd1, q1total,  q2total, q3total, nd, r, adj, no, am, ct, poyes, poyesp, pon, byes, byesp, bno, item2, desc2, pono2, pod2, qty2, up2, soh2, u2, q2, q1item2, q2item2, q3item2, edd2, item3, desc3, pono3, pod3, qty3, up3, soh3, u3, q3, q1item3, q2item3, q3item3, edd3, item4, desc4, pono4, pod4, qty4, up4, soh4, u4, q4, q1item4, q2item4, q3item4, edd4, item5, desc5, pono5, pod5, qty5, up5, soh5, u5, q5, q1item5, q2item5, q3item5, edd5, item6, desc6, pono6, pod6, qty6, up6, soh6, u6, q6, q1item6, q2item6, q3item6, edd6, item7, desc7, pono7, pod7, qty7, up7, soh7, u7, q7, q1item7, q2item7, q3item7, edd7, item8, desc8, pono8, pod8, qty8, up8, soh8, u8, q8, q1item8, q2item8, q3item8, edd8, remark
   
   from tb_po
   
   where pono = in_id AND postatus !='Approved' AND pono NOT IN (SELECT pono FROM tb_postatus);
   
   END
   
   $$

   , but when I execute it I got another error "PROCEDURE sp_po already exists", so I 've no idea how do I create a report with this coding?
3. 

   myDBR Team, Key Master

   When you have created a stored procedure and you want to modify it, you need first to drop it. Add the conditional drop statement in front of the create statement. myDBR will do this automatically for you once you have attached the procedure as a report.

   DROP PROCEDURE IF EXISTS sp_DBR_po
   
   $$
   
   CREATE PROCEDURE sp_DBR_po(in_id int(6))
   
   BEGIN
   
   select pono...
   
   

   Please also note that report stored procedure names in myDBR by default starts with 'sp_DBR'.

   --
   myDBR Team
4. 

   jazzc, Member

   yeah, i can create a report now, but when i entering the parameter i got another error "Could not execute the report. There was an error in the report. Table 'mydbr.tb_po' doesn't exist", and also does myDBR has report IDE, so that i can design it by myself?
5. 

   myDBR Team, Key Master

   Your report fecthed data from "from tb_po", which means mydbr.tb_po. You might want to select from your own database "from mydatabase.tb_po" (substitute mydatabase with your own database).

   also does myDBR has report IDE, so that i can design it by myself

   Which IDE are you referring to? myDBR does your report layout automatically. You do not need to design it yourself. You can if you want though, just look at the documentation for examples.

   --
   myDBR Team
6. 

   jazzc, Member

   can you please tell me how to fix the "mydbr.tb_po" issue? and referring to report IDE, can i design the position of each field for the report?
7. 

   myDBR Team, Key Master

   See the previous answer. You are fetching data from mydbr-database which does not have your "tb_po" table. Use the database.table notation to fetch the data from correct database

   can i design the position of each field for the report?

   Yes you can. See manual for various options (different layouts, templates). Usually this is done automatically for you though.

   --
   myDBR Team
8. 

   jazzc, Member

   I'm very sorry to bother for many times especially on question one, however, I do not know how to change the database because I just use the SQL statement in the report edit, so I really don't know what to do?
9. 

   myDBR Team, Key Master

   I'm very sorry to bother for many times especially on question one, however, I do not know how to change the database because I just use the SQL statement in the report edit, so I really don't know what to do?
   

   The answer was in the previous post.

   When you run a query:
   

   select pono..
   
   from tb_po;

   The query is run in mydbr-database and expects the 'tb_po' table to be in mydbr-database. If your tb_po-table is in different database (like 'mydatabase'), you should specify the query as:

   select pono..
   
   from mydatabase.tb_po;

   So you do not actually change database, you just refer to the tables in another database.

   --
   myDBR Team
10. 

    jazzc, Member

    thanks for fixing the problem, may I know how can I delete the "stored procedure" from the system as I've made a mistake to create the wrong one before?
11. 

    myDBR Team, Key Master

    Just use drop procedure command:

    DROP PROCEDURE IF EXISTS sp_po

    --
    myDBR Team

Reply

You must log in to post.