Recent

Author Topic: Using TSQLScript with mssql  (Read 2420 times)

mtournay

  • Jr. Member
  • **
  • Posts: 62
Using TSQLScript with mssql
« on: November 15, 2021, 11:18:49 am »
Hi everyone

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


Code: SQL  [Select][+][-]
  1. SET IDENTITY_INSERT [dbo].[nameOfTheTable] ON;
  2. INSERT INTO [dbo].[nameOfTheTable] (id, libelle, description, override) VALUES (48, 'a value', 'a description', 0);
  3. SET IDENTITY_INSERT [dbo].[nameOfTheTable] OFF;

Actually I always get this error

Quote
Could 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 :

Quote
Could 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
laz 2.06 32b - fpc 3.04 32b - win10 64b

af0815

  • Hero Member
  • *****
  • Posts: 842
Re: Using TSQLScript with mssql
« Reply #1 on: November 15, 2021, 01:51:07 pm »
Does it works if you send this as 3 extra isolated steps ?
regards
Andreas

mtournay

  • Jr. Member
  • **
  • Posts: 62
Re: Using TSQLScript with mssql
« Reply #2 on: November 15, 2021, 05:05:43 pm »
No

I also tried
Code: Pascal  [Select][+][-]
  1. connection.executeDirect(
  2.   'SET IDENTITY_INSERT [dbo].[nameOfTheTable] ON;'+
  3.   'INSERT INTO [dbo].[nameOfTheTable] (id, libelle, description, override) VALUES (48, ''a value'', ''a description'', 0);'+
  4.   'SET IDENTITY_INSERT [dbo].[nameOfTheTable] OFF;'
  5. )

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)
laz 2.06 32b - fpc 3.04 32b - win10 64b

LacaK

  • Hero Member
  • *****
  • Posts: 653
Re: Using TSQLScript with mssql
« Reply #3 on: November 16, 2021, 06:47:27 pm »
Try set AutoCommit property of TSQLScript to true?

mtournay

  • Jr. Member
  • **
  • Posts: 62
Re: Using TSQLScript with mssql
« Reply #4 on: November 17, 2021, 08:21:10 am »
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 ?
laz 2.06 32b - fpc 3.04 32b - win10 64b

af0815

  • Hero Member
  • *****
  • Posts: 842
Re: Using TSQLScript with mssql
« Reply #5 on: November 17, 2021, 08:59:38 am »
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.
regards
Andreas

mtournay

  • Jr. Member
  • **
  • Posts: 62
Re: Using TSQLScript with mssql
« Reply #6 on: November 17, 2021, 06:41:26 pm »
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 ....
laz 2.06 32b - fpc 3.04 32b - win10 64b

af0815

  • Hero Member
  • *****
  • Posts: 842
Re: Using TSQLScript with mssql
« Reply #7 on: November 17, 2021, 08:02:35 pm »
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  O:-)
regards
Andreas

miab3

  • Full Member
  • ***
  • Posts: 127

LacaK

  • Hero Member
  • *****
  • Posts: 653

 

TinyPortal © 2005-2018