Using TSQLScript with mssql
lazarus / 16-Databases

----------------------------------------------------------------------
mtournay · 2021-11-15 10:18

Hi everyone

I need to execute this with TSQLScript on a MSSQL database, using odbc
connector from FPC.


Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SET
IDENTITY_INSERT [dbo].[nameOfTheTable] ON;INSERT INTO
[dbo].[nameOfTheTable] (id, libelle, description, override) VALUES
(48, 'a value', 'a description', 0);SET IDENTITY_INSERT
[dbo].[nameOfTheTable] OFF;
Actually I always get this error

QuoteCould not execute statement. ODBC error details: LastReturnCode:
SQL_ERROR; Record 1: SqlState: 23000; NativeError: 544; Message:
[Microsoft][SQL Server Native Client 11.0][SQL Server]Impossible
d'ins�rer une valeur explicite dans la colonne identit� de la table
'transporteur_options_builtin' quand IDENTITY_INSERT est d�fini � OFF.
if i translate to english :

QuoteCould not insert with fixed id when IDENTITY_INSERT is OFF
this SQL script fully works with SSMS

I already have fully functionnal code to update database, managing
DDL, create or update function & procs.

What can I do ? is someone else doying this kind of stuff ?

best regards

----------------------------------------------------------------------
af0815 · 2021-11-15 12:51

Does it works if you send this as 3 extra isolated steps ?

----------------------------------------------------------------------
mtournay · 2021-11-15 16:05

No

I also tried
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
connection.executeDirect(  'SET IDENTITY_INSERT [dbo].[nameOfTheTable]
ON;'+  'INSERT INTO [dbo].[nameOfTheTable] (id, libelle, description,
override) VALUES (48, ''a value'', ''a description'', 0);'+  'SET
IDENTITY_INSERT [dbo].[nameOfTheTable] OFF;')
no error/exception raised but until I stop my code, the table is stuck
in a transaction (even if I call the other executeDirect with a
transaction)

----------------------------------------------------------------------
LacaK · 2021-11-16 17:47

Try set AutoCommit property of TSQLScript to true?

----------------------------------------------------------------------
mtournay · 2021-11-17 07:21

not better

In fact when I take a look to SQL Server Profiler, all 'scripts'
emited by SSMS is 'batch' : event classer are SQL:BatchStarting and
SQL:BatchCompleted

Is it possible to execute a 'batch' script througth odbc ?

----------------------------------------------------------------------
af0815 · 2021-11-17 07:59

odbc is not the full interface to the sqlserver it is only a common
compromise.
I am using the freetds library to communicate with MS-SQLserever. It
have still some missing functions, but works more better as odbc. 
freeTDS is working for me for Win32/Win64 (use the correct dll !!),
Linux64 (normal package from distribution) and Raspbian.

Used in SQLdb and ZEOS.

----------------------------------------------------------------------
mtournay · 2021-11-17 17:41

I have never been able to find this dll in a correct version

I've tried several times, but nothing worked, or I'm really stupid

I would really try to use it ....

----------------------------------------------------------------------
af0815 · 2021-11-17 19:02

look in a Folder of Zeos the guys there have a colletion of driver 

https://github.com/frones/ZeosLib/tree/master/lib/freetds

i think this is the shortest way

----------------------------------------------------------------------
miab3 · 2021-11-18 08:32

I guess better:

https://sourceforge.net/projects/zeoslib/files/3rd%20party/FreeTDS/
ftp://ftp.freepascal.org/pub/fpc/contrib/windows/

Michał

----------------------------------------------------------------------
LacaK · 2021-11-18 18:08

Or http://downloads.freepascal.org/fpc/contrib/windows/
(should be same as ftp://ftp.freepascal.org/pub/fpc/contrib/windows/)