SQL_Tools INSERT INTO problem

[Barry Erick]

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

sqlstmnt = "INSERT INTO VC (AudioIndex) VALUES (" + ai(1) +")"

It will insert this column.

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

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:

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

[Eric Pearson]

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...

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.

[Andre Smit]

Strings should be inserted as strings - use single quotes as in

sqlstmnt = "INSERT INTO VC (AudioIndex,Filename,LongFilename) VALUES ('" + ai(1) +"','" +_
        ai(2) + "','" + ai(3) +"')"

[Barry Erick]

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...

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.

[Eric Pearson]

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

[Michael Mattias]

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

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

MCM

[Brian Chirgwin]

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

   INSERT INTO Contact (lastname) VALUES ('O'Brian')

Should be corrected to

   INSERT INTO Contacts (lastname) VALUES ('O''Brian')

That's is two single quotes after the O, not a double quote.

[Barry Erick]

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 Erick]

Never mind... fixed.. see below


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

    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

Indexx = val(sIndex)

Never mind... brain * again. Fixed

[Eric Pearson]

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

[Michael Mattias]

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.

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

[Shawn Anderson]

I use a sql prep function like this:

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)+")"

[Michael Mattias]

IF TRIM$(s) = "" THEN
    FUNCTION = "NULL"
    ......

Depending on application, a string of all spaces may not be the same thing as NULL.

[Brian Chirgwin]

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