--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:

> If your XxxxSQL statement really is
>
> EXECUTE PROCEDURE SSP(:ESP_BLOB_FIELD)
>
> then this is the cause of the "internal error". You cannot EXECUTE

a

> selectable stored procedure, you can only SELECT from it.
>

Yes your right, i know. But that was a mistake the code is ok, i just
switched the 'E' with the 'S' my actual code is
"EXECUTE PROCEDURE ESP(:SSP_BLOB_FIELD)"

> You must write one or more SEPARATE procedures for the XxxxSQL
> operations, that include the Key values for the targeted records in
> their inputs. A blob can never be a key.
>
> It *is* possible to pass a blob as a parameter to an executable SP
> but the assignment for blobs is quite different to that for regular
> data fields. The data access object has to *know* the connection
> between the field in the SSP and the parameter in the ESP.
>
> Is the NAME of the parameter actually the same as the name of the
> blob field in the SSP? If not, then change it so that it is the
> same, e.g., if the blob field in the SSP is SSP_BLOB_FIELD then the
> XxxxSQL statement should be
>
> EXECUTE PROCEDURE ESP(:aKeyField, ......, :SSP_BLOB_FIELD)
>
> At this point I feel the need to remark that you're not providing
> enough information about these SPs.
>
> Helen
>

I think i solve the problem. The blob parameter being passed did not
have the exact name as the SSP. Thak you very mutch for the hint.

About passing parameter from the ESP, i forgot about that and now i
throw exceptions on the ESP and catch them on delphi.

Is this the correct approach to detect any errors on input data?
Bellow are my stored procedures as they are on the database, and the
XxxxSQL property.

SQL Property code:
"select * from KM_List_Players(:IN_PLAYERID)"

ESP Code:
CREATE PROCEDURE KM_LIST_PLAYERS(
IN_PLAYERID INTEGER)
RETURNS (
ID INTEGER,
LOGIN VARCHAR(32),
PASS VARCHAR(128),
TOKEN VARCHAR(32),
SESSION VARCHAR(32),
IP VARCHAR(16),
EMAIL VARCHAR(128),
DSC VARCHAR(128),
LOCATION_ID INTEGER,
LOCATION VARCHAR(128),
LANGUAGE_ID INTEGER,
LANGUAGE VARCHAR(128),
CIV_ID INTEGER,
CIV VARCHAR(128),
MSN VARCHAR(128),
GMAIL VARCHAR(128),
OCCUPATION VARCHAR(128),
INTERESTS BLOB SUB_TYPE 1 SEGMENT SIZE 256,
JOIN_DATE DATE)
AS
BEGIN
FOR
SELECT
P.id, p.login, p.pass, p.token, p.session, p.ip, p.email, p.dsc,
p.location_id, Lo.dsc, p.language_id, La.dsc, P.civ_id, C.dsc,
p.msn,
p.gmail, p.occupation, p.interests, p.join_date
FROM
players P
INNER JOIN locations Lo ON
Lo.id = P.location_id
INNER JOIN languages La ON
La.id = P.language_id
INNER JOIN civilizations C ON
C.id = P.civ_id
WHERE
((:IN_PlayerID IS NULL) or (P.id = :IN_PlayerID))
INTO
:id, :login, :pass, :token, :session, :ip, :email, :dsc, :locati
on_id,
:location, :language_id, :language, :civ_id, :civ, :msn, :gmail,
:occupation, :interests, :join_date
DO
BEGIN
SUSPEND;
END
END



EditSQL Property code:
"EXECUTE PROCEDURE KM_UPDATE_PLAYER
(:ID, :LOGIN, :PASS, :TOKEN, :SESSION, :IP, :EMAIL, :DSC, :LOCATION_ID
, :LANGUAGE_ID, :CIV_ID, :MSN, :GMAIL, :OCCUPATION, :INTERESTS, :JOIN_
DATE)
"

SSP Code:
CREATE PROCEDURE KM_UPDATE_PLAYER(
IN_PLAYER_ID INTEGER,
IN_PLAYER_LOGIN VARCHAR(32),
IN_PLAYER_PASS VARCHAR(128),
IN_PLAYER_TOKEN VARCHAR(32),
IN_PLAYER_SESSION VARCHAR(32),
IN_PLAYER_IP VARCHAR(16),
IN_PLAYER_EMAIL VARCHAR(128),
IN_PLAYER_DSC VARCHAR(128),
IN_PLAYER_LOCATION_ID INTEGER,
IN_PLAYER_LANGUAGE_ID INTEGER,
IN_PLAYER_CIV_ID INTEGER,
IN_PLAYER_MSN VARCHAR(128),
IN_PLAYER_GMAIL VARCHAR(128),
IN_PLAYER_OCCUPATION VARCHAR(128),
IN_PLAYER_INTERESTS BLOB SUB_TYPE 1 SEGMENT SIZE 256,
IN_PLAYER_JOIN_DATE DATE)
AS
BEGIN
-- Check if player exists
IF (NOT EXISTS(SELECT * FROM players WHERE id = :IN_PLAYER_ID)) THEN
EXCEPTION exc_player_not_exists;

-- First the data is validated!
IF (EXISTS(SELECT login FROM players WHERE login = :IN_PLAYER_LOGIN
AND id <> :IN_PLAYER_ID)) THEN
EXCEPTION exc_player_exists;

-- check location foreign key
IF (NOT EXISTS(SELECT id FROM locations WHERE id
= :IN_PLAYER_LOCATION_ID)) THEN
EXCEPTION exc_location_not_exists;

-- check language foreign key
IF (NOT EXISTS(SELECT id FROM languages WHERE id
= :IN_PLAYER_LANGUAGE_ID)) THEN
EXCEPTION exc_language_not_exists;

-- check civ foreign key
IF (NOT EXISTS(SELECT id FROM civilizations WHERE id
= :IN_PLAYER_CIV_ID)) THEN
EXCEPTION exc_civilization_not_exists;

-- update table!
UPDATE
players
SET
login = :IN_PLAYER_LOGIN,
pass = :IN_PLAYER_PASS,
token = :IN_PLAYER_TOKEN,
session = :IN_PLAYER_SESSION,
ip = :IN_PLAYER_IP,
email = :IN_PLAYER_EMAIL,
dsc = :IN_PLAYER_DSC,
location_id = :IN_PLAYER_LOCATION_ID,
language_id = :IN_PLAYER_LANGUAGE_ID,
civ_id = :IN_PLAYER_CIV_ID,
msn = :IN_PLAYER_MSN,
gmail = :IN_PLAYER_GMAIL,
occupation = :IN_PLAYER_OCCUPATION,
interests = :IN_PLAYER_INTERESTS,
join_date = :IN_PLAYER_JOIN_DATE
WHERE
id = :IN_PLAYER_ID;
END



EditSQL Property code:
"EXECUTE PROCEDURE KM_DELETE_PLAYER(:ID)"

SSP Code:
CREATE PROCEDURE KM_DELETE_PLAYER(
IN_PLAYER_ID INTEGER)
AS
BEGIN
/* Determine if the user is an administrator! */
IF (EXISTS(SELECT * FROM players WHERE ID = :IN_PLAYER_ID AND
ADMINISTRATOR = 1)) THEN
EXCEPTION exc_not_delete_admin;

/* Delete data from table player_colonies */
DELETE FROM player_colonies WHERE PLAYER_ID = :IN_PLAYER_ID;
/* Delete data from table player_evolutions */
DELETE FROM player_evolutions WHERE PLAYER_ID = :IN_PLAYER_ID;
/* Delete data from table players */
DELETE FROM players WHERE ID = :IN_PLAYER_ID;
END


(Sorry for the very, very long post!)
Luis Semedo Duarte