Firebird exception in Delphi WideStringType OutParameter

Question

I have sample firebird stored procedure

PROCEDURE PROCEDURE01
RETURNS (
 PARAMETER01 VARCHAR(50))
AS 
BEGIN
  PARAMETER01 = 'Hello';
END

and in the Delphi side

LCommand := SQLConnection1.DBXConnection.CreateCommand;
LCommand.CommandType := TDBXCommandTypes.DbxStoredProcedure;
LCommand.Text := 'PROCEDURE01';
LIdOut := LCommand.CreateParameter;
LIdOut.ParameterDirection := TDBXParameterDirections.OutParameter;
LIdOut.DataType := TDBXDataTypes.WideStringType;
LIdOut.Name := 'PARAMETER01';
LCommand.Parameters.AddParameter(LIdOut);
LCommand.Prepare;
LReader := LCommand.ExecuteQuery;

and receive exception

"Arithmetic exception, numeric overflow, or string truncation"

WideString implies multi-byte characters, and your parameter is set to VARCHAR(50), which typically is single-byte characters. Try using compatible types. (Do you REALLY TYPE ALL YOUR STORED PROCEDURES IN ALL CAPS? If so, I'm glad I don't have to work with your SQL - SHOUTING MAKES THINGS HARD TO READ. They invented the Shift key For a reason.) — Ken White
– Ken White, Commented Jun 15, 2012 at 2:18
@Ken White, it is common practice to write UPPER CASE keywords in Firebird. It's just an old habit I guess, so no need to shout. — LightBulb
– LightBulb, Commented Jun 15, 2012 at 16:56
@LightBulb: Except they're not all keywords. PROCEDURE01 is not a keyword, and neither is `PARAMETER01'. There's no requirement to write in upper-case, especially in a stored procedure where there's no possible ambiguity between the SQL and non-SQL statements. Text, whether it's an SQL statement or a plain sentence in an email or web post, is hard to read when it's typed in all caps. :-) And I didn't accuse the poster of shouting at us, but of shouting in general. :-) — Ken White
– Ken White, Commented Jun 15, 2012 at 17:07

RRUZ · Accepted Answer · 2012-06-15 13:53:57Z

4

It seems a limitation of the TDBXCommand (or maybe of the Dbexpress firebird driver), because using output parameters of another types all works fine. As workaround you can use the TSQLStoredProc class.

Try this sample.

var
 LSQLStoredProc :  TSQLStoredProc;
begin
  LSQLStoredProc:=TSQLStoredProc.Create(nil);
  try
    LSQLStoredProc.SQLConnection:=SQLConnection1;
    LSQLStoredProc.StoredProcName:='PROCEDURE01';
    LSQLStoredProc.ExecProc;
    ShowMessage(LSQLStoredProc.ParamByName('PARAMETER01').AsString);
  finally
    LSQLStoredProc.Free;
  end;
end;

answered Jun 15, 2012 at 13:53

RRUZ

137k20 gold badges365 silver badges489 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LightBulb Over a year ago

+1 for providing a "proper" way of calling stored procedures from code. IMO, OP used a bit uncommon way to do it.

Community · Accepted Answer · 2017-05-23 11:55:47Z

1

SET the database to UTF-8 as an example.

CREATE DATABASE localhost:mybase
  USER SYSDBA
  PASSWORD masterkey
  PAGE_SIZE 8192
  DEFAULT CHARACTER SET UTF8;
  SET NAMES ISO8859_1;

CREATE TABLE scales (
  ID ...,      
  byteken VARCHAR(50) COLLATE DE_DE,

look at my answer at Arithmetic exception, numeric overflow, or string truncation

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Jun 15, 2012 at 20:25

moskito-x

12k5 gold badges49 silver badges61 bronze badges

Comments

rstrelba · Accepted Answer · 2012-06-15 03:33:41Z

-1

first put "SUSPEND" before "END" in stored procedure

answered Jun 15, 2012 at 3:33

rstrelba

1,98416 silver badges16 bronze badges

4 Comments

masm Over a year ago

I inserted suspend but result is same

rstrelba Over a year ago

SUSPEND need for returning values from stored procrdures, so why you down my carma?

Ken White Over a year ago

I did not down-vote ("down your carma"), but it's probably because there's really no useful information in your answer. It either needs to contain much more detail about why this would be a solution, or it should be deleted and posted as a comment. It's not a complete answer, it's 8 words with no reason or information behind them.

Mark Rotteveel Over a year ago

SUSPEND is not necessary for procedures that only return 1 value. It would probably be wrong here, because procedures containing SUSPEND are treated differently from procedures without (one is selectable, the other is not).

Collectives™ on Stack Overflow

Firebird exception in Delphi WideStringType OutParameter

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related