SQLExecuteCommand - HowTo

I’m trying to exec a simple sql command:

with DARemoteDataAdapter_SQL.UpdateDataCall do
begin
ParamByName(‘aSQLText’).AsString :=
‘insert into myTableName (’‘name’’) values (’‘test’’)’;
Execute();
end;

But I get:

‘An exception was raised on the server: Script runtime error in beforeExecuteCommand: System.NullReferenceException:’

How do I setup it right?

Thanks.

Hello

Could you create a testcase for this issue?
Or at least please describe more detailed your setup: server platform and definition of the command you are trying to execute in this call.

TIA

Okay, I recently updated my DA Delphi version to DA 10.x while on my server runs a Relativity Server 9.x. So I decided to update Rel. server to the same version.

But now my formally working fb4.net connection is not working anymore because of a system.io.fileloadException “FirebirdSql.Data.FirebirdClient … not found.”

But there is a FirebirdSql.Data.FirebirdClient.dll comming with Relativity server (ver 3.0.2). Latest .net driver from firebirdsql.org is 7.0.1.

Is there a special requirement for FB setup regarding version of server, FB-client etc.?

3.0.2 is the last version that did work on .NET 3.5. We ship it for the compatibility purposes (it is used by the FB.NET driver profile). Unfortunately we cannot deploy 2 files with the same name to the same folder.

You need to install and setup the newer driver manually. What you need to do is:

  1. Download the driver
  2. Edit the configuration file to ensure that the driver version is correct

Both steps are quite simple.
1. Download the driver
Go to https://www.nuget.org/packages/FirebirdSql.Data.FirebirdClient/7.1.1 and click Download package (direct link https://www.nuget.org/api/v2/package/FirebirdSql.Data.FirebirdClient/7.1.1 )
Download the file, unpack it (it is a .zip archive file), go to lib\net452 and take the driver file.
Or just use this one: FirebirdSql.Data.FirebirdClient.zip (189.3 KB)

2. Edit the configuration file to ensure that the driver version is correct
Next to the Relativity server .exe file you can find a DataAbstract.daConfig file. Open it with any text editor and find the line <FB4.NET>. Next text line describes the database driver assembly version.
In your case it will look like

<AssemblyName Value="FirebirdSql.Data.FirebirdClient, Version=5.1.0.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c" />

Change it to

<AssemblyName Value="FirebirdSql.Data.FirebirdClient, Version=7.1.1.0, Culture=neutral, PublicKeyToken=3750abcc3150b00c" />

Save the file and restart Relativity

Thank you.
The key info was to edit DataAbstract.daConfig file. Now I got it working again.

Relativity Server: v10.0.0.1449
Data Abstract: v10.0.0.1449
RemObjects SDK: v10.0.0.1449
.NET Runtime: v4.0.30319.42000
Operating System: Microsoft Windows NT 6.2.9200.0
Platform: Win32NT

Here is my “component to code”. Login and fetching records is working as expected. Now I want to run simple insert scripts w/o any parameters, no MemTable etc.

var
  DARemoteDataAdapter_SQL: TDARemoteDataAdapter;

  DARemoteDataAdapter_SQL := TDARemoteDataAdapter.Create(Self);

  DARemoteDataAdapter_SQL.Name := 'DARemoteDataAdapter_SQL';
  DARemoteDataAdapter_SQL.DynamicSelect := True;
  with DARemoteDataAdapter_SQL.GetDataCall.Params.Add do begin 
    Name := 'Result';
    DataType := rtBinary;
    Flag := fResult;
    Value := Null;
  end;
  with DARemoteDataAdapter_SQL.GetDataCall.Params.Add do begin 
    Name := 'aSQLText';
    DataType := rtUTF8String;
    Flag := fIn;
    Value := 'select * from t_smp_hp order by time_update desc';
  end;
  with DARemoteDataAdapter_SQL.GetDataCall.Params.Add do begin 
    Name := 'aIncludeSchema';
    DataType := rtBoolean;
    Flag := fIn;
    Value := True;
  end;
  with DARemoteDataAdapter_SQL.GetDataCall.Params.Add do begin 
    Name := 'aMaxRecords';
    DataType := rtInteger;
    Flag := fIn;
    Value := -1;
  end;
  with DARemoteDataAdapter_SQL.UpdateDataCall.Params.Add do begin 
    Name := 'Result';
    DataType := rtInteger;
    Flag := fResult;
  end;
  with DARemoteDataAdapter_SQL.UpdateDataCall.Params.Add do begin 
    Name := 'aSQLText';
    DataType := rtUTF8String;
    Flag := fIn;
    Value := 'insert into t_smp_hp (name) values ('#39'test'#39')';
  end;
  DARemoteDataAdapter_SQL.RemoteService := RORemoteDataService;

DARemoteDataAdapter_SQL.UpdateDataCall.Execute();

Exception: FMessage ‘An exception was raised on the server: Script runtime error in beforeExecuteCommand: System.NullReferenceException: Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt.’

Hi,

I don’t see that you have changed DARemoteDataAdapter_SQL.GetDataCall.MethodName and DARemoteDataAdapter_SQL.UpdateDataCall.MethodName values.
As a result, you are trying to pass wrong parameters to GetData & UpdateData methods …

Sorry, “Component2Code” suppressed sth. I overlooked that.
I’ve changed MethodName. Please see screenshots:


Hi,

can you create a simple testcase with this issue, pls?
it will allow to faster help you.

you can attach it here or drop email to support@ if you want to keep it privately.

I sent you demo project support@…

thx. we investigate this case

bugs://83427 got closed with status fixed.

Hello

Remote access to this method was broken in the current build. Send a mail to support@ and we’ll provide you an updated Relativity Server build.

Still why do you need to call this method at all?
It is incredibly dangerous to allow remote execution of this method, as nothing prevents someone from sending in a command like DROP TABLE .. or DELETE FROM ....
Data Abstract provides feature named Schema Commands where remote client can send only parameters for a command without ability to performs SQL Injection attack or anyhow change the SQL statement to be executed.

Regards

Hello,

please provide an updated Relativity Server build as mentioned below.

Thank you.

Viele Grüße

Andreas

image001.jpg

Thank you for clarification.

Regarding using this method: We are in an early prototyping stage so we need flexibility. For the next project stage we’ll consider to switch to schema commands. Thank you for pointing this out.