[V4REV] problem with binded variables - SQL

[David Simpson]

Ruslan,
[I am not sure if this got thru previously due to my mistake with the  
attachment.]

Thanks for your answer, now I think I understand that documentation  
entry a little better.

So now I will provide some details of the Revolution code where I am  
also having a slightly different issue with the bind variables.

I am using the revDB calls (not the calls to the Valentina  
extension), and I am successfully connecting to an existing Valentina  
DB file, setting Date format and creating a table with SQL commands:

    if the platform is "Win32" then
       -- Windows Valentina SN#
       put revOpenDatabase 
("Valentina2","",gValentinaDBPathname,,,,,gValentinaSNWin) into  
varDBConnection
    else
       -- Mac Valentina SN#
       put revOpenDatabase 
("Valentina2","",gValentinaDBPathname,,,,gValentinaSNMac,"") into  
varDBConnection
    end if -- platform check - for license key Mac vs Win
    put the result into varError
    -- must set date format to YMD with "-" separator every time file  
is opened
    put "SET PROPERTY DateTimeFormat,DateSeparator,TimeSeparator OF  
DATABASE TO 'kYMD','-',':';" into tSQLCommands
    revExecuteSQL varDBConnection,tSQLCommands
    put the result into varError


I have previously created an array of data values (please see the  
attached test stack (having empty Valentina SN fields).
I fill an array of placeholder variables, then try to insert values  
into the columns:

   -- fill arrays with data for writing
   put ":1,:2,:3,:4,NULL,NULL,:7,:8,NULL,: 
10,NULL,NULL,NULL,NULL,NULL,NULL,:17,NULL,NULL,NULL,:21" into  
varPlaceholdersList

   -- execute SQL statement
   put "INSERT INTO " & varTableName & " ( " & gFieldsList & " )  
VALUES (" & varPlaceholdersList & ")" into tSQLCommands
   --put "INSERT INTO " & varTableName & " VALUES (" &  
varPlaceholdersList & ")" into tSQLCommands
   revExecuteSQL varDBConnection,tSQLCommands,"varSourceDataArray"
   put the result into varError


I get the error:
Date-time value "2007-06-22
" is invalid. Illegal symbol at "11"  
position.

for the first date column in the database. I have also seen similar  
errors with a TIMESTAMP column.

When I look at the variables in the Revolution 3.0 debugger/variable  
pane, the error message text shows several square boxes following the  
end of the date, which seems to indicate that more characters than  
the date were returned in the error message.
When I look at the source date values I passed in via the array,  
everything looks fine and I only see the exact date characters which  
I put into the array element.

-------------- next part --------------

David Simpson
www.fmpromigrator.com