Announcement

Collapse
No announcement yet.

Question Mark in varchar argument creates ERROR_BAD_PARAM_VALUE

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Question Mark in varchar argument creates ERROR_BAD_PARAM_VALUE

    Hiya,

    A client of mine has been using one of my programs (with SQL Tools) in a production environment for about 7-8 years and I can't believe that this is the first time I have seen this. My program waits for text files to arrive in a folder then loads the data from the file into an SQL table. I audit everything so here is a copy of the command that was executed in the PB program:

    SQL_ResSetArray( "EXEC [dbo].[ORD_Detail_Add] @FK_Ord_Header = 44772, @SKU_Ref = '44816', @Quantity = 1, @SKU_Desc = 'Whats Up Doc?'",BYREF aResultSet(), 0, 0)

    This instruction fails with a ERROR_BAD_PARAM_VALUE error.
    When the question mark in the @SKU_Desc argument is removed, the instruction processes fine.
    I can also exec the same statement (with the question mark included) in SSMS and it works fine.
    It also does not matter where in the string the question mark appears. I get the error either way.

    I have a hard time believing that question marks are not allowed in string fields/varchar columns. I also have trouble believing this is the first time this has happened although I am open to the possibility.

    What am I missing?

  • #2
    "?" denotes a parameter in a prepared SQL statement.

    Try escaping it by replacing the "?" with "\?" or "??"

    Comment


    • #3
      Thanks for that Stuart. Tried both of your suggestions but to no avail.

      In addition, am I really using prepared statements when I call SQL_ResSetArray() directly?

      Comment


      • #4
        Originally posted by Jim McLachlan View Post
        Thanks for that Stuart. Tried both of your suggestions but to no avail.

        In addition, am I really using prepared statements when I call SQL_ResSetArray() directly?
        My suggestion was just that (a suggestion) since I haven't come across the problem.

        Probably Eric will pop in soon with a definitive answer.
        (I'll be very interested in what he has to say since this could well bite me one day as well(

        Comment


        • #5
          I appreciate that Stuart. I just pinged Eric to see what he has to say.

          Comment


          • #6
            Hi Jim,

            > SQL_ResSetArray
            > ERROR_BAD_PARAM_VALUE

            I'm pretty sure your version of SQL Tools needs a patch. Send an email to [email protected] and we'll get you fixed up.

            > am I really using prepared statements when I call SQL_ResSetArray() directly?

            No, the SQL_ResSetArray function does not use any prepared statements, it direct-executes the SQL statement that you pass to it.
            "Not my circus, not my monkeys."

            Comment


            • #7
              Eric,

              Does that mean that Jim is using an early version or is it a problem with the current version?

              (I'm using ver 3.14.0.0 Pro dated dated 29 Jun 2012. )

              Comment


              • #8
                Morning Stuart. I replied to Eric off-line. You and I have the same version.

                Comment


                • #9
                  Ditto...

                  -John

                  Comment


                  • #10
                    HI Eric, is there a update to v3.14 standard/pro available?

                    Comment


                    • #11
                      Hi Eric,

                      Just checking in to see how the fix is coming along.

                      Comment

                      Working...
                      X