Devart Forums

m227

Hi,
I use MySQL connection via UniDAC with http tunneling. When I assign TCRSSLIOHandler to Connection.IOHandler and properly change HttpUrl to https It usually works.

But then after some time (1-15 minutes) disconnects and error occurs (during trying to edit dataset or run query).
Sample error log:

exception class : SocketException
exception message : Failed to connect to server script.

Code: Select all

main thread ($1e2c):
01920b6c +06c KDR_admin.exe ScSecureSocket                TSecureSocket.Send
0192537b +01f KDR_admin.exe ScSSLClient                   TScSSLClient.WriteBuffer
0197ab47 +01f KDR_admin.exe CRSSLIOHandler        282  +2 TCRSSLIOHandler.Write
01766ee2 +01a KDR_admin.exe CRVio                         TCRVioHandler.Write
017fd268 +020 KDR_admin.exe MySqlNetUni                   TMySqlNet.VioWrite
017fbcca +1ba KDR_admin.exe MySqlNetUni                   TMySqlNet.RealSend
017fba65 +03d KDR_admin.exe MySqlNetUni                   TMySqlNet.Send
01817041 +041 KDR_admin.exe MySqlSessionUni               TMySqlSession.SimpleCommand
0181eb61 +071 KDR_admin.exe MySqlApiDirectUni             TMySQLAPIDirect.mysql_real_query
01830d94 +15c KDR_admin.exe MyClassesUni                  DoExecute
01831469 +09d KDR_admin.exe MyClassesUni                  TMySQLCommand.InternalExecute
01831591 +0bd KDR_admin.exe MyClassesUni                  TMySQLCommand.Execute
017c1944 +034 KDR_admin.exe CRAccess                      TCRConnection.ExecuteSQL
0183ad9b +0e3 KDR_admin.exe MyClassesUni                  TMySQLTransaction.StartTransaction
017a5c2e +10e KDR_admin.exe DBAccess                      TDATransaction.StartTransaction
01796f70 +03c KDR_admin.exe DBAccess                      TDADataSetUpdater.SetSavepoint
01797005 +029 KDR_admin.exe DBAccess                      TDADataSetUpdater.SetLockSavepoint
0179b004 +01c KDR_admin.exe DBAccess                      TDADataSetUpdater.PerformLock
018410d4 +000 KDR_admin.exe MyServicesUni                 TCustomMyDataSetUpdater.PerformLock
0179b262 +092 KDR_admin.exe DBAccess                      TDADataSetUpdater.PerformUpdateDelete
0179b485 +009 KDR_admin.exe DBAccess                      TDADataSetUpdater.PerformUpdate
01716955 +035 KDR_admin.exe MemDS                         TDataSetUpdater.DoPerformUpdate
01711a0f +013 KDR_admin.exe MemDS                         TMemDataSet.DoPerformUpdate
016fb6b5 +00d KDR_admin.exe MemData                       TData.InternalUpdate
017018c8 +034 KDR_admin.exe MemData                       TMemData.UpdateRecord
016ea848 +000 KDR_admin.exe MemUtils                      CopyBuffer
01703491 +009 KDR_admin.exe MemData                       TBlockManager.GetRecord
016fb580 +048 KDR_admin.exe MemData                       TData.PostRecord
0170169b +037 KDR_admin.exe MemData                       TMemData.PostRecord
0171105a +126 KDR_admin.exe MemDS                         TMemDataSet.InternalPost
0178f785 +045 KDR_admin.exe DBAccess                      TCustomDADataSet.InternalPost
00aac6ed +029 KDR_admin.exe Data.DB                       TDataSet.CheckOperation
00aac1e8 +048 KDR_admin.exe Data.DB                       TDataSet.Post
01711142 +006 KDR_admin.exe MemDS                         TMemDataSet.Post
00ae07f9 +0fd KDR_admin.exe cxDBData                      TcxDBDataController.CheckBrowseMode
00ae2565 +15d KDR_admin.exe cxDBData                      TcxDBDataController.TryFocusRecord
00adf5e4 +0f8 KDR_admin.exe cxDBData                      TcxDBDataController.CanFocusRecord
00a39f55 +041 KDR_admin.exe cxCustomData                  TcxCustomDataControllerInfo.CanFocusedRowChanging
00a3ac76 +0b6 KDR_admin.exe cxCustomData                  TcxCustomDataControllerInfo.DoChangeFocusedRow
00a3ccfd +02d KDR_admin.exe cxCustomData                  TcxCustomDataControllerInfo.SetFocusedRowIndex
00a33448 +07c KDR_admin.exe cxCustomData                  TcxCustomDataController.ChangeFocusedRowIndex
01083db3 +08b KDR_admin.exe cxGridCustomTableView         TcxCustomGridTableController.SetFocusedRecordIndex
010859d8 +010 KDR_admin.exe cxGridCustomTableView         TcxCustomGridTableController.SetFocusedRecord
011014a7 +127 KDR_admin.exe cxGridTableView               TcxGridTableController.SetFocusedRecord
010778bb +04b KDR_admin.exe cxGridCustomTableView         TcxCustomGridRecordViewInfo.Click
0110e110 +034 KDR_admin.exe cxGridTableView               TcxCustomGridRowViewInfo.Click
01077ad8 +088 KDR_admin.exe cxGridCustomTableView         TcxCustomGridRecordViewInfo.MouseDown
010750cf +03b KDR_admin.exe cxGridCustomTableView         TcxGridTableCellViewInfo.MouseDown
01076c4e +136 KDR_admin.exe cxGridCustomTableView         TcxGridTableDataCellViewInfo.MouseDown
00ba9983 +04b KDR_admin.exe cxGridCustomView              TcxCustomGridController.MouseDown
01088580 +09c KDR_admin.exe cxGridCustomTableView         TcxCustomGridTableController.MouseDown
00baada7 +05b KDR_admin.exe cxGridCustomView              TcxGridSite.BeforeMouseDown
0092ffbe +17a KDR_admin.exe cxControls                    TcxControl.MouseDown
00baba1d +025 KDR_admin.exe cxGridCustomView              TcxGridSite.MouseDown
005c48ec +08c KDR_admin.exe Vcl.Controls                  TControl.DoMouseDown
005c493b +03f KDR_admin.exe Vcl.Controls                  TControl.WMLButtonDown
005c4292 +2be KDR_admin.exe Vcl.Controls                  TControl.WndProc
005c8e77 +63b KDR_admin.exe Vcl.Controls                  TWinControl.WndProc
0092f53c +1ac KDR_admin.exe cxControls                    TcxControl.WndProc
00babd74 +02c KDR_admin.exe cxGridCustomView              TcxGridSite.WndProc
005c8444 +02c KDR_admin.exe Vcl.Controls                  TWinControl.MainWndProc
005454dc +014 KDR_admin.exe System.Classes                StdWndProc
75e07e5b +00b USER32.dll                                  DispatchMessageW
006ccb17 +0f3 KDR_admin.exe Vcl.Forms                     TApplication.ProcessMessage
006ccb5a +00a KDR_admin.exe Vcl.Forms                     TApplication.HandleMessage
006cce8d +0c9 KDR_admin.exe Vcl.Forms                     TApplication.Run
01e83296 +0ae KDR_admin.exe KDR_admin              31 +10 initialization
7522fa27 +017 KERNEL32.DLL                                BaseThreadInitThunk

I use latest UniDAC and SecureBridge.

m227

Hi, would you clarify what specific TLockMode values do? It is not specified in my UniDac documentation...

m227

Hi Vitaliy,
I successfully installed TCRSSLIOHandler (using corresponding to my UniDAC SecureBridge version).
Then I set up TUniConnection.IOHandler. And in SpecificOptions set https:// address and a protocol as mpSSLHttp.

When I run my application from IDE with Debugging then I get

First chance exception at $75CCB5B2. Exception class SocketException with message
'Error on data reading from the connection:
An existing connection was forcibly closed by the remote host'.

Then application runs fine. Is it OK?

m227

Hi Vitaliy, thank you for reasonable and detailed answer. On the other hand: I changed address to tunnel.php file in SpecificOptions, changing http:// to https://

If I put this (https) address to web browser I get:

Devart HttpTunnel v1.69
Tunnel script is installed correctly.
You can establish connections through the HTTP tunnel.

so encrypted http transmission works.

But when I try to set my TUniConnection.Connected to True in design time with this address I get: "Secure connection is not available". I do not have any IOHandler set. Do I have to use SecureBridge and set this handler to be able to use tunnel.php with https?

m227

Then what changed since 7.0.2 as there it still works without any keys?

m227

Hi, I use Delphi XE7, UniDAC 7.0.2, MySqlProvider and connection via HTTP tunneling. It works.
My TUniConnection.ConnectionString is:

Code: Select all

Provider Name=MySQL;User ID=ufin_kdr;Password=mysecretpass;Character Set=cp1250;Data Source=sql.myserver.pl;Database=ufin_kdr;Protocol=mpSSLHttp;Http Url=http://mysite.nazwa.pl/kdr/tunnel.php;Login Prompt=False

Then I try the same in Delphi 10.2 with UniDAC 8.2.4. But I get error:

First chance exception at $75EDB512. Exception class EMySqlException with message
'Wrong or unknown protocol
Can't create SSL connection via HTTP'.

What is wrong?

m227

Thank you for hints Viktor. I tried both your solutions, but none worked :/
Still get the same error.
What's more the same query run in Workbench works without problem.

This is my line from my.ini:
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION

Here I prepared simple source code to program I run:
http://www.bazydlafirm.home.pl/e-storm/ ... problem.7z

I put there my.ini also.

m227

Hi, I use UniDAC 6.1.3 for Delphi 2006 and 8.2.4 for RAD Studio 10.2 to connect with MySQL 8.0.23.

My simple code like:

Code: Select all

procedure TForm34.Button1Click(Sender: TObject);
begin
  UniQuery1.SQL.Text := 'SELECT "0"';
  UniQuery1.Open;
end;

produces error:

Exception class EMySqlException with message '#42S22Unknown column '0' in 'field list''.

While the same query with apostrophes (single quotes) instead double quotes runs ok.
I checked global and session variables sql_mode and there is not ANSI_QUOTES in it.

m227

So UniDAC is rather limited in some fields while not limited in others. I will then reconsider upgrading to UniDAC old apps which use MyDAC. Thank you for clear response.

m227

Hi, i migrated from MyDac to UniDac. My program can't compile as:

Code: Select all

  DM.mq.Options.AutoRefreshInterval := mRefreshPeriod.IntValue;
  DM.mq.Options.AutoRefresh := mRefresh.Down;

are not recognized. How to replace it with proper properties of UniDac?

m227

Hi,
reading changelogs of UniDac/LiteDac I found, that direct mode for SQLite is based on version 3.27 which is older than year and a half.

Current version of SQLite is 3.33 and since 3.27 there are numerous important improvements introduced.
Do (and when) you plan to upgrade direct mode of SQLite/UniDac SQLite provider to reflect these changes?

Current one is shamefully outdated.

m227

Hi, in topic below you wrote that it is possible to get result from ExecSQL.(SQLite)

https://www.devart.com/unidac/docs/deva ... ect[]).htm

It is not clear how, if I shall use ExecSQL function result or UniConnection Result. Anyway both below trials fail:

Code: Select all

// Solution 1:
  DstID := uc.ExecSQL('SELECT 1');

// Soultion 2:
  uc.ExecSQL('SELECT 1');
  DstID := uc.ParamByName('Result').AsInteger;

First one returns NULL
Second one returns error "No Result parameter".

Would you clarify the topic and fill missing data in a help?

m227

Hi I have TUniScript (SQLite) like below:

Code: Select all

DROP TABLE IF EXISTS tmp;
CREATE TEMP TABLE tmp AS
WITH RECURSIVE
  tt(ID, ParentID, Name, AreaID, Symbol, Source, Path, SumUp, Static, USF, Address, Kind) AS (
    SELECT ID, NULL, Name, AreaID, Symbol, Source, Path, SumUp, Static, USF, Address, Kind
      FROM Scaff WHERE ID = &SrcID
    UNION
    SELECT t.ID, t.ParentID, t.Name, t.AreaID, t.Symbol, t.Source, t.Path, t.SumUp, t.Static, t.USF, t.Address, t.Kind
      FROM Scaff t
      JOIN tt ON t.ParentID = tt.ID
  ) SELECT * FROM tt;
DROP TABLE IF EXISTS var;
CREATE TEMP TABLE var (v1 INT);
INSERT INTO var (v1) 
  SELECT SEQ-MIN(tmp.ID)+1 FROM sqlite_sequence, tmp 
   WHERE sqlite_sequence.name = 'Scaff';
UPDATE tmp SET (ID, ParentID) = (SELECT ID + v1, ParentID + v1 FROM var);
UPDATE tmp SET ParentID = &DstID WHERE ParentID IS NULL; 
INSERT INTO Scaff (ID, ParentID, Name, AreaID, Symbol, Source, Path, SumUp, Static, USF, Address, Kind) 
     SELECT * FROM tmp;
UPDATE Scaff SET OrderFld = rowid * 10 WHERE OrderFld IS NULL;
SELECT ID FROM tmp LIMIT 1;

I want to retrieve value from last Select, my Delphi code is like:

Code: Select all

  usCopyNodeSingleDS.MacroByName('SrcID').AsInteger := 1;
  usCopyNodeSingleDS.MacroByName('DstID').AsInteger := 2;
  usCopyNodeSingleDS.Execute;
  AResult := usCopyNodeSingleDS.DataSet.Fields[0];

then I got AV as DataSet seems to be nil. How to use it then?

m227

Hi, thank you for a response. I managed to create MEDIAN function and it works. The only problem is that if I want to use such function twice in a query it fails, as when passing parameters to <YourStepProcedure> i don't know from which column they come. My sample SQLite query.

Code: Select all

CREATE TEMPORARY TABLE TestData1 (a INT);
CREATE TEMPORARY TABLE TestData2 (a INT);
INSERT INTO TestData1 VALUES (7),(14),(21),(3),(9),(2),(4);     -- Median = 7
INSERT INTO TestData2 VALUES (7),(14),(21),(3),(9),(2),(4),(5); -- Median = 6
SELECT Median2(TestData1.A), Median2(TestData2.A)
  FROM TestData1, TestData2;

Shall I use any MOD to differentiate myself from which column data came?

m227

Hi, you are boasting that "Custom SQL aggregate functions are supported" since UniDAC 7.1
I have devoted one hour and did not find anywhere how to use this facility.
Could you enlighten me?
The only trace I found was for LiteDAC:
viewtopic.php?f=48&t=38101&p=132659&hil ... ns#p132659

Devart Forums

Search found 75 matches

UniDAC MySQL + SecureBridge, https, disconnecting problems

Re: Record was changed by user ...

Re: Can't create SSL connection via HTTP

Re: Can't create SSL connection via HTTP

Re: Can't create SSL connection via HTTP

Can't create SSL connection via HTTP

Re: Double quotes problem

Double quotes problem

Re: Options.AutoRefresh missing after migration

Options.AutoRefresh missing after migration

Any progress in supporting newer versions od SQLite in Direct mode?

Result of TUniQuery.ExecSQL

Result of TUniSctipt (SQLite)

Re: Custom User Aggregate Fuction - SQLite

Custom User Aggregate Fuction - SQLite