Announcement

Collapse
No announcement yet.

sql tools quote marks

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

  • sql tools quote marks

    sSQLStatement = "SELECT item, bag " + _
    "FROM EDC0001 " + _
    "WHERE item like '" + sNameSearch + "'"

    what I don't understand is the single quote after like and at the end of the line

    like ' " sNameSearch + ' "

    I would like to add "ordered by bag asc " so I don't know how to use the quote marks

    is there a section in help that covers the proper punctuation for creating a string for the sSQLStatement

  • #2
    ==> http://www.w3schools.com/Sql/default.asp

    Best online SQL tutorials I've found.
    Michael Mattias
    Tal Systems (retired)
    Port Washington WI USA
    [email protected]
    http://www.talsystems.com

    Comment


    • #3
      Two simple rules for building SQL strings:

      1. You need to surround any string variable with single quotes.
      2. If a single quote appears in a string, you need to "escape" it by converting it to two single quotes.

      So in your case it would be just be :
      sSQLStatement = "SELECT item, bag " + _
      "FROM EDC0001 " + _
      "WHERE item like '" + sNameSearch + "' order by bag asc "
      Last edited by Stuart McLachlan; 6 Aug 2009, 06:46 AM.

      Comment


      • #4
        FWIW.....

        The delimiter character "can" be anything.. but I have never seen it "be" anything BUT the 'single quote'

        I know there is an ODBC function you can use to query what the current escape character is; so I assume there is an SQL Tools function to do the same.

        MCM
        Last edited by Michael Mattias; 6 Aug 2009, 10:13 AM.
        Michael Mattias
        Tal Systems (retired)
        Port Washington WI USA
        [email protected]
        http://www.talsystems.com

        Comment


        • #5
          what I see

          '" + sNameSearch + "' I see single, double then double, single (quote marks)
          You are saying that at the end there are 3 single quotes?

          ===================
          I got it to work doing this

          sSQLStatement = "SELECT item, bag " + _
          "FROM EDC0001 " + _
          "WHERE item like '" + sNameSearch + "'" + _ 'note how this looks
          "order by item "

          But I will try what you sent. I appreciate the feedback. I have only had sql tools for a couple of days.

          Comment


          • #6
            Tom --

            This isn't a SQL Tools thing, as such. It's really a SQL Statement Syntax thing.

            When you submit a SQL Statement to a database, whether you use SQL Tools or not, it must follow certain rules. The most important thing is the final string, so I find that the easiest way to troubleshoot a SQL Statement is to PRINT it or display it in a MSGBOX. In most cases I PRINT# it to a text file so I can examine it in detail.

            I also find it useful to use PowerBASIC's $SQ (Single Quote) equate. Instead of...
            Code:
            "WHERE item like '" + sNameSearch + "'" + _
            ... I would use...
            Code:
            "WHERE item like " + $SQ+sNameSearch+$SQ + _
            ... to make it clearer that single quotes must surround the string value in the SQL statement.

            Finally, there is a missing space in your statement, before the keyword order, which may or may not be important (depending on how picky the driver is).
            Code:
            "WHERE item like " + $SQ+sNameSearch+$SQ + " order by item " 
                                                        ^                                                
                                                      space
            Again, you should focus more on the final string, rather than the PB syntax that is used to build the string.

            -- Eric Pearson, Perfect Sync Software
            "Not my circus, not my monkeys."

            Comment


            • #7
              ok

              Thanks for info and tips especial $sq and sample they are a lot easier to read with the equate

              Comment


              • #8
                > especial $sq and sample they are a lot easier to read with the equate

                ...and an outstanding example of why sometimes "top speed" is not everything.

                Apparently the cost for the string concatenation is worth the maintainability benefit, huh?

                MCM
                Michael Mattias
                Tal Systems (retired)
                Port Washington WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  so I assume there is an SQL Tools function to do the same
                  Code:
                  Local lDbsNum As Long
                  Local sQ As String
                  sQ = SQL_DatabaseInfoStr(lDbsNum, %DB_IDENTIFIER_QUOTE_CHAR)
                  For MS Access, this returns ` Asc(96).

                  When used in a SQL statement (in an Access database), ODBC error 07002 (too few parameters) occurs; so I use ' Asc(39) instead.
                  Last edited by Christopher Carroll; 8 Aug 2009, 12:15 AM. Reason: Clarify last sentence.

                  Comment


                  • #10
                    Christopher --

                    sQ = SQL_DatabaseInfoStr(lDbsNum, %DB_IDENTIFIER_QUOTE_CHAR)
                    That function asks the database (in this case MS Access) for the %DB_IDENTIFIER_QUOTE_CHAR, which is the character that is used to delimit SQL identifiers such as table names and column names in a SQL statement. In my experience it always works. The SQL string delimiter is always CHR$(39) as far as I know so your program doesn't need to query for that.

                    BTW, that's why I recommend the use of the back-tick character instead of square brackets (see http://www.powerbasic.com/support/pb...ad.php?t=41177). Not only that, it's easier -- especially when writing code to create a statment -- to use one character for both ends of a delimited string.


                    -- Eric Pearson, Perfect Sync, Inc.
                    Last edited by Eric Pearson; 8 Aug 2009, 05:43 AM.
                    "Not my circus, not my monkeys."

                    Comment

                    Working...
                    X