Announcement

Collapse

Maintenance

The forum could be offline for 30-60 minutes in the very near future for maintenance (said 3pm Pacific). I was behind on getting this notice. I do apologize.
See more
See less

SQL_Tools INSERT INTO problem

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

  • SQL_Tools INSERT INTO problem

    I'm having a heck of a time with syntax errors. If I make the statment


    Code:
    sqlstmnt = "INSERT INTO VC (AudioIndex) VALUES (" + ai(1) +")"
    It will insert this column.

    But, If I try to add more fields, it won't:

    Code:
    sqlstmnt = "INSERT INTO VC (AudioIndex,Filename,LongFilename) VALUES (" + ai(1) +"," +_
            ai(2) + "," + ai(3) +")"
    The column names are correct, including case. There are actually 47 columns, but as I understand it, I do not have to send all.

    The last example gives the sql syntax error in the statement. The statement comes out as such:
    Code:
    INSERT INTO VC (AudioIndex,FileName,LongFilename) VALUES(5,,C:\DRIVE E\F\Checked MP3 Files\5th Dimension\01 Up-Up and Away = The 5th Dimension.mp3)
    I've also tried placing "','" (Single quotes inside the double quotes as in the sample SQL_Tools programs to no avail. ai() are string valuse.. The database fields referenced ate Numeric, TEXT and TEXT
    Barry

  • #2
    Barry --

    > Single quotes inside the double quotes

    You definitely need single quotes around strings in a SQL statement. The final result should look something like...
    Code:
    INSERT INTO VC (AudioIndex,FileName,LongFilename) VALUES(5,'C:\DRIVE E\F\Checked MP3 Files\5th Dimension\01 Up-Up and Away','The 5th Dimension.mp3')
    ...although I don't think those are quite the right strings. You need 3 values (AudioIndex,FileName,LongFilename) and 'The 5th Dimension.mp3' doesn't look like a LongFileName to me.

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

    Comment


    • #3
      Strings should be inserted as strings - use single quotes as in
      Code:
      sqlstmnt = "INSERT INTO VC (AudioIndex,Filename,LongFilename) VALUES ('" + ai(1) +"','" +_
              ai(2) + "','" + ai(3) +"')"

      Comment


      • #4
        Originally posted by Eric Pearson View Post
        Barry --

        > Single quotes inside the double quotes

        You definitely need single quotes around strings in a SQL statement. The final result should look something like...
        Code:
        INSERT INTO VC (AudioIndex,FileName,LongFilename) VALUES(5,'C:\DRIVE E\F\Checked MP3 Files\5th Dimension\01 Up-Up and Away','The 5th Dimension.mp3')
        ...although I don't think those are quite the right strings. You need 3 values (AudioIndex,FileName,LongFilename) and 'The 5th Dimension.mp3' doesn't look like a LongFileName to me.

        -- Eric Pearson, Perfect Sync, Inc.
        There is a empy item for filename in there that I failed to show:
        (5,,'D:\DRIVE E ...
        I'll try the single quotes again. I had asciiz strings in there first and have strings, now.

        Ok, that works... single quote around strings (or items going to a text filed BUT NOT GOING TO A NUMERIC field.
        Now to find the chaulk to write this 100 times.
        Last edited by Barry Erick; 20 Nov 2008, 07:40 AM.
        Barry

        Comment


        • #5
          Barry --

          quotes around strings (...) but not (...) numerics
          Old friend, you have been using BASIC even longer than I have. Does that rule... ring a bell?

          Seriously, all you have to remember is that within SQL statements, strings need single quotes. Some database drivers are more forgiving than others; they will convert the string '1' to the numeric value 1, and so on. But overall if you put (single) quotes around strings and don't put them around numerics, you'll do fine.

          -- Eric
          "Not my circus, not my monkeys."

          Comment


          • #6
            >There is a empy item for filename in there that I failed to show:
            >(5,,'D:\DRIVE E ...

            ===>>> "(5, NULL, 'D:\Drive E...."

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

            Comment


            • #7
              Originally posted by Barry Erick View Post
              Ok, that works... single quote around strings (or items going to a text filed BUT NOT GOING TO A NUMERIC field.
              Now to find the chaulk to write this 100 times.
              Also you need to take care of special characters that have special meaning in SQL. Similar to text that contains greater than/less than (< >) that is being placed in HTML.

              In SQL apostrophes need to be replaced
              Code:
                 INSERT INTO Contact (lastname) VALUES ('O'Brian')
              Should be corrected to

              Code:
                 INSERT INTO Contacts (lastname) VALUES ('O''Brian')
              That's is two single quotes after the O, not a double quote.

              Comment


              • #8
                Originally posted by Eric Pearson View Post
                Barry --



                Old friend, you have been using BASIC even longer than I have. Does that rule... ring a bell?


                -- Eric
                Yes, it is somthing I knew. Problem is, following 3 surgeries in one week (a few years ago), I forgot many things and have to re-learn much. It's just little rules that are locked in somewhere and have to be knocked loose.

                Thanks for the (obvious) tip!
                Barry

                Comment


                • #9
                  Never mind... fixed.. see below


                  Now it's a Error 07001 .. missing paramater.. expects 1

                  Code:
                      sqls = "DELETE FROM VC WHERE AudioIndex = " + "Indexx"
                                  ? sqls
                                  res = SQL_Stmt (%SQL_STMT_IMMEDIATE, sqls)
                  VC is the table (all inserts are fine) and AudioIndex is the numeric column and the first column. The index passed IS correct. res returns -1. I have tried encosing the field in single quotes, but looking at the QSL_Tools Del routine, it is not used there so I leave it here. If I change "Indexx" to "'sIndex'", which is the index in string form that Index is derived from with VAL
                  Code:
                  Indexx = val(sIndex)
                  Never mind... brain * again. Fixed
                  Last edited by Barry Erick; 23 Nov 2008, 10:15 PM. Reason: fixed
                  Barry

                  Comment


                  • #10
                    Barry, FWIW, I wasn't trying to give you a hard time, I was pointing out the similarities between PB syntax and SQL syntax as a way to help you sort things out. Of course it's the differences that will getcha...

                    -- Eric
                    "Not my circus, not my monkeys."

                    Comment


                    • #11
                      It's a little more work at the source code level, but you can avoid all the "single quotes here, no quotes there" stuff by using a parameterized statement and binding the values.

                      Code:
                      sql = "INSERT INTO tablename (col1, col2, col3)  VALUES (?, ?, ?) "
                      
                         Sql_Prepare (or whatver the SqlTools syntax is) statement
                         FOR n = 1 TO 3 
                            Bind Parameter 'n' to statement with a bind variable 
                         NEXT
                         FOR each row to be inserted 
                                param1 = value1       ' params and values are "natural" datatypes 
                                param2 = value2      ' that is, the same datatypes as the rest of your program 
                                param3  = value3 
                               SQL_Execute  statement 
                          NEXT
                          Clean up here
                      If there is only one row to insert, the amount of work done by the DBMS is a wash.... since a direct execute does a prepare and binds the values from the text of the sql statement anyway.....
                      ... but if you have many rows to insert, preparing the statement and using bound parameters is way more efficient, since the DBMS only has to parse and prepare the statement ONCE.

                      Hell, it's a thought...

                      MCM
                      Last edited by Michael Mattias; 24 Nov 2008, 08:47 AM.
                      Michael Mattias
                      Tal Systems Inc. (retired)
                      Racine WI USA
                      [email protected]
                      http://www.talsystems.com

                      Comment


                      • #12
                        I use a sql prep function like this:

                        Code:
                        FUNCTION sqlPrep(s AS STRING) AS STRING      
                        
                          IF TRIM$(s) = "" THEN
                            FUNCTION = "NULL"
                          ELSE    
                            REPLACE $sq WITH $sq+$sq IN s
                            FUNCTION = $sq+s+$sq     
                          END IF    
                        
                        END FUNCTION
                        v="shawn"
                        sqlStatement="insert into tableName(myName) values("+sqlPrep(v)+")"

                        Comment


                        • #13
                          Code:
                          IF TRIM$(s) = "" THEN
                              FUNCTION = "NULL"
                              ......
                          Depending on application, a string of all spaces may not be the same thing as NULL.
                          Michael Mattias
                          Tal Systems Inc. (retired)
                          Racine WI USA
                          [email protected]
                          http://www.talsystems.com

                          Comment


                          • #14
                            Originally posted by Michael Mattias View Post
                            It's a little more work at the source code level, but you can avoid all the "single quotes here, no quotes there" stuff by using a parameterized statement and binding the values.
                            This also protect against SQL injections. i.e. Where the user can type an entry and cause SQL to execute including

                            deleting all data
                            changing data
                            login with admin rights
                            capture a copy of all data

                            Comment

                            Working...
                            X