Use Vbscript To Complete Postgresql Query Using Inputbox Inside Select Statement Follow

0
Avatar
Legacy Poster

     Hi everybody, I am new to VBScript so please be gentle.  I am trying to use a Visual Basic Script that will use an input variable provided at print time by an operator from a Visual Basic InputBox to complete our custom postgresql query...and then use that output to populate my label.  Currently this is the path I am on but I am open to ideas for better methods of achieving my desired result, which is simply to pass in a print-operator typed number to the WHERE clause in my custom postgresql statement prior to the query hitting the database.  This will reduce the time the query takes if it only searches for the one record instead of outputting all possible shipment numbers.  So, here we go...

 

Below is my custom postgresql query:

 

SELECT  shiphead_number "Shipment#",
    cohead_number "Sales Order#",
    REPLACE(cohead_custponumber, '-', '') "Purchase Order#",
    cust_id,
    CASE WHEN POSITION('Rev' in (CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                        END),'-',''))) > 0
                          THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                        END),'-',''), 'REV', 1)
                                ELSE (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                        END),'-','')) 
                     END)) > 0 
        THEN     split_part((CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                       END),'-',''))) > 0
                          THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                       END),'-',''), 'REV', 1)
                            ELSE      (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                       END),'-','')) 
                    END), 'Rev', 1)
            ELSE (CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                       END),'-',''))) > 0
                          THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                       END),'-',''), 'REV', 1)
                            ELSE         (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    
                                        THEN split_part(item_descrip1, ',', 1)
                                            ELSE item_number
                                       END),'-','')) 
                  END)
        END AS "True Part Number",
    CASE WHEN POSITION(' ' in (CASE WHEN POSITION(',' in item_descrip1) > 0 
                    THEN split_part(item_descrip1, ',', 1) 
                        ELSE item_number 
                    END)) > 0
            THEN CONCAT(split_part(CASE WHEN POSITION('-' in (CASE WHEN POSITION(',' in item_descrip1) > 0 
                                        THEN split_part(item_descrip1, ',', 1) 
                                            ELSE item_number 
                                        END)
                                 ) > 0 
                            THEN CONCAT(split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 
                                            THEN split_part(item_descrip1, ',', 1) 
                                                ELSE item_number 
                                        END, '-', 1), 
                                    split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 
                                            THEN split_part(item_descrip1, ',', 1) 
                                                ELSE item_number 
                                        END, '-', 2), 
                                    split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 
                                            THEN split_part(item_descrip1, ',', 1) 
                                                ELSE item_number 
                                        END, '-', 3)
                                   ) 
                                ELSE (CASE WHEN POSITION(',' in item_descrip1) > 0 
                                        THEN split_part(item_descrip1, ',', 1) 
                                            ELSE item_number 
                                    END) 
                        END, ' ', 3),
                    split_part(CASE WHEN POSITION('-' in (CASE WHEN POSITION(',' in item_descrip1) > 0 
                                        THEN split_part(item_descrip1, ',', 1) 
                                            ELSE item_number 
                                        END)) > 0 
                            THEN CONCAT(split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 
                                            THEN split_part(item_descrip1, ',', 1) 
                                                ELSE item_number 
                                        END, '-', 1), 
                                    split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 
                                            THEN split_part(item_descrip1, ',', 1) 
                                                ELSE item_number 
                                        END, '-', 2), 
                                    split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 
                                            THEN split_part(item_descrip1, ',', 1) 
                                                ELSE item_number 
                                        END, '-', 3)
                                   ) 
                                ELSE (CASE WHEN POSITION(',' in item_descrip1) > 0 
                                        THEN split_part(item_descrip1, ',', 1) 
                                            ELSE item_number 
                                    END) 
                        END, ' ', 4)
                  )
                ELSE '-'
    END AS "REV LEVEL",                            
    CASE WHEN POSITION(',' in item_descrip1) > 0
            THEN split_part(item_descrip1, ', ', 2)
                ELSE split_part(item_descrip1, ', ', 1)
    END AS "True Part Description",
        CAST(SUM(shipitem_qty) AS integer) "Total Item Quantity",
    CASE WHEN POSITION(' ' in cohead_shiptoaddress2) <= 1
        THEN CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, chr(13), cohead_shiptocountry)
            ELSE (CASE WHEN POSITION(' ' in cohead_shiptoaddress3) <= 1 
                    THEN CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptoaddress2, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, chr(13), cohead_shiptocountry) 
                        ELSE CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptoaddress2, chr(13), cohead_shiptoaddress3, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, '*', cohead_shiptocountry)
                END)
    END AS "Address(FULL)",
    REPLACE(STRING_AGG((CASE WHEN EXISTS(      
        SELECT string_agg(metric_value, ', ') 
        FROM metric
        WHERE metric_name='LotSerialControl' 
        AND metric_value ='t'
        )
            THEN x.ls_number
                ELSE ''
    END), ', '), '-', '') AS "LotNumbers"
FROM       shiphead
    LEFT JOIN cohead
              ON  shiphead_order_id = cohead_id
                  AND shiphead_order_type = 'SO'
        LEFT JOIN cust
          ON  cust_id = cohead_cust_id
    LEFT JOIN shipitem
              ON  shiphead_id = shipitem_shiphead_id
    LEFT JOIN coitem
              ON  coitem_id = shipitem_orderitem_id 
    LEFT JOIN itemsite
              ON  coitem_itemsite_id = itemsite_id 
    LEFT JOIN item
              ON  itemsite_item_id = item_id
        LEFT JOIN (   
            SELECT ls_number,    
                   shipitem_shiphead_id, 
                   shipitem_orderitem_id 
              FROM invdetail, invhist, shipitem, ls
             WHERE
               (shipitem_invhist_id=invhist_id)
               AND  (invhist_id=invdetail_invhist_id)
               AND  (invdetail_ls_id=ls_id)
       ) x
       ON ( x.shipitem_shiphead_id = shiphead.shiphead_id
            AND
            x.shipitem_orderitem_id = coitem.coitem_id
       )
--WHERE shiphead_number='" & CStr(searchkey) & "'  --JT used for VBScript in BarTender(allows operator input to complete query)
GROUP BY shiphead_number,
     item_descrip1,
     item_number,
     cohead_number,
     cohead_custponumber,
     cohead_shiptoname,
     cohead_shiptoaddress1,
     cohead_shiptoaddress2,
     cohead_shiptoaddress3,
     cohead_shiptocity,
     cohead_shiptostate,
     cohead_shiptozipcode,
     cohead_shiptocountry,
     x.ls_number,
     cohead_cust_id,
     cust_id
ORDER BY shiphead_number DESC;
 
 
If you notice the line with this: "--WHERE shiphead_number='" & CStr(searchkey) & "".   This line, if uncommented and inserted into a vbscript, will provide an InputBox for the print-operator to type or scan in a shipment number.  I want that input variable to be passed into my query before it hits my database, thus reducing the collected records from the query.  Below is my same custom query put into the "OnPrintJobStart" VBScript section:
 
'------QUERY PARAMETERS------------------------------------
'Search for...:
searchkey = InputBox("Enter Shipment Number")
'----------------------------------------------------------
 
connectionparameters = "Driver={PostgreSQL ANSI(x64)};Server=MYIP;Port=5432;Database=MYDB;Uid=admin;Pwd=REMOVED;"
sqlcall = "SELECT  shiphead_number ,cohead_number,REPLACE(cohead_custponumber, '-', ''),cust_id,CASE WHEN POSITION('Rev' in (CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''))) > 0THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''), 'REV', 1)ELSE (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''))  END)) > 0 THEN     split_part((CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''))) > 0THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''), 'REV', 1)ELSE      (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-','')) END), 'Rev', 1)ELSE (CASE WHEN POSITION('REV' in (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''))) > 0THEN split_part(REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-',''), 'REV', 1)ELSE         (REPLACE((CASE WHEN POSITION(',' in item_descrip1) > 0    THEN split_part(item_descrip1, ',', 1)ELSE item_number END),'-','')) END)END,CASE WHEN POSITION(' ' in (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number  END)) > 0THEN CONCAT(split_part(CASE WHEN POSITION('-' in (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END)) > 0 THEN CONCAT(split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 1), split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 2), split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 3)) ELSE (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END) END, ' ', 3),split_part(CASE WHEN POSITION('-' in (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END)) > 0 THEN CONCAT(split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 1), split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 2), split_part(CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END, '-', 3)) ELSE (CASE WHEN POSITION(',' in item_descrip1) > 0 THEN split_part(item_descrip1, ',', 1) ELSE item_number END) END, ' ', 4))ELSE '-'END,CASE WHEN POSITION(',' in item_descrip1) > 0THEN split_part(item_descrip1, ', ', 2)ELSE split_part(item_descrip1, ', ', 1)END,CAST(SUM(shipitem_qty) AS integer),CASE WHEN POSITION(' ' in cohead_shiptoaddress2) <= 1THEN CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, chr(13), cohead_shiptocountry)ELSE (CASE WHEN POSITION(' ' in cohead_shiptoaddress3) <= 1 THEN CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptoaddress2, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, chr(13), cohead_shiptocountry) ELSE CONCAT(cohead_shiptoname, chr(13), cohead_shiptoaddress1, chr(13), cohead_shiptoaddress2, chr(13), cohead_shiptoaddress3, chr(13), cohead_shiptocity, ', ', cohead_shiptostate, ' ', cohead_shiptozipcode, '*', cohead_shiptocountry)END)END,REPLACE(STRING_AGG((CASE WHEN EXISTS(SELECT string_agg(metric_value, ', ') FROM metric WHERE metric_name='LotSerialControl' AND metric_value ='t')THEN x.ls_number ELSE ''END), ', '), '-', '')FROM       shiphead LEFT JOIN cohead ON  shiphead_order_id = cohead_id AND shiphead_order_type = 'SO'LEFT JOIN cust ON  cust_id = cohead_cust_id LEFT JOIN shipitem ON  shiphead_id = shipitem_shiphead_id LEFT JOIN coitem ON  coitem_id = shipitem_orderitem_id  LEFT JOIN itemsite ON  coitem_itemsite_id = itemsite_id LEFT JOIN item ON  itemsite_item_id = item_id LEFT JOIN (SELECT ls_number,shipitem_shiphead_id,shipitem_orderitem_id FROM invdetail, invhist, shipitem, ls WHERE(shipitem_invhist_id=invhist_id) AND  (invhist_id=invdetail_invhist_id) AND  (invdetail_ls_id=ls_id)) x ON ( x.shipitem_shiphead_id = shiphead.shiphead_id  AND x.shipitem_orderitem_id = coitem.coitem_id) WHERE shiphead_number='" & CStr(searchkey) & "' GROUP BY shiphead_number,item_descrip1,item_number,cohead_number,cohead_custponumber,cohead_shiptoname,cohead_shiptoaddress1,cohead_shiptoaddress2,cohead_shiptoaddress3,cohead_shiptocity,cohead_shiptostate,cohead_shiptozipcode,cohead_shiptocountry,x.ls_number,cohead_cust_id,cust_id ORDER BY shiphead_number DESC;"
 
Set conn = CreateObject("ADODB.Connection")
conn.Open connectionparameters
Set recs = CreateObject("ADODB.Recordset")
recs.Open sqlcall, conn, 1, 3
MsgBox("It Worked!!!  Now I just need to figure out how to get this to output the results to variables that I can call into label fields later")
 
 
Ok, so when I use the above in a VBScript and test it....the test works and I get the MsgBox test at the end of the script.  However, I don't know where the output is stored, how to store it if I need to, and how to pull that output into the specific fields I want on my label.  Any ideas or guidance to complete this would be greatly appreciated.  

7 comments

0
Avatar
Ian Cummings
Moderator
Comment actions Permalink

Why are you not using the built-in functionality of BarTender to connect to the database and use the queryprompt functionality to prompt the user at print time for the search criteria value?  Note that BarTender's database capability does also support custom SQL statements that may include the queryprompt reference in them if you wish.

 

Take a look at these videos:

 

https://www.youtube.com/watch?v=WUBXhAnhrMU

 

https://www.youtube.com/watch?v=FRcASc2CnD4

0
Avatar
Legacy Poster
Comment actions Permalink

Hi Ian, thanks for the reply.  I tried using the BT built in DB connection in conjunction with the query prompt, but I was unable to set the query prompt filters because I am using a custom SQL statement.  I thought this was unusual, so I called tech support and was told I cannot use the built in query prompt if I am using my custom SQL statement.  Was I informed incorrectly?  If so, how can I set the query prompt to search a specific column from my query?  You can set it by going to the "Filters" tab, but I don't have the "Filters" tab when I use a custom SQL statement.  

0
Avatar
Ian Cummings
Moderator
Comment actions Permalink

Yes, unfortunately you got incorrect information.  To view the syntax used for such query prompt references, create a database connect, build a query using a named query prompt, and then switch to the custom SQL view.  Example:

 

SELECT `CONTCOMP` FROM `CUSTOMER` WHERE `CONTCOMP` = '?QueryPrompt1'

0
Avatar
Legacy Poster
Comment actions Permalink

Yep, that was incredibly easy compared to what I was getting into.  Thanks a bunch for the insight.  I simply added "WHERE shiphead_number = '?ShipmentNumber' AND shipitem_qty = '?Quantity'".  These both allow me to prompt the operator for the shipment number and quantity prior to firing my query.  Thanks again!

0
Avatar
Ian Cummings
Moderator
Comment actions Permalink

Glad that worked out for you.  Trying to debug the VB script wouldn't have been fun for anyone... :)

0
Avatar
Ian Cummings
Moderator
Comment actions Permalink

FYI: BarTender 2016 R3 now offers native support for PostgreSQL databases: http://www.seagullscientific.com/support/service-releases-legacy-versions/2016/

0
Avatar
Legacy Poster
Comment actions Permalink

Awesome!  Thanks for the update.  I am going to check out the new features today.  

Please sign in to leave a comment.