#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    74
    Rep Power
    13

    CfSpreadsheet :xls -> MySql


    Hi
    I am working on project where I need to load data into 21 different tables and all the data was given to me in spreadsheets.

    I am using CFSPREADSHEET to read the xl file and loop the results over an insert statement to get the data in the table.

    i decided to put together a helper app [no award winner but will get the job done, i think] to speed this process up and I hit a snag when i try to loop over the insert statement.

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
    Here is the error:
    The error occurred in C:/ColdFusion10/cfusion/wwwroot/test/spreadsheet/rdSpreadSheet.cfm: line 41

    39 : <cfquery name='ins' datasource='inspections'>
    40 : INSERT INTO #tableName#(#colNames#)
    41 : Values(#colValues#)
    42 : </cfquery>
    43 : </cfoutput>

    VENDORERRORCODE 1064
    SQLSTATE 42000
    SQL INSERT INTO case_tbl(AGENT,CASE_CLOSE_DATE,CASE_DATE,CASE_FILE_NUMBER,CASE_ID,CASE_TITLE,COMPLETE,GRAND_JURY,INS PECTION_ID,LOCATION,LOCKED,OFFICE_ID,STATUS,SYNOPSIS) Values(''#AGENT#'',''#CASE_CLOSE_DATE#'',''#CASE_DATE#'',''#CASE_FILE_NUMBER#'',#CASE_ID#,''#CASE_TI TLE#'',#COMPLETE#,''#GRAND_JURY#'',#INSPECTION_ID#,''#LOCATION#'',#LOCKED#,#OFFICE_ID#,''#STATUS#'', ''#SYNOPSIS#'')
    DATASOURCE DSN

    Using CF10 [STAND ALONE]
    WIN7 HOME PREMIUM

    mYSQL 5

    iF your wondering why I did not use <cfqueryparam> for colNames and colValues its because when i did i recieved errors and the actual values would not appear. even using the list attrib.

    any advice would be helpful.

    tia
    J. Birdsell,
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    You're using a string and not the evaluated values for your Values() line. Try Values(Evaluate(colValues)).
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    iF your wondering why I did not use <cfqueryparam> for colNames and colValues its because when i did i recieved errors and the actual values would not appear. even using the list attrib.
    cfqueryparam should be used on the individual values.

    You really, really, really should use cfqueryparam because your current code leaves your database wide open to sql injection attacks.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    74
    Rep Power
    13
    Thanks for the input guys.

    Kiteless, I will give your suggestion a try.

    cf searching : normally I do use cfqueryparam all the time. This was one of those times when I was stripping things out one at time to try and find the root cause of the errror.

    To that extent, it appears that mysql acts alot like access when doing mass updates. it chokes on fields with null values.

    I had to make sure every cell in the spread sheet had a value then and only then would it properly insert the data to the table.

    again, thanks for the input, its greatly appreciated.
    J. Birdsell,
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    Originally Posted by jbird4k
    To that extent, it appears that mysql acts alot like access when doing mass updates. it chokes on fields with null values.

    I had to make sure every cell in the spread sheet had a value then and only then would it properly insert the data to the table.
    I am not so sure ;-) I suspect it was a problem with how the code constructed the sql statements.

    A NULL is very different than an empty string from a spreadsheet. mySQL can handle NULL values just fine. Provided that A) your table column allows nulls -and- B) you structure the sql to actually insert NULL, and not just omit the value. Omitting values would definitely cause a syntax error.

    Code:
        
        <!--- works --->
        INSERT INTO Table (ColA, ColB, ColC, ColD)
        VALUES ('a', 'b', NULL, 22)
    
        <!--- fails --->
        INSERT INTO Table (ColA, ColB, ColC, ColD)
        VALUES ('a', 'b', (no value at all here), 22)
    Given that the original code was not using cfqueryparam, I suspect invalid sql syntax was the true cause of the problem, not MySQL. Yet another reason to avoid dynamic sql ;-) It is harder to debug.

IMN logo majestic logo threadwatch logo seochat tools logo