Devart Forums

chkaufmann

I still have the same error.

exception class          : EUniError
exception message        : Error on data writing to the connection: An existing connection was forcibly closed by the remote host. Socket Error Code: 10054($2746).

thread $470 (TBackgroundTaskRunner):
00e779eb RankingsHttpService.exe Uni               1182 HandleInternalError
00e7c2e7 RankingsHttpService.exe Uni               3356 TCustomUniDataSet.Prepare
770d3a61 ntdll.dll                                      KiUserExceptionDispatcher
00480dd5 RankingsHttpService.exe madExcept         3136 IsValidObject2
004b9bc1 RankingsHttpService.exe madExcept        16620 SetLastExceptionHelper
004b9c26 RankingsHttpService.exe madExcept        16655 MyRaiseExceptProc
011aa7ce RankingsHttpService.exe PgClassesUni      5062 TPgSQLConnection.ProcessInternalException
011b0df0 RankingsHttpService.exe PgClassesUni      7841 TPgSQLCommand.InternalExecute
770d3a61 ntdll.dll                                      KiUserExceptionDispatcher
004b9bc1 RankingsHttpService.exe madExcept        16620 SetLastExceptionHelper
004b9c26 RankingsHttpService.exe madExcept        16655 MyRaiseExceptProc
01173598 RankingsHttpService.exe PgSQLNetUni        287 TPgSQLNet.VioWrite
01173677 RankingsHttpService.exe PgSQLNetUni        324 TPgSQLNet.FlushSend
0116abcb RankingsHttpService.exe PgSQLProtocolUni  1590 TPgSQLProtocol.Flush
0116ba08 RankingsHttpService.exe PgSQLProtocolUni  1915 TPgSQLProtocol.InternalExecuteStmt
01169c51 RankingsHttpService.exe PgSQLProtocolUni  1144 TPgSQLProtocol.ExecuteStmt
011b0cca RankingsHttpService.exe PgClassesUni      7813 TPgSQLCommand.InternalExecute
011afb49 RankingsHttpService.exe PgClassesUni      7242 TPgSQLCommand.Execute
011abddc RankingsHttpService.exe PgClassesUni      5622 TPgSQLTransaction.ExecuteSQL
011abeff RankingsHttpService.exe PgClassesUni      5662 TPgSQLTransaction.StartTransaction
011b0247 RankingsHttpService.exe PgClassesUni      7508 TPgSQLCommand.PrepareFetchConnection
011af96c RankingsHttpService.exe PgClassesUni      7177 TPgSQLCommand.Prepare
00e58884 RankingsHttpService.exe CRAccess          4040 TCRRecordSet.InternalPrepare
011b37b1 RankingsHttpService.exe PgClassesUni      9006 TPgSQLRecordSet.InternalPrepare
00d4b56b RankingsHttpService.exe MemData           2635 TData.Prepare
00e588aa RankingsHttpService.exe CRAccess          4051 TCRRecordSet.Prepare
011b25fb RankingsHttpService.exe PgClassesUni      8490 TPgSQLRecordSet.Prepare
00df294f RankingsHttpService.exe MemDS             1068 TMemDataSet.Prepare
00e22301 RankingsHttpService.exe DBAccess          8637 TCustomDADataSet.Prepare
00e7c2d3 RankingsHttpService.exe Uni               3354 TCustomUniDataSet.Prepare
00dfa43a RankingsHttpService.exe MemDS             5092 TMemDataSet.SetPrepared
00fb3f67 RankingsHttpService.exe BSDBUniDACImp      315 TBSUniDataSet.PrepareParams

It's almost impossible to create a reproducable case because it doesn't happen regularly. So I need a solution to isolate the problem in the production environement:
- It happens in an application, that runs on a server and that executes some tasks, e.g. every hour.
- It never happened before update from UniDac 8.6.x to version 9.x
- Normally all runs fine, but as soon as I make changes to the database with a different application, I start to have these errors
- Readonly access to the same database doesn't seem to have any influence. There are PHP scripts from a website running all the time.
- Currently I use PostgreSQL 13.x. Database and application are running on the same computer, so there is no router or firewall between them. I'm using Win Server 2019, all updates installed.
- The UniDac objects (TUniConnection, TUniDataset) are objects newly created in a new thread

So any hint on how to isolate the problem would be helpfull.

Christian

chkaufmann

Hi,

since I update from UniDac 8.x to the latest UniDac version (9.2.1) I get a new error in different places. Unfortunately not always when I run the code only on some occations. So it's difficult to find out. Can somebody give me a hint about where I should start to search the problem. The error looks always like this:

Code: Select all

exception class          : EUniError
exception message        : Error on data writing to the connection: An existing connection was forcibly closed by the remote host. Socket Error Code: 10054($2746).

thread $3564 (TBackgroundTaskRunner):
00e77617 RankingsHttpService.exe Uni               1182 HandleInternalError
00e7bfe7 RankingsHttpService.exe Uni               3385 TCustomUniDataSet.OpenCursor
77a63a61 ntdll.dll                                      KiUserExceptionDispatcher
00480dd5 RankingsHttpService.exe madExcept         3136 IsValidObject2
004b9bc1 RankingsHttpService.exe madExcept        16620 SetLastExceptionHelper
004b9c26 RankingsHttpService.exe madExcept        16655 MyRaiseExceptProc
011a9cda RankingsHttpService.exe PgClassesUni      4999 TPgSQLConnection.ProcessInternalException
011b0294 RankingsHttpService.exe PgClassesUni      7768 TPgSQLCommand.InternalExecute
77a63a61 ntdll.dll                                      KiUserExceptionDispatcher
004b9bc1 RankingsHttpService.exe madExcept        16620 SetLastExceptionHelper
004b9c26 RankingsHttpService.exe madExcept        16655 MyRaiseExceptProc
01172ecc RankingsHttpService.exe PgSQLNetUni        287 TPgSQLNet.VioWrite
01172fab RankingsHttpService.exe PgSQLNetUni        324 TPgSQLNet.FlushSend
0116a4ff RankingsHttpService.exe PgSQLProtocolUni  1590 TPgSQLProtocol.Flush
0116b33c RankingsHttpService.exe PgSQLProtocolUni  1915 TPgSQLProtocol.InternalExecuteStmt
01169585 RankingsHttpService.exe PgSQLProtocolUni  1144 TPgSQLProtocol.ExecuteStmt
011b016e RankingsHttpService.exe PgClassesUni      7740 TPgSQLCommand.InternalExecute
011af019 RankingsHttpService.exe PgClassesUni      7174 TPgSQLCommand.Execute
011ab2e8 RankingsHttpService.exe PgClassesUni      5559 TPgSQLTransaction.ExecuteSQL
011ab40b RankingsHttpService.exe PgClassesUni      5599 TPgSQLTransaction.StartTransaction
011af6ef RankingsHttpService.exe PgClassesUni      7436 TPgSQLCommand.PrepareFetchConnection
011b18c2 RankingsHttpService.exe PgClassesUni      8354 TPgSQLRecordSet.ExecFetch
00e5844c RankingsHttpService.exe CRAccess          3955 TCRRecordSet.InternalOpen
011b368b RankingsHttpService.exe PgClassesUni      9244 TPgSQLRecordSet.InternalOpen
00d4ba98 RankingsHttpService.exe MemData           2584 TData.Open
00df2a3b RankingsHttpService.exe MemDS             1131 TMemDataSet.InternalOpen
00e28597 RankingsHttpService.exe DBAccess         11319 TCustomDADataSet.InternalOpen
00dde56e RankingsHttpService.exe Data.DB                TDataSet.DoInternalOpen
00dde61e RankingsHttpService.exe Data.DB                TDataSet.OpenCursor
00df2dae RankingsHttpService.exe MemDS             1263 TMemDataSet.OpenCursor
00e22635 RankingsHttpService.exe DBAccess          8748 TCustomDADataSet.OpenCursor
00e7bfd3 RankingsHttpService.exe Uni               3383 TCustomUniDataSet.OpenCursor
00dde4d1 RankingsHttpService.exe Data.DB                TDataSet.SetActive
00e22423 RankingsHttpService.exe DBAccess          8685 TCustomDADataSet.SetActive
00dde310 RankingsHttpService.exe Data.DB                TDataSet.Open
00f6e6e5 RankingsHttpService.exe BSDB              2159 TBSDBConnection.InternalOpenLookup

Christian

chkaufmann

I connect to MS SQL Server with the default provider option "prAuto".

Once the connection is established, how can I find out which provider (Native, OLEDB, SQL, direct) was found and used in my connection?

Christian

chkaufmann

Hi,

normally I don't change the provider property and have no native client installed.

For backups I create a copy of my connection with:

Code: Select all

    FUniConnection.DefaultTransaction.IsolationLevel := ilSnapshot;
    FUniConnection.DefaultTransaction.ReadOnly       := True;
    FUniConnection.SpecificOptions.Values['Provider']:= 'prDirect';

This works fine except for one customer, who defines a specific instance:

Code: Select all

    FUniConnection.Server   := 'CLWSTSPR30\LOGO';

I cannot test this on my local installation, so my question: Is it possible, that using this syntax for the Server property does not work in direct mode for SQL Server?

Regards
Christian

chkaufmann

Hi,

with a TUniQuery I can run a command like:

Code: Select all

copy mytable(col1,col2) from 'testdata.csv' delimiter ',' csv

This works fine when the csv file is placed on the server.
Now I have my file on the client computer and it doesn't work. I can do it with the command line tool psql.exe (\copy command) but my question is, if there is a way to run it from inside my Delphi application.

Christian

chkaufmann

I just run into this problem again. I use it in a service application where I have many connections and it's not always easy to find the correct one in dbMonitor. So it would be helpfull to have another boolean flag on each object to turn monitor messages on and off.

Christian

chkaufmann

But how should I handle this? In case I opened the transaction explicitely: Did the server rollback all? Should I call conn.Commit or conn.Rollback?

Christian

chkaufmann

It's funny to find my own post from 9 years ago when I search for a solution for a problem :-).

I have a similar case:
- Postgresql 11
- using a TUniQuery

Code: Select all

    FUniConnection.Open;
    FUniConnection.DefaultTransaction.StartTransaction;
    try
      FUniQuery.SQL.Text := 'select last_value from GEN_NOT_THERE';  // this fails
      FUniQuery.Open;
    except
      // nothing here
    end;
    FUniQuery.SQL.Text := 'select count(*) from BSENTITY';
    FUniQuery.Open;
    FUniConnection.DefaultTransaction.Commit;

Again, the second call of "Open" fails with error "current transaction is aborted, commands ignored until end of transaction block".

Could you please look into this?

Regards
Christian

chkaufmann

Hi,

I have to parse SQL statements (MySql or PostgreSql) for params:

Input: "select * from PERSON where NAME = :PNAME and CITY = :PCITY"
Output: "select * from PERSON where NAME = ? and CITY = ?" and array ("PNAME", "PCITY")

I found the code in TCRCommand.ParseSQL for this. But how to use it without any connection? Can I just create a TCRCommand object and call ParseSQL?
Or what is the correct way to use this code?

Christian

chkaufmann

Hi,

one of my customers reports an application error that I cannot reproduce anywhere else.

Code: Select all

thread $8590 (TBSDBMessageController), inner exception level 3:
>> SocketException, Cannot connect to server on host 'srsofaioi23358': Only one usage of each socket address is normally permitted. Socket Error Code: 10048($2740)
01c35417 Logo12.exe   CRVioTcp                568 TCRVioTcp.Connect
01f68a6d Logo12.exe   PgSQLProtocolUni       1218 TPgSQLProtocol.CreateVio
01f692d6 Logo12.exe   PgSQLProtocolUni       1445 TPgSQLProtocol.StartConnection
01f67c7c Logo12.exe   PgSQLProtocolUni        758 TPgSQLProtocol.Connect
01f8a462 Logo12.exe   PgClassesUni           4166 InternalConnect
01f8a548 Logo12.exe   PgClassesUni           4191 TPgSQLConnection.Connect
01c11d30 Logo12.exe   DBAccess               4187 TCustomDAConnection.DoConnect
01c6fd03 Logo12.exe   Uni                    1858 TUniConnection.DoConnect
01c120cd Logo12.exe   DBAccess               4308 TCustomDAConnection.PerformConnect
01c15105 Logo12.exe   DBAccess               5370 TCustomDAConnection.SetConnected
007d34f0 Logo12.exe   Data.DB                     TCustomConnection.Open
01d8828e Logo12.exe   BSDBUniDACImp           501 TBSUniConnection.InternalOpen

The code is running in a background thread that starts every two seconds. Normally I keep the connection open between two calls but every 100 seconds I force to close the connection and then reopen it again.

The same application runs for other customers during many days without any problem, so I think, it has something to do with this customers setup of the network or his Postgresql server.

But since the error message apears in my application, I have to "prove" that this is the case and I didn't find the source for the problem yet.

Any hints where to search? Or what I can change on my test Postgresql server so that I get this error as well?

Christian

chkaufmann

Normally I download the "source only" Zip file. So this means I have to use an installer in order to get the compiled packages?

Christian

chkaufmann

Hi,

I try to use the Http tunnel with Postgresql:

Code: Select all

    UniConnection.Server := '127.0.0.1';
    UniConnection.SpecificOptions.Values['UseHttp'] := 'True';
    UniConnection.SpecificOptions.Values['HttpUrl'] := 'https://myserver.xy/tunnel.php';

Now I get an error about missing IOHandler. I found the unit CRSSLIOHandler.pas in the demos but this doesn't compile because of other missing units. Where can I find these units?

Christian

chkaufmann

Hi,

I think the datepart function is still missing. Any news here?

Then I still need MS Access as option in one project and there is no SQL function "upper", it's named "ucase".

Can I just extend the definition for upper in all initalization sections? Like this?

AccessFunctions.Add('UPPER', 'UCASE(%s)');

And could you add that in the next update?

Thanks

Regards
Christian

chkaufmann

Hi,
I try to understand AutoCommit with Firebird connections.

When I use StartTransaction, then do some updates / inserts and then use Rollback, this doesn't work. But GDS.isc_commit_transaction was never called.

So is the AutoCommit flag applied to the transaction properties and handled by the Firebird server?

Is this Firebird specific? Shouldn't AutoCommit be set to false in StartTransaction if the behaviour is like this?

Regards
Christian

chkaufmann

Hi,

I try to limit the connections that log to dbMonitor. I found this in the helpfile:
"set to True the Debug property for components you want to trace;"

So for my understanding, only components with Debug=True should generate log entries. But this doesn't work and evrywhere in the code I find "MonitorClass.HasMonitor or Debug".
Did I missunderstand something? Is the only way to filter by connection to click in the "Object Tree" in dbMonitor? Unfortunately the whole log is deleted in this case.

cu Christian

Devart Forums

Search found 82 matches

Re: Random Error with PostgreSQL

Random Error with PostgreSQL

Get the used provider

TUniConnection - SQL Server, direct with instance name

Postgresql - import CSV data

Re: dbMonitor - limit to certain connections

Re: Queries with errors in PostgreSQL

Re: Queries with errors in PostgreSQL

Parse SQL with UniDAC

Postgresql connection problem

Re: Using Http Tunnel with Postgresql

Using Http Tunnel with Postgresql

Re: Unified SQL - date functions

TUniConnection.AutoCommit with Firebird

dbMonitor - limit to certain connections