MySQL Stored Proc & Temp Table

  1. 6 weeks ago

    Dale D

    Dec 7 Pre-Release Testers, Xojo Pro

    Hello DB gurus, I need your help.

    I am working on a receiving report where I need to print multiple QR codes of each item. Example:

    Receiving Report: 00001
    
    Items     Quantity
    ---------    -------------
    Item A         15
    Item B         10
    Item C         25
    

    I am using MySQL db and the above sample is stored in a table called RRDetails. Of course, the structure is just an abstract.

    Although I only have 3 items, I need to print 50 qr codes ( 15 for Item A, 10 for Item B and 25 for Item C). I was told that in order to accomplish this, I have to write a stored procedure in mysql, that stored proc would create a temporary table and then be populated from the number of quantities of the filtered rows from RRDetails.

    Anyone can lead me to the solution?

    Thank you all!

  2. Paul B

    Dec 7 Pre-Release Testers, Xojo Pro Europe (UK, Somerset)

    I am unsure why you need a temporary table, you have all you need above. Could you not loop through the recordset, first to last and use another loop to print the labels as you know how many you need? Pseudo code below (its not XOJO code).

    while not end of recordset
      for count  as integer = 1 to [quantity]
        print the label for current Item
      next
    
      move to next record
    
    end while
  3. Dale D

    Dec 7 Pre-Release Testers, Xojo Pro

    Hi Paul, thanks for the reply. I forgot to say that I'll be printing these qr codes through a 3rd party reporting tool.

  4. Paul B

    Dec 7 Pre-Release Testers, Xojo Pro Europe (UK, Somerset)

    Have you actually got 15 records of Item A? If so you could consider a view, i.e. if your table is something like below and you just use a 'distinct' query to get your example. If so you could consider creating a view and point your reporting tool at that.

    Item A - 1
    Item A - 2
    Item A - 3
    ...
    Item A - 15
    Item B - 1
    ...
    Item B - 10
    Item C - 1
    ...
    Item C -25

  5. Dale D

    Dec 7 Pre-Release Testers, Xojo Pro

    No, Paul, the actual records (based on my example above) are only 3. I did not use distinct. In a way, I need to multiple each record based on their respective receipt quantity.

    The reporting tool that I use has no problem at all, except that it relies to the number of rows (records) it can access.

  6. Paul B

    Dec 7 Pre-Release Testers, Xojo Pro Europe (UK, Somerset)
    Edited 6 weeks ago

    Stored procedures may be the way to go depending on where your reporting tool is located (if desktop i may have been tempted to write out a temporary file and use that), take a look at the example here which may give you a few pointers to do what you need (with a few changes).

    https://stackoverflow.com/questions/8812470/how-to-insert-multiple-rows-based-on-a-quantity-value-in-one-row

  7. Dale D

    Dec 7 Pre-Release Testers, Xojo Pro

    Paul, thank you for keeping through with me on this one and for pointing me to this link. Obviously, I am not DB savvy but am willing to learn as the need arises. I will study the link that you provided. Kudos to you! Big thanks!

  8. Eric B

    Dec 7 Pre-Release Testers, Xojo Pro Maryland, USA

    I've done this by creating a table named Copies. It has a single column named 'NumCopies'. Add records to this table, 1 through 25 (or the maximum number you may need.)

    Then use a query or view like this:
    SELECT * FROM [YourTable], Copies WHERE Copies.NumCopies<=[YourTable.Quantity]

    This has the effect of multiplying the records. You should add an ORDER BY so the QR codes print in the order you expect.

or Sign Up to reply!