Newest questions tagged interbase - Stack Overflow

Embarcadero InterBase 2020 Server Edition Update 6 views with more than 2 UNION ALL Select Statements Fails

2025-05-04T20:48:50Z

I downloaded the latest Interbase from here https://altd.embarcadero.com/download/interbase/2020/latest/InterBase_2020_Windows.zip and used the trial version.

Upon installation, either as 32-bit or as 64-bit, and creating a database with all the default settings, I cannot run a view with more than two Union All Select statements. It gives the error: arithmetic exception, numeric overflow, or string truncation.

Here is the minimum reproducible error:

Install Interbase 2020 (any version of Interbase 2020, including the latest update 6 as of the time of asking this, gives the same error).
Create a new database with the default parameters (I tried also altering the default database creation parameters but it made no difference).
Create this table:

CREATE TABLE TESTTABLE1
(
     AUTO1 integer NOT NULL
    ,NAME1 varchar(100)
    ,CONSTRAINT PK_TESTTABLE1_0 PRIMARY KEY (AUTO1)
);

Insert one entry into this table:

INSERT INTO TESTTABLE1 (AUTO1, NAME1) VALUES (1, 'STUDENT1');

Create a view with more than one Union All statement:

CREATE VIEW VIEW2 (AUTO1, CUST1) AS  
    SELECT
         T1.AUTO1
        ,T1.NAME1
    FROM TESTTABLE1 T1

    UNION ALL

    SELECT
         T2.AUTO1
        ,T2.NAME1
    FROM TESTTABLE1 T2

    UNION ALL

    SELECT
         T3.AUTO1
        ,T3.NAME1
    FROM TESTTABLE1 T3;

Select from this view:

SELECT * FROM VIEW2;

Error: Error at line 1: arithmetic exception, numeric overflow, or string truncation

I noticed that when I have only one UNION ALL SELECT statement, it works correctly.

I also noted that when you show System Data(tables) and access the Data tab of the table RDB$VIEW_RELATIONS, in the view with two UNION ALL statements, it has only one entry, inserting only the last table from the select list. In the view that works, it has two tables.

Question: Is there a workaround?

Failure when connecting to a LAN UNC path located Interbase database, using IBLite

2025-04-24T14:28:09Z

I'm writing a little POC console IBLite application for Windows, using Delphi and the FireDAC components

Here is a code that works without error:

  lDACConnection := TFDConnection.Create(nil);
  lFDPhysIBDriverLink := TFDPhysIBDriverLink.Create(nil);
  try
    lFDPhysIBDriverLink.Lite := true;
    lDACConnection.DriverName := 'IBLite';
    lDACConnection.Params.Add('Database=.\TestORM.IB'); // <- note the database is local to the application
    lDACConnection.Params.Add('User_Name=sysdba');
    lDACConnection.Params.Add('Password=masterkey');

    lDACConnection.Open(); // the connection opens gracefully

Now if I want to access that same database, located in a LAN UNC path (either directly or mapped to a drive):

    lDACConnection.Params.Add('Database=\\cabale7\<a_network_path_to_my_DB>\TestORM2.IB'); // <- replace lDACConnection.Params.Add('Database=.\TestORM.IB');

then I end up with this Error when opening the TFDConnection:

Project OneToManyTest.exe raised exception class EIBNativeException with message '[FireDAC][Phys][IBLite]Unable to complete network request to host "CABALE7". Failed to establish a connection. The system cannot find the file specified. '.

This is quite annoying because all my data, ie Delphi projects source code and databases, are stored on a LAN server (NAS).

Note: I noticed an Interbase config file, ibconfig, that is supposed to be deployed where the application is. Should this config file be configured /modified for resolving this issue?

Delphi app won't initialize IBX components on Wine (Ubuntu)

2025-01-21T03:01:01Z

Trying to get my Delphi application to run on Wine but it crashes initializing the IBX (InterBase Express) components. The call stack indicates the crash happens in TMBCSEncoding.Create with the error:

Invalid code page

I have tried installing the MS core fonts and tried playing around with the local settings, but I am not really sure what settings might apply to MBCS encoding.

Anyone managed to get an app using IBX to run on Wine?

command SHOW SYSTEM TABLES generate an error message

2024-12-16T18:08:39Z

Got an error message when trying to view a list of system tables on Interbase 2020 server with SHOW SYSTEM TABLES command.

Trying to view a list of system tables on Intebase 2020 server by using Interactive SQL utility. When executing command SHOW SYSTEM TABLES got such error message:

Executing of script failed Access violation at address 0000000000DA606C in module 'IBConsole.exe'. Read of address 0000657461657243

What I do wrong?

How to configure access to remote Interbase Database in emsserver.ini for Rad Server

2024-10-30T19:22:01Z

I have searched and I found this question: Hosting RAD Server in IIS. EMS license not found which is the error I am receiving. I have tried the solutions listed there, but I am still receiving the license not found issue.

The server at 192.168.2.28 holds a Rad Server and Interbase running a production web app and is properly licensed and works fine. We are attempting to spin up another server (currently for testing and maybe eventually for load balancing) and connect to the interbase db on 192.168.2.28.

The Data section of the emsserver.ini file on the new server looks like this:

InstanceName:192.168.2.28/gds_db
Database=C:\Users\Public\Documents\Embarcadero\EMS\emsserver1.ib
UserName=TheUerName
Password=ThePassword

As I mentioned, when I attempt to connect to this new server via Postman, I received the error:

"EMS license not found.  To resolve this issue, install an instance of Interbase with an EMS license and configure emsserver.ini to connect to the licensed instance."

As far as I can tell by reading the other post, I have this file configured correctly. We are using Delphi 10.3.1.

One question is, is my InstanceName configured correctly? The IP address is correct and gds_db appears to be the instance name running on 192.168.2.28.

Another question is, Do I need the emsserver1.ib file to also be located on my new server? Shouldn't it just exist on 192.168.2.28.
If it only needs to exist on 192.168.2.28, why do I need to specify the full path name to the file in the emsserver.ini on the new server? Should I specify the Database property differently somehow?

Do I just have the emsserver.ini file configured incorrectly? Do I need to make some change on 192.168.2.28 to allow this to work? Thank you for any help you can provide. Ken

InterBase find these character (Code 13: Carriage Return (CR) Code 10: Line Feed (LF) ) in database

2024-10-22T07:56:53Z

I am using InterBase 2007 to connect and update information in a database. I am importing data from a SQL Server database, while working on InterBase database.gdb file using Delphi in a DBgrid

I am getting a line break in some field as

"Cabezanellosa
, Salamanca"

While W want it to be like

"Cabezanellosa, Salamanca"

After copying the break character and search that on https://apps.timwhitlock.info/unicode/inspect, I got the character is UTF-8(10) and UTF-8(13) in decimal. I want to replace them with an empty string.

I use a program made by my office to export the data to a text file. When any field has this line break, that software stops operation and shows an error.

While working in SQL Server, I can easily find and replace those character using this query

SELECT * FROM data WHERE BIRTH_PLACE LIKE '%' + CHAR(10) + '%' OR BIRTH_PLACE LIKE '%' + CHAR(13) + '%'

UPDATE data 
SET BIRTH_PLACE = REPLACE(REPLACE(BIRTH_PLACE, CHAR(10), ''), CHAR(13), '')
WHERE BIRTH_PLACE LIKE '%' + CHAR(10) + '%' OR BIRTH_PLACE LIKE '%' + CHAR(13) + '%'

This query does not work in InterBase 2007. I also tried different AI generated query from multiple AI website as chatGPT, Claude, Copilot but none of them are working.

Is there any way I can update these character to empty string using InterBase 2007?

Problem with threads in an MDI application: application waits when thread is executing

2024-10-15T09:16:04Z

Over the past 15 years, I have been writing and maintaining an MDI application written in Delphi 7 that accesses a database. The program runs fine and I have no problems (normally) in adding or displaying data from the database.

A few days ago, I was tasked with creating a new form/report that calculates correlations from the data; this requires relatively dense code that does not allow usage of the UI whilst the correlation is being calculated. I thought that it would be better to have the correlation be calculated in a separate thread as I understand that this would 'free up' the UI. The thread sends a message to the main program when it has finished and this message causes the main program to open a new child window and display the correlation, so there is no use of 'synchronise'.

All of this works, BUT the application simply hangs and cannot be revived if I try to open any other form while the thread is executing. This obviates the whole point of using a thread! I have created a separate database connection within the thread, so this would seem not to be the problem.

The thread is invoked by the following code

DoCorrelThread (id1, id2, fdt, caption + ' ' + cbFromDate.text + ' Scale A = ' +
                            cbA.text + ', Scale B = ' + cbB.text, HandleTerminate);

HandleTerminate is a public procedure in the calling form; at the moment it prevents the calling form from being closed while the thread is executing - which is just dodging the real problem.

The thread code itself is as follows - this is an edited version that does not calculate correlations, but displays the problem.

unit correlthread;
interface

uses Windows, classes, DB, FMTBcd, SqlExpr, dbclient, messages, 
 sysutils, IBDatabase, IBCustomDataSet, IBQuery, Registry;

Procedure DoCorrelThread (ascale1, ascale2: longint; adt: tdatetime;
                      const atitle: string; proc: TMyProc);

implementation
type
 TCorrelThread = class (TThread)
 private
  scale1, scale2: longint;
  fdt: tdatetime;
  title: string;
  procedure Execute; override;
 public
  constructor Create (ascale1, ascale2: longint; adt: tdatetime;
                      const atitle: string; proc: TMyProc); reintroduce;
end;

constructor TCorrelThread.Create (ascale1, ascale2: longint; adt: tdatetime;
                              const atitle: string; proc: TMyProc);
begin
 inherited Create (true);
 FreeOnTerminate:= True;
 OnTerminate:= proc;
 scale1:= ascale1;
 scale2:= ascale2;
 fdt:= adt;
 title:= atitle;
 resume;
end;

procedure TCorrelThread.Execute;
var
 i, j, instance: integer;
 qTemp, qNewInstance: TIBQuery;
 ibdb: TIBDatabase;
 trans: TIBTransaction;

begin
 ibdb:= TIBDatabase.Create (nil);
 with TRegIniFile.create ('\software\nbn') do
  begin
   ibdb.databasename:= ReadString ('firebird', 'q4admin', '');
   free
  end;

 with ibdb do
  begin
   loginprompt:= false;
   params.add ('password=masterkey');
   params.add ('user_name=sysdba');
   sqldialect:= 1;
   connected:= true;
  end;

 trans:= TIBTransaction.create (nil);
 trans.defaultdatabase:= ibdb;
 qNewInstance:= TIBQuery.create (nil);
 qNewInstance.database:= ibdb;
 qNewInstance.Transaction:= trans;
 qNewInstance.sql.add ('select max (instance) from temp');

 qTemp:= TIBQuery.create (nil);
 qTemp.database:= ibdb;
 qTemp.transaction:= trans;
 qTemp.SQL.add ('insert into temp (instance, id, payload)');
 qTemp.sql.add ('values (:p1, :p2, :p3)');

 for i:= 1 to 100 do  // do something that takes some time
  begin
   qNewInstance.Open;
   instance:= qNewInstance.Fields[0].asinteger + 1;
   qNewInstance.Close;
   trans.active:= false;

   for j:= 1 to 100 do
    begin
     trans.StartTransaction;
     qTemp.Params[0].asinteger:= instance;
     qTemp.params[1].asinteger:= j;
     qTemp.params[2].asinteger:= i;
     qTemp.ExecSQL;
     trans.Commit;
     trans.active:= false;
    end;
  end;

 trans.free;
 ibdb.free;
 sendmessage (mainhandle, WM_ShowCorrel, instance, 0);
end;
Procedure DoCorrelThread (ascale1, ascale2: longint; adt: tdatetime;
                          const atitle: string; proc: TMyProc);
begin
 TCorrelThread.Create (ascale1, ascale2, adt, atitle, proc);
end;

end.

As I say, the code itself works fine, but the rest of the program cannot be accessed whilst the thread is executing. Where is the problem?

Scaffold Database Context Error "Object cannot be cast from DBNull to other type"

2024-06-21T16:12:16Z

I am using this command in the Package Manager Console: Scaffold-DbContext "data source=SOURCENAME;initial catalog=INTERBASE_DB;user id=username;password=password" InterBaseSQL.EntityFrameworkCore.InterBase -Tables tableName –o dbModel It builds correctly and then gives me the Object cannot be cast from DBNull to other types error.

All the solutions I am finding online have to deal with code rather than the scaffold command. Even though my database exists and has NULL values, should I still be able to scaffold? The connection to the database is fine as I can print out all the data...

 var cs = BuildConnectionStringBuilder().ToString();
 using (var connection = new IBConnection(cs))
 {
     connection.Open();
     using (var transaction = connection.BeginTransaction())
     {
         using (var command = new IBCommand("select * from artcust", connection, transaction))
         {
             using (var reader = command.ExecuteReader())
             {
                 
                 while (reader.Read())
                 {
                     var values = new object[reader.FieldCount];
                     reader.GetValues(values);
                     Console.WriteLine(string.Join("|", values));
                     
                 }
                 return new object[6];
             }
         }
     }
 }

My model would be a lot of manual typing of code and really do not want to do that!

Entity Framework Core Interbase -quotation marks issue

2024-02-05T14:45:35Z

I am facing an issue with Interbase connectivity using Entity Framework Core, following the guidelines provided in this documentation: https://docwiki.embarcadero.com/InterBase/2020/en/Entity_Framework. The problem arises in the following code snippet:

using (var db = new myContext())
{   
    var iqryble = db.Userlists.Select(d => d.USRID);
    var result = iqryble.ToList();
}

The generated SQL code includes double quotes around table and column names, which is not valid in the Interbase database. The error received is:

Error at line 1
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, char 11
.
SQL - SELECT "u"."USRID"
FROM "USERLIST" AS "u"

This issue is due to the double quotes. How can I fix this problem?"

Migrating from BDE to FireDac: Strange FireDac behaviour with Interbase and automatic transactions

2024-01-26T05:49:09Z

The isolated representation of the behavior is as follows: I have created a project with Delphi 11.3 and use FireDac components to access an Interbase database. All settings except the connection definition are default. A Select query is opened to query more than 500 records. The data is displayed in a grid and users can move through the data sets. This starts a transaction and keeps it open until the end of the data set is scrolled (or not). If an update is now carried out with a second update query, this does not trigger an automatic transaction (StartTransaction and then Commit) if the one from before is still open, and depending on this the change is visible to others or not. This behavior was different when using the BDE components and we could rely on automatic transactions in a large legacy application. Interestingly, the behavior compared to BDE has not changed when using Oracle as a database and our application works as expected. So if a transaction is autocommited or not depends on the grid having been scrolled to the end. That makes automatic transactions useless. What can I do to influence this behavior centrally without having to process hundreds of queries?

I examined the configuration options of the FDConnection component (AutoStart, AutoCommit...), but found no way to adjust the behavior as desired.

Cannot open gbk file - is it Firebird or Interbase?

2024-01-20T09:08:03Z

We have a .gbk file that we are trying to restore to a database, in order to convert it to Postgres, but we can’t figure out the ODS or even if it is actually an Interbase file, and not a Firebird file.

We ran downloaded gbak version UI-V3.0.10.33601 Firebird 3.0 and restored it. Running gstat on the resulting file gives us:

Database header page information:
    Flags           0
    Generation      1814
    System Change Number    0
    Page size       16384
    ODS version     12.2
    Oldest transaction  1803
    Oldest active       1804
    Oldest snapshot     1804
    Next transaction    1804
    Sequence number     0
    Next attachment ID  9
    Implementation      HW=AMD/Intel/x64 little-endian OS=Darwin CC=gcc
    Shadow count        0
    Page buffers        0
    Next header page    0
    Database dialect    1
    Creation date       Jan 19, 2024 23:35:51
    Attributes      no reserve

    Variable header data:
    Sweep interval:     0
    *END*

Trying to open it with DBeaver on a Mac (as Firebird):

I/O error during "open" operation for file “myfilename”; Error while trying to open file; Operation not permitted [SQLState:08001, ISC error code:335544344]

Trying to open it with DBeaver on a PC (as Firebird):

unsupported on-disk structure for file “myfilename”: found 32780.18, support (null) (null)

I presume this means it expected 18, and I read that 32779, that is 1 lower than ours, indicates that i is a Firebird 2.x file. Is it a Firebird file because we used gbak as supplied by Firebird? It is the only version we got to work.

Trying with Interbase 2020 togo installation (PC)

gstat on the gdb file:

Wrong ODS version, expected 18, encountered 32780

Also, running gbak on our gbk file that we started with gives:

gbak: ERROR: Expected backup version less than or equal to 7. Found 9

So in summary we can’t figure out what is going on at all, it’s not clear if the file is too new or too old, or even if it is Firebird or Interbase. What is the right approach?

Unable to connect to Interbase 2020 Database with PHP 8.2

2023-12-24T01:23:48Z

We recently installed Interbase Server 2020 on Windows Server 2019 with Apache Server 2.4.57 (Win64) OpenSSL/3.1.2 PHP/8.2.10

I am able to connect to the Interbase SQL Database remotely with IBConsole Administrator Database Utility on port 3050.

The following link show the php info including the interbase extension:

http://70.182.48.236/testphp.php

I am not able to connect to the Interbase SQL Database remotely with PHP 8.2.

$db = ibase_connect( DB_HOST.DB_NAME, DB_USER, DB_PASSWORD, 'ISO8859_1', 100, 1) OR die('Unable to connect to the loan servicing database. ');

I receive the following error:

Warning: ibase_connect(): connection rejected by remote interface in C:\Program Files\Apache2\htdocs\test-sqlconnect.php on line 24

What can I do to connect to the Interbase 2020 Database remotely with PHP 8.2?

FireDAC database unavailable when trying to connect

2023-12-17T12:31:35Z

When attempting to make a connection (named MyConnection) on the fly with Interbase server I get the message unavailable database.

Instead, it works fine if I assign a previosuly declared TFDConnection (named FDConnection1) to the connection variable.

I also assigned all properties of FDConnection1 to MyConnection attempting to make them identical.

Still I am getting the same error message.

So I am presuming that there is some unknown twist that I could not find in my researches, that establishes the connection succesfully when assigning a previosuly declared TFDConnection to the connection variable.

Follows code snippets:

working code

...
implementation
...
var MyConnection : TFDConnection;
...
procedure MakeConnection;
begin
  MyConnection := dmConn.FDConnection1; // resides in a data module
  MyConnection.Connected := True; // works fine
  {dmConn.FDConnection1 properties
     Name        = FDConnection1
     DriverName  = IB
     LoginPrompt = False
     Params 
       Database = C:\MyDatabase
       DriverId = IB
       Password = masterkey
       UserName = SYSDBA
       InstanceName = instance2 // name of the server instance
       OsAuthent    = No
       Port         = 3050
  }
End;

not working code

...
implementation
...
var MyConnection : TFDConnection;
...
procedure MakeConnection;
begin
  MYConnection := TFDconnection.Create (Self);
  with MyConnection do begin
       Name        := 'MyConnection1';
       DriverName  := 'IB';
       LoginPrompt :=  False;
       with Params do begin
            Clear;
            Database := 'C:\MyDatabase';
            DriverId := 'IB';
            Password := 'masterkey';
            UserName := 'SYSDBA';
            Add ('InstanceName = instance2'); // name of the server instance        Add ('OsAuthent    = No');
            Add ('Port         = 3050');
       end;
       Connected := True; // error message at this point
   end;
end;

As you can see, all properties are exactly the same.

Follows error message:

Any help with this issue will be highly appreciated.

Testing for existence of a row and insert it if not present in InterBase

2023-11-13T20:01:40Z

I am looking for a way to insert rows into an InterBase table if they do not already exist in the table. I cannot guarantee that the InterBase table is not empty to begin with.

In SQL Server, I could do something like the WHERE NOT EXISTS in the following script sample where the output is the same whether or not the commented line is uncommented or not. In SQL Server, the script continues to function even if the first INSERT INTO is also commented out.

CREATE TABLE #Table(A VARCHAR(3), B INT, C INT);
INSERT INTO #Table(A, B, C) VALUES ('a1', 1, 11);
--INSERT INTO #Table(A, B, C) VALUES ('a2', 2, 12);

INSERT INTO #Table(A, B, C)
    SELECT 'a2', 2, 12
    WHERE NOT EXISTS (
        SELECT 1
        FROM #Table WHERE A='a2' AND B=2 AND c=12)

SELECT * FROM #Table;

DROP TABLE #Table;

How would I do the equivalent of this in InterBase?

Note, there is a second question embedded in this script because I know "FROM" is required by syntax for a "SELECT" in InterBase and there is no "FROM" in the line "SELECT 'a2', 2, 12", and I am curious what the best way to handle that is from someone experienced with InterBase.

Error 104 after creating domain in Interbase 2020 script

2023-10-31T09:31:26Z

I am a beginner with Interbase (always used Advantage and SQL Server) and I am now running into a problem that is probably very easy to fix, but I'm just not seeing it (nor can I find a usable hint in the documentation). Please consider the following (condensed script):

CREATE DOMAIN DOM_ID AS INTEGER;
CREATE TABLE Relatie (
  ID DOM_ID NOT NULL,
  RelatieNaam VARCHAR(100),
  Bedrijf VARCHAR(40),
  CONSTRAINT PK_RELATIE PRIMARY KEY (ID)
);

This script fails (using 'Prepare') with error message 'Dynamic SQL error. SQL Error code = -104. Token unknown - line 2, char 1 CREATE'.

I first thought creating the domain requires a Commit but as per documentation that's not required here since it's a DDL statement.

What am I doing wrong / am I overlooking?

INTERBASE bad parameter number error when using subqueries

2023-10-05T05:47:52Z

I have been working on switching over to an Interbase database from SQLITE. One of the issues I have run into is a "bad parameter number" error. So, I went back to one of the sample databases that Embarcadero supplies (EMPLOYEE) to tried various SQL commands to reproduce the problem.

So here is a scenario: I want a query that gives the average sales amount for all sales to a particular customer over a pre-specified amount. So, here is a query that works for that:

SELECT AVG(TOTAL_VALUE) AS AvgSales
FROM SALES
WHERE TOTAL_VALUE > :TOTAL_VALUE
  AND CUST_NO = :CUSTNO

Now, I want to store that average in a table. But how can you do that? Here is what I tried:

UPDATE <table>
SET <Column> = (SELECT AVG(TOTAL_VALUE) AS AvgSales
                FROM SALES
                WHERE TOTAL_VALUE > :TOTAL_VALUE
                  AND CUST_NO = :CUST_NO)

But when you try to execute that, it gives the error message "bad parameter number"

If you hard code the two parameters, it works fine:

UPDATE <table>
SET <Column> = (SELECT AVG(TOTAL_VALUE) AS AvgSales
                FROM SALES
                WHERE TOTAL_VALUE > 10000
                  AND CUST_NO = 1001)

After more experimenting, it looks to me like any parameter in a subquery will generate that same error message rather than executing. For example, this also generates the same error message (and hard coding the parameters works):

SELECT AMT
FROM
    (SELECT AVG(TOTAL_VALUE) AS AvgSales
     FROM  SALES
     WHERE TOTAL_VALUE > :TOTAL_VALUE
       AND CUST_NO = :CUSTNO
    ) X(Amt)

So, how do you go about storing such a value in an Interbase table?

Connect remote Interbase database freeze my app

2023-09-26T08:30:25Z

I have an application (C++Builder, Delphi) and I connect to remote Interbase database via IBX (TIBDatabase).

When try to connect to database using network and the IP address dosn't exists, so the application freeze for 20 seconds until the error message (Unable to complete network request to host "172.16.1.144:3050". Failed to establish a connection.) appears.

I try to connect to remote database using a new thread, also the app freeze.

How can try to connect without freeze the whole app ??

Thanks.

Shutdown Interbase Database from Delphi

2023-08-22T20:44:45Z

Using the TIBConfigService I'm attempting to shutdown an Interbase database using its ShutdownDatabase method like so:

ibConfig.ShutdownDatabase(Forced, 0);

This ends up with an error, "Invalid Service Handle".

Setting the Active property on the control is successful and other methods on the TIBConfigService such as SweepDatabase work as expected.

How can I resolve the "Invalid Service Handle" error?

Entity Framework Core Interbase SQL 7.13.1 error

2023-07-10T06:55:02Z

I Got this error doing a query with parameters with .NET entity framework

Exception thrown: 'InterBaseSql.Data.InterBaseClient.IBException' in System.Private.CoreLib.dll Dynamic SQL Error SQL error code = -104 Token unknown - line 3, column 29

I upgraded to 7.13.1 and then got this error

Microsoft.EntityFrameworkCore.DbUpdateException
  HResult=0x80131500
  Message=An error occurred while saving the entity changes. See the inner exception for details.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__29.MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__9.MoveNext()
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__103.MoveNext()
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__107.MoveNext()
   at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__60.MoveNext()
   at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__60.MoveNext()
   at Common.DataAccess.EFCore.Repositories.BaseUserRepository`2.<Edit>d__2.MoveNext() in C:\bundle2\bundle-interbase\backend\src\Common\Common.DataAccess.EFCore\Repositories\BaseUserRepository.cs:line 45
   at Common.Services.UserService`1.<Edit>d__5.MoveNext() in C:\bundle2\bundle-interbase\backend\src\Common\Common.Services\UserService.cs:line 45
   at Common.WebApiCore.Controllers.UsersController.<Create>d__7.MoveNext() in C:\bundle2\bundle-interbase\backend\src\Common\Common.WebApiCore\Controllers\UsersController.cs:line 75
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.<Execute>d__0.MoveNext()
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<<InvokeActionMethodAsync>g__Awaited|12_0>d.MoveNext()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<<InvokeNextActionFilterAsync>g__Awaited|10_0>d.MoveNext()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<<InvokeFilterPipelineAsync>g__Awaited|20_0>d.MoveNext()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<<InvokeAsync>g__Logged|17_1>d.MoveNext()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<<InvokeAsync>g__Logged|17_1>d.MoveNext()
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<<Invoke>g__AwaitRequestTask|6_0>d.MoveNext()
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.<Invoke>d__9.MoveNext()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.<Invoke>d__6.MoveNext()
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.<Invoke>d__5.MoveNext()
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.<Invoke>d__4.MoveNext()
   at Common.WebApiCore.Setup.ErrorHandlingMiddleware.<Invoke>d__3.MoveNext() in C:\bundle2\bundle-interbase\backend\src\Common\Common.WebApiCore\Setup\ErrorHandlingMiddleware.cs:line 32

  This exception was originally thrown at this call stack:
    [External Code]

Inner Exception 1:
IBException: Dynamic SQL Error
SQL error code = -303
internal error

Trying to add a new user

Thanks

Craig

Inner Exception 2:
IscException: Dynamic SQL Error
SQL error code = -303
internal error

adding a new user with roles and claims

64bit FireDac connection to Interbase requires unnecessary ibtogo64.dll?

2023-06-01T13:16:18Z

After converting my Delphi Alexandria 11.3 program from 32 to 64 bits, I noticed that connecting to a (64bit) FireBird database required other DLLs.

In FireDac.Phys.IBWrapper this procedure gets called with

AVendorHome=''
AVendorLib=''
ALibNames=('ibclient64.dll', 'ibtogo64.dll')

procedure TIBLib.LoadBase(const AVendorHome, AVendorLib: String; AThreadSafe: Boolean
  {$IFNDEF FireDAC_IB_STATIC} ; const ALibNames: array of String {$ENDIF});
{$IFNDEF FireDAC_IB_STATIC}
var
  sDLLName: String;
  aDLLNames: array of String;
  i, j, n: Integer;
{$ENDIF}
begin
  FThreadSafe := AThreadSafe;
{$IFNDEF FireDAC_IB_STATIC}
  for j := 1 to 2 do begin
    sDLLName := AVendorHome;
    if sDLLName <> '' then begin
      sDLLName := FDNormPath(sDLLName);
      if j = 2 then
        sDLLName := FDNormPath(sDLLName + C_FD_DLLFolder);
    end
    else if j = 2 then
      Break;
    n := Length(aDLLNames);
    if AVendorLib <> '' then begin
      SetLength(aDLLNames, n + 1);
      aDLLNames[n] := sDLLName + AVendorLib;
    end
    else begin
      SetLength(aDLLNames, n + Length(ALibNames));
      for i := 0 to Length(ALibNames) - 1 do            <-- HERE
        aDLLNames[n + i] := sDLLName + ALibNames[i];
    end;
  end;
  inherited Load(aDLLNames, True);
{$ELSE}
  LoadEntries;
  GetLibraryInfo;
{$ENDIF}
end;

What this does is filling the initially empty aDLLNames with the two entries in ALibNames ('HERE') The inherited Load(aDLLNames, True) (the True meaning Required) then tries to load both DLLs with inherited LoadLibrary(ADLLNames, ARequired)

I copied the two files ibclient64.dll and interbase.msg from c:\Users\Public\Documents\Embarcadero\Interbase\redist\InterBase2020\win64\ to my program directory, but the above code also requires ibtogo64.dll, so this then fails on the ibtogo64.dll with 'Cannot load vendor library':

My code does not use IBToGo.

The array with the two names was set up in ('HERE'):

procedure TIBLib.LoadIB(const AVendorHome, AVendorLib: String;
  AEmbedded, AThreadSafe: Boolean);
{$IFNDEF FireDAC_IB_STATIC}
const
  C_GDS:      String = {$IFDEF MSWINDOWS} {$IFDEF FireDAC_64} 'ibclient64' {$ELSE} 'gds32' {$ENDIF} {$ENDIF}
                       {$IFDEF POSIX} 'libgds' {$ENDIF} + C_FD_DLLExt;
  C_TOGO:     String = {$IFDEF MSWINDOWS} {$IFDEF FireDAC_64} 'ibtogo64' {$ELSE} 'ibtogo' {$ENDIF} {$ENDIF}
                       {$IFDEF POSIX} 'libibtogo' {$ENDIF} + C_FD_DLLExt;
{$ENDIF}
begin
{$IFDEF ANDROID}
  FDSetEnv('INTERBASE', TPath.GetDocumentsPath + PathDelim + 'interbase');
{$ENDIF}
  if AEmbedded then
    // Force ToGo
    LoadBase(AVendorHome, AVendorLib, AThreadSafe
{$IFNDEF FireDAC_IB_STATIC},
      [C_TOGO]
{$ENDIF}
    )
  else
    LoadBase(AVendorHome, AVendorLib, AThreadSafe
{$IFNDEF FireDAC_IB_STATIC},
  {$IF DEFINED(MACOS) and not DEFINED(IOS)}
      // MacOSX: InterBase ToGo is preferred, and thus LIBIBTOGO has a higher priority
      [C_TOGO, C_GDS]
  {$ELSEIF DEFINED(IOS) or DEFINED(ANDROID)}
      // iOS, iOSSim, Android: no client/server, and thus no LIBGDS
      [C_TOGO]
  {$ELSE}
      // Windows: client/server is preferred over ToGo
      [C_GDS, C_TOGO]       <-- HERE
  {$ENDIF}
{$ENDIF}
  );
end;

Isn't that strange? If I have ibclient64.dll, why require ibtogo64.dll? Should I report this as a bug?

And the next question is what the best solution is (assuming that ibtogo64.dll is indeed not necessary). Distribute ibtogo64.dll with my app as well ^*, or hack the code to not include C_TOGO in the array?

That conditional define FireDAC_IB_STATIC is set in FireDac.Inc as:

{$IF DEFINED(IOS) and (DEFINED(CPUARM) or DEFINED(CPUARM64)) or DEFINED(ANDROID)}
  {$DEFINE FireDAC_IB_STATIC}            // Use IB driver static linking
  {$DEFINE FireDAC_IB_STATIC_IBBRAND}    // Use IbTogo static linking library
{$ENDIF}

Using that define would seem to 'solve' the issue, were it not that the definition of TIBLib.LoadBase changes. I'd probably open a can of worms trying to use that.

^{* Actually , there are 3 DLLs in c:\Users\Public\Documents\Embarcadero\Interbase\redist\InterBase2020\win64_togo\: ibtogo64.dll, ibxml64.dll and ib_util64.dll}

UTF8 in a Delphi DBMemo to/from an Interbase BLOB field

2023-03-13T18:21:23Z

Summary: I am trying to work with UTF8 text in a TDBMemo to store, retrieve and display Greek letters from/to a BLOB subtype 0 field in an Interbase table. The Greek letters display correctly in the DBMemo but when posted to the database they are garbled, and display garbled when retrieved.

Delphi 10.4, Interbase 2020, Windows 10

I have a TDBMemo connected to a BLOB field in an Interbase table using FireDAC:

TFDConnection --> TFDQuery --> TDataSource --> TDBMemo

This is in a simple test program, with no event handlers attached to any component in the chain. The database table field is BLOB subtype 0 (i.e. binary). The data in the field displays correctly as text in the DBMemo if there are no UTF8 (Greek, Cyrillic, etc. - non-Latin) characters in it, and updates and retrieves the text correctly, and works correctly with any Latin alphabet with non-ASCII characters, like French, but all non-Latin UTF8 characters are garbled in the DBMemo and in the table field after posting changes. If I paste Greek letters into the DBMemo they display correctly (so the DBMemo is handling UTF8 correctly) but are garbled after a post to the table. The table field is a binary BLOB, which should store whatever is posted to it without changing the data. That means the text is not being kept as UTF8 somewhere along the chain from the DBMemo to the FDQuery - the text is being converted to an ANSI string.

I have tried (using the ANSI to UTF8 functions) to stuff the DBMemo.Text into the table field directly - didn't work. I have tried streaming the data from DBMemo.Text into the BLOB field (TStringStream --> TBLOBStream) - didn't work. I have tried using a BLOB subtype 1 (text) field with explicit charset UTF8 instead of the binary BLOB - same result. Examining the data in the Delphi debugger immediately before the post shows the DBMemo.Text is correct. Displaying the text BLOB UTF8 field with isql shows it has been garbled in the table.

There may be a problem with the BLOB text field in the table: if I try to update the field directly in IBConsole isql with

update biblio set greekfield = ἡλιοδρόμος' where id=10793

update biblio set greekfield = cast('ἡλιοδρόμος' as varchar(2000) character set UTF8) where id=107937

it fails with "Cannot transliterate character between character sets". But maybe that's just IBConsole being silly.

Since Delphi 10 is fully Unicode compliant - and so is Interbase 2020 - I expected Delphi to be able to handle the Greek UTF8 characters, at the very least when using an explicitly UTF text BLOB table field in Interbase. Obviously I am doing something wrong, but after several hours of trying everything I can think of I still can't get it to work.

Help!

David

Interbase 2020 crashes/loops

2023-01-23T18:07:39Z

We use Interbase 2020 as production DB using UTF8 (approx 250 simultaneous user). With this database we have two main problems that we are not able to solve.

In history we had a problem with an older udf-function that crashed our database because it was not ready for unicode string operation. As a result we changed to unicode compatible versions.

The last few years sometimes we get hiccup (as we call it). In this case every client looses connection and the guardian restarts. The clients can connect again without us doing anything.

The second problem is that sometimes the interbase does not crash but everyone looses the connection and it is not possible to reconnect (by client, or ibexpert for example). In this case we have to restart the whole server.

These problems are occuring irregular. Most times it first starts with a hiccup. After a time (maybe two to ten hours later), the second problem arrives and we need to restart our database. If we are lucky we need to restart the server 2-3 times, on a bad day we need to restart the server more often as the second problem returns again and again (for example every 30 minutes).

We are not yet able to locate this problem. It doesn't matter if a user is connected to the database or just idling on weekends. It also often happens when nobody is connected.

Even the server logs don't give hints that helped us yet.

-We minimized udf function use as low as possible, changed to newer udfs that support unicode etc. -functions that crash the server (afaik) are guarded that they dont get for example invalid datetimes -We update database server regularely to newest version -also updated client dlls -also updated connection components (IBDAC) + Delphi 11.1 -wrote exception tracker in our client software (unfortunately there is only the connection lost error) -regularely check active transactions if something hangs/loops/snapshot creation

Do you have any information that we could use to solve our problems? Is there any possibility to get more info out of the log files (other log levels possible?)? We don't want to log every procedure call if not necessary, but if there are no other options we need to..

Thanks for your help!

Firedac with Interbase some forms only updates database after closing appalication

2022-10-19T10:49:21Z

I'm using firedac with Delphi 10.3 with Interbase, when i use an update SQL statement in more forms it will work perfect.

But in one form i do exact the same the table is not updated, but when i close the form and close the application the update is done to the database.

I'm completely lost and i don't know how this is possible.

I found a part of the problem:

When i use Commit it works, but when there is a open command between two update statements i need to use StartTransAction before the open en Commit after the open. Then the second update works:

DM_IB.IBF_VHE.ExecSQL('Update BB_ASML_FORECAST set Uren_Montage = 100, Uren_Berekend = ''Ja'' ' +
                        'where AANMAAK_DATUMTIJD = ''19-10-2022 10:12:38'' and ItemCode = ''4022.472.18632'' ') ;

    DM_IB.IBF_VHE.Commit ;

    ShowMessage('1') ; // Works no problems

  MEM_VPA.DisableControls ;
  MEM_Fouten.DisableControls ;

  Post_Confirm := False ;

  Try
    MEM_VPA.Close ;
    MEM_VPA.Open ;
    MEM_VPA.EmptyTable ;

    MEM_Fouten.Close ;
    MEM_Fouten.Open ;
    MEM_Fouten.EmptyTable ;

    With TFDQuery.Create(nil) do
    Try
      Connection := DM_IB.IBF_VHE ;
      SQL.Text   := 'select * from PL_CAPACITEIT_NPW where ' +
                    '(jaar = ' + FormatDateTime('yyyy', now()) + ' and ' +
                    'Weeknummer > ' + IntToStr(WeekofTheyear(Now())) + ') or ' +
                    '(jaar = ' + FormatDateTime('yyyy', IncYear(Now())) + ' and ' +
                    'weeknummer < ' + IntToStr(52 - (52 - WeekofTheyear(Now()))) + ') order by jaar, weeknummer' ;
    DM_IB.IBF_VHE.StartTransaction ; // when this line is nod add and the commit the update below is not working till i end the application
    Open ;
    DM_IB.IBF_VHE.Commit ;

    DM_IB.IBF_VHE.StartTransaction ;

    DM_IB.IBF_VHE.ExecSQL('Update BB_ASML_FORECAST set Uren_Montage = 100, Uren_Berekend = ''Ja'' ' +
                        'where AANMAAK_DATUMTIJD = ''19-10-2022 10:12:38'' and ItemCode = ''4022.472.88135'' ') ;

    DM_IB.IBF_VHE.Commit ;

    ShowMessage('2') ; // Works only when de above open command is between a transacion.

Thanks in advice,

How to debug Array DML FireDAC queries?

2022-10-08T12:23:20Z

I'm trying to insert values taken from a comma-seperated file into an Interbase SQL DB using Array DML. The Problem is, I'm having a plethora of issues with

arithmetic, numeric overflow or string truncation

errors when trying to execute the DML queries, which is most likely due to a loop going too far.

While DML is faster than using conventional loops and pushing a query for each line of the file, it is hard for me to debug as I cannot see the queries being pushed at the end or during step-by-step debugging.

Is there a method for viewing the queries being pushed? FDQuery doesn't seem to offer this possibility as of now, so it's hard for me to tell what went wrong during runtime.

The csv file used can be found here, and here's the code :

if FileExists(OpenDialog1.FileName) then
begin
  Strings := TStringList.Create;
  CurrentLine := TStringList.Create;

  try
        Strings.Clear;
        Strings.LoadFromFile(OpenDialog1.FileName);

        iArraySize := Strings.Count;
        FDQuery1.SQL.Text := 'INSERT INTO tbl_post4 (lngpostid, strname, strdesc, ysnisdefault, memLabelList, memNotesText, lnggroupid, ysnisgroup, dtiCreated, dtiModified) VALUES(:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10);';
        FDQuery1.Params.ArraySize := iArraySize;

           for m := 1 to Strings.Count - 1 do   //starting at second line of file since first is used for column names
           begin
              CurrentLine.Clear;
              Split(',', Strings[m], CurrentLine);
              for n := 0 to CurrentLine.Count - 1 do
              begin //  replace empty with space && escape single quotes if found
                    if CurrentLine[n].IsEmpty then
                    begin
                      CurrentLine[n] := ' ';
                    end;
                    if CurrentLine[n].Contains('''') then
                    begin
                      CurrentLine[n] := StringReplace(CurrentLine[n], '''', '''''', [rfReplaceAll, rfIgnoreCase]);
                    end;

              end;

              FDQuery1.Params[0].AsIntegers[m] := CurrentLine[0].ToInteger;
              FDQuery1.Params[1].AsStrings[m] := CurrentLine[1];
              FDQuery1.Params[2].AsStrings[m] := CurrentLine[2];
              FDQuery1.Params[3].AsBooleans[m] := CurrentLine[3].ToBoolean;
              FDQuery1.Params[4].AsStrings[m] := CurrentLine[4];
              FDQuery1.Params[5].AsStrings[m] := CurrentLine[5];
              FDQuery1.Params[6].AsIntegers[m] := CurrentLine[6].ToInteger;
              FDQuery1.Params[7].AsBooleans[m] := CurrentLine[7].ToBoolean;
              FDQuery1.Params[8].Values[m] := CurrentLine[8];
              FDQuery1.Params[9].Values[m] := CurrentLine[9];


           end; //end for m

        FDConnection1.StartTransaction;
        FDQuery1.Execute(FDQuery1.Params.ArraySize);
        FDConnection1.CommitRetaining;
           
        ShowMessage('Data imported');
        CurrentLine.Free;
        Strings.Free;
  except
     on E : Exception do
        begin
          ShowMessage(E.Message);
          FDConnection1.RollbackRetaining;
          FDConnection1.Commit;
          CurrentLine.Free;
          Strings.Free;
        end;

  end;
  FDConnection1.Commit;

  end
  else
  begin
     raise Exception.Create('File does not exist.');
  end;



  // split function for reference
  procedure Split(Delimiter: Char; Str: string; ListOfStrings: TStrings) ;
  begin
      ListOfStrings.Clear;
      ListOfStrings.Delimiter       := Delimiter;
      ListOfStrings.StrictDelimiter := True; // Requires D2006 or newer.
      ListOfStrings.DelimitedText   := Str;
  end;

RAD Server Lite does not install on Linux

2022-09-25T06:13:45Z

When installing Delphi RAD Server Lite on Linux CentOS 7 (RH 7 like), I get an error:

[FireDAC][Phys][IB]-314. Cannot load vendor library [libgds.so or libibtogo.so]

Steps taken :

Installed Delphi 11.2 on Windows 10 machine --> OK
Configured PAServer for Linux CentOS 7 -- > OK
Copied EMS files from C:\Program Files (x86)\Embarcadero\Studio\22.0\EMSServer to Linux server and run sudo ./ems_install --> OK
Copied Interbase IBToGo files from C:\Users\Public\Documents\Embarcadero\Interbase\redist\InterBase2020\linux64_togo to Linux server /usr/lib/ems/ --> OK
Downloaded RAD Server Lite license from https://reg.embarcadero.com/srs6/promotion.jsp?promoId=572 --> OK
Copied license file to Linux server /usr/lib/ems/interbase/license --> OK
Started EMS server, first time run for setup, in Linux server sudo ./EMSDevServerCommand -setup ===> FAILED !!

After confirming the questions, the final result was an error message and EMSServer was not installed properly, see the results of ./EMSDevServerCommand :

>start
EMS server configuration file not found. Run the configuration wizard?(y)
Set up Options 
Server Instance ()? 
DB file name (emsserver.ib)? 
DB file directory (/usr/lib/ems)? 
Sample data(y) 
Console User (consoleuser)?
Console Password (consolepass)?
----------------------------
Set up Options
Server Instance: <default>
DB file name: emsserver.ib
DB file directory: /usr/lib/ems
Sample data: True
Console User: consoleuser
Console Password: consolepass
DB file: /usr/lib/ems/emsserver.ib
Configuration file: /etc/ems/emsserver.ini
----------------------------

-  Continue with these options?(y)

- An error occurred when trying to load your InterBase license. Verify that you have entered
  valid connection parameters for the new database. Also verify that the Interbase server
  instance is running. [FireDAC][Phys][IB]-314. Cannot load vendor library [libgds.so or
  libibtogo.so]. Hint: check it is in the PATH or application EXE directories, and has x64
  bitness.

Installing IBConsole on client PC

2022-09-05T19:38:38Z

Does anyone know if it's possible to install IBConsole on a client PC and then connect to the server (in LAN)?

I installed Interbase 2020 on my server together with IBConsole; all working fine.

I have been searching everywhere how to install IBConsole on my desktop but cannot seem to find anything. A "Client Only" installation of Interbase didn't install it either.

Is it possible/allowed to do this and if so, how?

Equivalent of Firebird "when any" for SQL exception trapping in InterBase

2022-08-22T09:31:58Z

I need to extend an app written with Embarcadero Firemonkey for Windows to Android, so I need InterBase.

Besides there is no tutorial (none found so far at least) on how to use Firebird under Android, I have found a very important difference not only in the language itself, but in the way I can trap exceptions with Firebird and InterBase (i.e. into an insert stored procedure). Here is a sample Firebird code:

ALTER PROCEDURE AD_0_LIST_UPD_ORDER (
AD_0_NAME VARCHAR(20),
AD_0_ORDER INTEGER)
RETURNS (N_ERROR INTEGER)
AS
begin
   n_error=0;
   begin
      update ad_0_list
            set ad_0_order = :ad_0_order
          where (ad_0_name = :ad_0_name);
      when any do begin
        n_error=sqlcode;
        exit;
      end
   end
end^

What's beautiful in those few lines (when any ...) is that I have an exception that traps any SQL error, while in InterBase I have to (imagine) and write situations like -803, -625, theoretically from -1 to -999 for every procedure.

I can't fine a way to translate it. Is there a way to do this in InterBase?

The char* fn_Substr(char* s, short m, short n) function does not return the correct value

2022-07-18T11:57:21Z

I am developing a user defined function dll ib_udf64cpp.dll to my InterBase 2020 database.

I am using Embarcadero C++ Builder Alexandria 11.1 to develop and test the functions exported from the ib_udf64cpp.dll.

The code of the exported fn_SubStr(...) function from the ib_udf64cpp.dll is:

/*===============================================================
 fn_SubStr(s, m, n) - Returns the substr starting m ending n in s.
================================================================= */
char* EXPORT fn_SubStr(char* s,
                short m, /* starting position */
                short n) /* ending position */
{
    short i = 0;
    short j = 0;
    char buffer = (char *)ib_util_malloc(256);

    if (m > n || m < 1 || n < 1)
        return "Bad parameters: m, n in substring";
    else
        {
            while (*s && i++ < m-1) /* skip */
                s++;

            while (*s && i++ <= n)  /* copy */
                buffer[j++] = *s++;

            buffer[j] = '\0';

            return buffer;
        }
}

This function uses the ib_util_malloc(256) function from InterBase 2020 API to initialize the char* buffer variable.

UDFs must allocate memory using ib_util_malloc() rather than static arrays in order to be thread-safe

When I debug the code of this function with: s = "1234567890"; m = 2; n = 7.

I get the following value of buffer = 0x0000000000894690 "234567".

The main program DLLDebugger used to debug the ib_udf64cpp.dll has the following code: (Note: InterBase recommends to use the cdecl calling convention.)

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop
#include <stdlib.h>
#include <libloaderapi.h>
#include "ufrmDLLDebugger.h"
#include "uIB_UDF64cpp.h"
#pragma comment (lib, "ib_udf64cpp.a")
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
#pragma comment(lib, "ib_udf64cpp")

TfrmDLLDebugger *frmDLLDebugger;

const wchar_t* ib_udf64cpp_dll = L"d:/MyFiles/Database/IB_UDF64C/Cpp/Win64/Debug/ib_udf64cpp.dll";

extern "C" __declspec(dllimport) char*  __cdecl fn_SubStr(char*, short, short);
typedef char   (*SUBSTR)(AnsiString, ShortInt, ShortInt);

SUBSTR      fnc_SubStr;

//---------------------------------------------------------------------------
__fastcall TfrmDLLDebugger::TfrmDLLDebugger(TComponent* Owner) : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TfrmDLLDebugger::btnExecutarUDFClick(TObject *Sender)
{
    int m, n;
    AnsiString astr;

    HINSTANCE dllhandle = LoadLibrary(ib_udf64cpp_dll);

    if (dllhandle == NULL)
    {
        ShowMessage("Error: The library ib_udf64cpp.dll was not loaded!");
    }
    else
    { switch(rdgSelUDF->ItemIndex)
        {
         case -1 : ShowMessage("Select one of the UDFs.");
                             break;

         case  3 : {fnc_SubStr = (SUBSTR)GetProcAddress(dllhandle, "fn_SubStr");
                             if (Trim(edS->Text) == "")
                             {
                                 ShowMessage("The [s:] field is empty");
                                 break;
                             }

                             if (Trim(edM->Text) == "")
                             {
                                 ShowMessage("The [m:] field is empty.");
                                 break;
                             }
                             if (Trim(edN->Text) == "")
                             {
                                 ShowMessage("The [n:] field is empty.");
                                 break;
                             }
                             astr = AnsiString(edTexto->Text);
                             m = StrToInt(edM->Text);
                             n = StrToInt(edN->Text);
                             astr = fnc_SubStr(astr, m, n);
                             lblResult->Caption = astr;
                             break;}
     }
    }
}

The returned value of fn_Substr() assigned to lblResult->Caption should be "234567" instead of "`".

How can I assign the correct returned value of buffer from fn_Substr(s, m, n) to the lblResult->Caption?

With php ibase is there any way of returning to the first row after reading several rows (to avoid re-querying the database)

2022-06-22T11:06:56Z

With php ibase, ibase_query fetches the relation, several functions can then fetch rows from that relation, but all these functions extract the next row.

In Delphi I have the ability to return to the first row (DataSet.First;) - is there any equivalent in PHP ibase?

I could of course re-query the database, but this seems a waste of resources if the original relation is still accessible.

Example code:

$table = ibase_query($sql);
$row = ibase_fetch_object($table);

while (!empty($row))
{
    echo $row->ENTRYNO.'<br>';
    $row = ibase_fetch_object($table);
}

//The missing functionality
$table.First;     
//or maybe
$row = ibase_fetch_object($table,first);  
     //in which case of course the following line would be redundant


$row = ibase_fetch_object($table);
while (!empty($row))
{
    //process record

    $row = ibase_fetch_object($table);
}

Create ASC and DESC INDEX in in one index

2022-06-15T07:18:24Z

I want to create an index (in Delphi/Interbase) which contained a ASC and a DESC order.

Example:

CREATE INDEX AMUT_DAT ON ARTIKMUT(DATUM,ARTIKEL,KODE);

This create DATUM and ARTIKEL ascending.

When I create it as follow:

CREATE DESCENDING INDEX AMUT_DAT ON ARTIKMUT(DATUM,ARTIKEL,KODE);

then the field DATUM is ASCENDING, but also the ARTIKEL. I want DATUM descending and ARTIKEL ascending.

I tried:

CREATE INDEX AMUT_DAT ON ARTIKMUT([DATUM] DESC,[ARTIKEL] ASC,[KODE] ASC);

But that does not work.

How can I create such an index in Interbase?