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, Jun 15 '12 at 02: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, Jun 15 '12 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, Jun 15 '12 at 17:07

score 4 · Answer 1 · answered Jun 15 '12 at 13:53

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;

+1 for providing a "proper" way of calling stored procedures from code. IMO, OP used a bit uncommon way to do it. — LightBulb, Jun 15 '12 at 18:33

score 1 · Answer 2 · edited May 23 '17 at 11:55

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 '17 at 11:55

Community

1
1

answered Jun 15 '12 at 20:25

moskito-x

11,832
5
47
60

score -1 · Answer 3 · answered Jun 15 '12 at 03:33

-1

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

answered Jun 15 '12 at 03:33

rstrelba

1,838
15
16

I inserted suspend but result is same – masm Jun 15 '12 at 07:43
SUSPEND need for returning values from stored procrdures, so why you down my carma? – rstrelba Jun 15 '12 at 10:02
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. – Ken White Jun 15 '12 at 22:09
`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). – Mark Rotteveel Jun 16 '12 at 08:00

Firebird exception in Delphi WideStringType OutParameter

3 Answers3