.NET Error in SQL-Statement SQL Server 2008 ODBC

[Harald]

Hi,

if an update or insert-statement contains something else than a bind variable, a sql error is fired.

Code: Select all

Call SqlPrepareAndExecute(hSql,'UPDATE MyTable SET ModificationDate = GetDate() WHERE Id = :nId')
Call SqlPrepareAndExecute(hSql,'UPDATE MyTable SET RecDeleted = 1 WHERE Id = :nId')

In this example, "= GetDate()" and "= 1" are not accepted.

Invalid_Parameter.png

"Invalid use of default parameter"

My environment is TD.NET 6.1 SP1 Build 29277, MS SQL Server 2008, ODBC, Win XP (German country settings)

Kind regards
Harald

[Jeff Luther]

Can't duplicate what you describe. I'm connecting to MS SS 2005 and wrote a test with:

Code: Select all

Set dtVar = SalDateCurrent()
! try 6 ways to ins/upd
! first with literal in WHERE
Call SqlPrepareAndExecute( hSql, "update dt set dt = GetDate() WHERE id = 1" )
Call SqlPrepareAndExecute( hSql, "update dt set dt = '20-JUL-2012' WHERE id = 1" )
Call SqlPrepareAndExecute( hSql, "update dt set dt = :dtVar WHERE id = 1" )
!
Set nID = 1   ! now with bind in WHERE
Call SqlPrepareAndExecute( hSql, "update dt set dt = GetDate() WHERE id = :nID" )
Call SqlPrepareAndExecute( hSql, "update dt set dt = '20-JUL-2012' WHERE id = :nID" )
Call SqlPrepareAndExecute( hSql, "update dt set dt = :dtVar WHERE id = :nID" )

and they all update ok. Attached is a test case in binary with a breakpoint to remind you to set Sql vars. for the connection. Maybe you'll need to change literal date value to your country's format?

[Harald]

Hello Jeff,

thanks for the reply. I did some additional investigations, and I found the reason. The error occurs, if there was a SqlSetLongBindDatatype used previousely. I save data to parent and child tables, the parent table has a long varchar field. The commit is at the end of all operations. If I commit directly after the long operation, it works fine.

Code: Select all

Call SqlPrepareAndExecute(hSql,'UPDATE MyParentTable SET ... WHERE Id = :nParentId')
...
If SqlPrepare(hSql, 'UPDATE MyParentTable SET MyLongField = :lsLongString WHERE Id = :nParentId')
   Call SqlSetLongBindDatatype(1,23)
   Call Execute(hSql)
...
Call SqlPrepareAndExecute(hSql,'UPDATE MyChildTable SET ModificationDate = GetDate() WHERE Id = :nId')    => SQL Error
Call SqlCommit(hSql)

I have also some trouble reading data from database in two complex select statements which uses sub-selects, if I exceed a number of fields or if I use sub-selects, I get a result set with only empty fields, but other queries with sub-selects and a huge amount of columnns work proper. I can't give you a test case because it only occurs in that special circumstances, and the database is to big to share (>30 gb).

Kind regards

Harald

[Jeff Luther]

Thanks for letting us know what you found out, Harald. Sounds like it's possible that SqlSetLongBindDatatype(1,23) call is still trying to be applied to your next update, and calling SqlCommit() resets or flushes that. I see in help for the SetLong function that it shows a COMMIT right after the SqlExecute.