CALL a database Function

[Brobeck_Micael]

I want to do a CALL to a function I created in a MySQL Database
In my heidi workbench for SQL I write
CALL DatabaseName.FunctionName(Param1,Param2)
How to do this from Delphi

[wlandgraf (Wagner Landgraf)]

You should just use your regular database-connection component, maybe you are using FireDAC, thus you should use TFDQuery to do so.

If you are using Aurelius, you can execute arbitrary SQL statements, it's explained in the following topics:

Using a generator for non Id fields?

Thank you for the contribution, @Weetch_Russell. Also note that there is no need to use the TFDQuery component directly, you can use Aurelius interfaces directly and thus the code is not dependent on a specific component: class function TConnectionUtils.GetNextSequenceValue(AConnection: IDBConnection; const SequenceName: string): Int64; const SQL = 'SELECT NEXT VALUE FOR %s FROM RDB$DATABASE'; var Query: IDBStatement; ResultSet: IDBResultSet; begin Query := AConnection.CreateStatement; …

IDBResultSet and stored procedures

I'm trying to get the result of a firebird stored procedure but it always returns 0.  The stored procedure gives the correct result when called directly in IBExpert.  Is there something that I am doing wrong?   var Statement: IDBStatement; ResultSet: IDBResultSet; Params : TObjectList< TDBParam >; SQLCommand : string; begin Result := 0; SQLCommand := 'SELECT UNIT_PRICE FROM CALCULATE_UNIT_PRICE(:COMMODITY_ID, :ACCOUNT_ID)'; //SQLCommand := 'execute procedure CALCULATE_UNIT_PRI…

Multiple requests to xdata server to run a regular query

You have three options. Use the connection pool normally, and execute queries using the Aurelius interfaces, this way: For UPDATE/INSERT/DELETE: uses {...}, Aurelius.Drivers.Interfaces; var Statement: IDBStatement; Statement := Manager.Connection.CreateStatement; Statement.SetSQLCommand(TheSQLStatement); Statement.Execute; for SELECT: var Statement: IDBStatement; ResultSet: IDBResultSet; Statement := Manager.Connection.CreateStatement; Statement.SetSQLCom…

[Brobeck_Micael]

I copied some code and made this
PROCEDURE TInkaUserMainForm.FixaVinster(inDate, inBana: STRING);
VAR
Statement: IDBStatement;
BEGIN
Statement := Manager.Connection.CreateStatement;
Statement.SetSQLCommand('CALL inkadb.uppdatera_18_hål(indate,inbana');
Statement.ExecuteQuery;
END;
But i dont know wher Manager comes from

[wlandgraf (Wagner Landgraf)]

XDataserver getting DB connection via XDatapool for service operation

You can get manager this way: Manager := TXDataOperationContext.Current.GetManager; Or you can simply do it this way if you don't use manager: var Statement: IDBStatement; ResultSet: IDBResultSet; Statement := TXDataOperationContext.Current..Connection.CreateStatement; Statement.SetSQLCommand(TheSQLStatement); ResultSet := Statement.ExecuteQuery;

[Brobeck_Micael]

Whitch USES do I need for TXDataOperationContext.Current

And the dubble dots .. what are they for

[Brobeck_Micael]

And also a USES for IDBStatement

[wlandgraf (Wagner Landgraf)]

You should use units XData.Server.Module and Aurelius.Drivers.Interfaces.

[Brobeck_Micael]

I get Undeclared identifier TXDataOperationContext

[Brobeck_Micael]

When I write XData. I do not see Servermodule in the list showing

[Brobeck_Micael]

Sorry I found Server.Module

[Brobeck_Micael]

And now I TXDataOperationContext is ok

[Brobeck_Micael]

Sorry
I'm to quick in making questions.
I try to test more before I make a question

Now is it so that when Writing in Delphi
XData. It show a list and on that list I find Server.Module
But when compiling it says cant find unit XData.Server.Module

Same problem with
Aurelius. It show a list where I can find Drivers.Interfaces
But when compiling it says Cant find unit Aurelius.Drivers.Interfaces

[wlandgraf (Wagner Landgraf)]

@Brobeck_Micael

Sorry I found Server.Module

I'm sorry, it was my typo. I have fixed my message, indeed it's XData.Server.Module.

@Brobeck_Micael

But when compiling it says cant find unit XData.Server.Module

Do you have a screenshot? Those are regular XData/Aurelius units, if compiler is not finding them, it means that they are not properly installed in Delphi.

[Brobeck_Micael]

Delphi see the XData/Aurelius units because there are no underlining on
Aurelius.Drivers.Interfaces, XData.Server.Module,

Compiler givs

I have also made a REST Server with the same installed XData/Aurelius units and no problem there

Something else
It says That I have TMS WEB Core version 2.7.2.2 but I know for shure that I have Downloaded registered version (1,634.56 MB) and installed it

[wlandgraf (Wagner Landgraf)]

TMS Aurelius is not supported in Web Core, and XData.Server.Module is also not available because it belongs to server-side XData, which makes no sense to be used in Web Core as well.

[Brobeck_Micael]

So how should I solve the problem to call a database function
from my web application created with Web Core

[wlandgraf (Wagner Landgraf)]

You should create a service operation in XData that performs such database call, and then call such service operation from Web Core client.

[Brobeck_Micael]

a small working code exampel for both
XData Server and for my webcore client would be nice

[wlandgraf (Wagner Landgraf)]

Besides documentation for service operations and Web Core XData client, you can check the FireDAC SQL demo located in folder <xdata>\demos\firedac-sql.

In such demo, unit CustomerServiceImpl shows how to implement several endpoints executing SQL statements using FireDAC. You can simply do the same to execute your own call to database function.