Untitled Document
Results 1 to 4 of 4

Thread: Problem with Quotes in SQL Write-Back

  1. #1

    We been using the SQL version of FedEx for about 2 weeks. Today, we have noticed a pretty substantial issue. Whenever we write back back from ShipRush, we are doing so by inserting a fairly comprehensive set of fields into a new record in an SQL Table. Our SQL statement looks like this:

    INSERT INTO dbo.TempTrackingExport(InvoiceNumber, UPSDate, TimeStamp, QBFileAsName, ServiceType, PubBoxCost, PubTotalShipmentCost, DiscBoxCost, DiscTotalShipmentCost, TrackingNumber, VoidIndicator, Weight, Customer, Street1, City, State, ZipCode, Country, Division, Ref2, BillingOption, ESMAccount, EnteredAs, ShipRush)
    SELECT %RecordID% AS InvoiceNumber, CONVERT(varchar, Shipping.dbo.FedExExpressCurrentShippingDate(), 101) AS UPSDate, GETDATE() AS TimeStamp, '%ToName%' AS QBFileAsName, '%ShippingService%' AS ServiceType, %ShippingCharges% AS PubBoxCost, %ShippingCharges% AS PubTotalShipmentCost, %ShippingCharges% AS DiscBoxCost, %ShippingCharges% AS DiscTotalShipmentCost, '%TrackingNumber%' AS TrackingNumber, 'N' AS VoidIndicator, %ShippingWeight% AS Weight, '%ToName%' AS Customer, '%ToStreet1%' AS Street1, '%ToCity%' AS City, '%ToStateOrProvince%' AS State, '%ToZIP%' AS ZipCode, '%ToCountry%' AS Country, Shipping.dbo.FedExOneRateOrderSource() AS Division, '%RecordID%' AS Ref2, 'Bill Sender' AS BillingOption, Shipping.dbo.FedExOneRateSourceAccounts() AS ESMAccount, %RecordID% AS EnteredAs, 1 AS ShipRush
    We have discovered that because of the way that ShipRush does exports - first "printing" the ShipRush variables to literal strings and then executing the SQL statement with that literal text embedded - that a quote or apostrophe in a field such as %ToName% will cause the export to fail throwing an invalid syntax error. Today's example was from a shipment to a person with the last name O'Neal.

    The major problem with this is it cannot be effectively handled in the SQL statement. If we were dealing with an actual SQL variable there are plenty of ways in could be handled with functions or procedures, but because this data has already been downgraded into a literal text string by the time the SQL statement executes, none of those options are available without first encapsulating the text within two single quotes. Encapsulating the text within two single quotes will not work however, because SQL will read the literal quote within the text string as the closing quote.

    For example, the portion of the statement above:
    CONVERT(varchar, Shipping.dbo.FedExExpressCurrentShippingDate(), 101) AS UPSDate, GETDATE() AS TimeStamp, '%ToName%' AS QBFileAsName, '%ShippingService%' AS ServiceType,
    CONVERT(varchar, Shipping.dbo.FedExExpressCurrentShippingDate(), 101) AS UPSDate, GETDATE() AS TimeStamp, 'Tom O'Neal' AS QBFileAsName, 'FedEx 2Day?' AS ServiceType,
    at the time the time the SQL statement executes. The extra quote will cause it to throw a syntax error.

    If you try to wrap '%ToName%' in a SQL function to handle the quotes like master.dbo.EscapeQuotes('%ToName%') it will still fail because you are effectively running master.dbo.EscapeQuotes('Tom O'Neal') wherein the function will have a syntax error because it sees too many quotes.

    You can try this yourself. Just try to execute SELECT REPLACE('Tom O'Neal',CHAR(39), CHAR(39) + CHAR(39)) in any version of MS SQL and it will fail because there are too many quotes and there is no way to run a function or stored procedure against a literal string without encapsulating it in quotes.

    There are escape characters that can be used when a programmer is typing a literal string into a SQL statement, however this is also not viable because it is ShipRush that is injecting its variables as literals into the SQL statement and such escape characters would have to directly precede the character being escaped.

    With my 13 years of experience as a SQL developer I can say with a very high degree of certainty that the is no way that this issue can be resolved through changes to the SQL statement that does the write back. The only way this can be resolved is through Z-Firm making changes to ShipRush.

  2. #2

    Join Date
    Apr 2004
    SYSOPS is offline
    Thank you for the detailed write up. Case 68811 has been created for our developers to review further. We will update back to this thread with new information\updates.

    Thank you,
    Have Questions? Ask the ShipRush Knowledge Base: https://shiprush.com/kbase
    Reply With Quote

  3. #3

    Join Date
    Apr 2004
    Blog Entries
    SYSOPR is offline
    ZF Case 68811

    Thank you very much for the detailed write up. Your points are understood, and well presented.

    We should, in a day or two (business day or two) have a build that passes the parameters as... parameters. (E.g. The SQL statement is converted on the fly to a real parameterized SQL call)

    Thank you again for the write up. (Other users just pass ID's around, you are the first user to use the system so richly.)
    The Blog: http://forums.zfirm.com/blog.php

    YouTube channel: http://youtube.com/shiprush

    Thank you for using ShipRush!
    Reply With Quote

  4. #4
    Thank you very much for the response and taking action on this.

    If you were at the same time also able to incorporate Case #68675 wherein I requested Reference1/Package1, Reference2/PO Number, Reference3/Invoice, and ShipperAcct/FedEx Acct. as additional fields available for the write back, I would greatly appreciate it.

    I understand that Case #68676 wherein I asked that the raw scale weight be available for the write back is a much bigger ask from you. However, it would be very useful for us because we have a SQL job running to check the expected weight of our shipment against the actual weight that gets written back (from systems other than ShipRush) and send a notification to our shippers if there is too much of a variance. This is very useful for identifying if there are incorrect items or the wrong number of items included in an order when it ships. It would be great to also be able do this for shipments shipped with ShipRush.
    Last edited by TimLevine; 10-12-2018 at 10:04 PM.
    Reply With Quote


Posting Permissions

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