I'm using D XE3, EDB 2.18 Build 3, Win 8.1.
I don't have a great grasp of temporary tables, as the following code works well in EDBMgr, but not at all in Delphi. In fact, it doesn't give any errors in Delphi, but it also doesn't insert the record.

I have a couple of procedures and a table,
CREATE TABLE "XList"
(
"XListID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1),
"ClaimNum" VARCHAR(15) COLLATE "UNI",
"ClientID" INTEGER,
"XListD" DATE,
"IsActive" BOOLEAN,
"XDescr" VARCHAR(80) COLLATE "UNI",
CONSTRAINT "PrimaryKey" PRIMARY KEY ("XListID")
)
VERSION 0.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768!

CREATE PROCEDURE "SPCreateMTTmpXList" (IN "TableN" VARCHAR)
BEGIN
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name = ''' + TableN + '''';
OPEN InfoCursor;
IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DELETE FROM "' + TableN + '"';
ELSE
 BEGIN
  EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "' + TableN + '" (LIKE "XList")';
 END;
END IF;
END
VERSION 1.00!

CREATE PROCEDURE "SPInsTmpXList" (IN "TableN" VARCHAR,
IN "ClaimNum" VARCHAR, IN "ClientID" INTEGER, IN "XListD" DATE,
IN "IsActive" BOOLEAN, IN "XDescr" VARCHAR)
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO "' + TableN + '" (ClaimNum, ClientID, XListD,
IsActive, XDescr) VALUES (?,?,?,?,?)'
USING ClaimNum, ClientID, XListD, IsActive, XDescr;
END
VERSION 1.00!

These all work a treat when I run them from EDBMgr. However, if I call them from my app, using...
function RunSPInsTmpXList(const STableN, SClaim, STxt: string; CreateD: TDate; IsAct: boolean; IClient: integer): boolean;
var aProc: TEDBStoredProc;
begin
Result := False;
aProc  := TEDBStoredProc.Create(nil);
try
 aProc.DatabaseName   := DM1.EDBDatabase1.DatabaseName;
 aProc.SessionName    := DM1.EDBDatabase1.SessionName;
 aProc.StoredProcName := 'SPInsTmpXList';
 aProc.Prepare;  //STableN, ClaimNum, ClientID, XListD, IsActive, XDescr
 aProc.Params[0].AsString  := STableN;
 if SClaim >'' then
  aProc.Params[1].AsString := SClaim;
 aProc.Params[2].AsInteger := IClient;
 aProc.Params[3].AsDate    := CreateD;
 aProc.Params[4].AsBoolean := IsAct;
 aProc.Params[5].AsString  := STxt;
 try
  aProc.ExecProc;
 except
  raise;
  Exit;
 end;
 Result := True;
finally
 aProc.Free;
end;
end;
...nothing. No record, no error.

Can you see where I have gone wrong? I can easily add many records to the TmpXList table in the EDBMgr, so it isn't because the TmpXList.XListID field is NULL.

Regards and TIA, Peter

Peter

If it works in EDBManager then it should be OK so I suspect either the data or using the parameter index (shouldn't be that but I've only ever used it when there's only one parameter).

So easy suggestion 1: try using ParamByName

Secondly this

 if SClaim >'' then
  aProc.Params[1].AsString := SClaim;

If SClaim is '' then it will mean that a null is supplied for that parameter. It doesn't look as though it would have an impact but you never know.

Looking at your code you should have an error reported if there is one but try taking out the try..except and see if one is being hidden.

If no one comes up with a solution can you post a bit of data and I'll set up here to try it.

Roy Lambert

The fact it isn't doing anything would make me ask it for what it
thinks it was doing. I think the Plan will do that?

xQuery.RequestPlan := True;
// execute
szResult := xQuery.Plan.Text;

No idea how that works with StoredProcedures though, but there must be
something similar?

--

Matthew Jones

Matthew

>The fact it isn't doing anything would make me ask it for what it
>thinks it was doing. I think the Plan will do that?
>
>xQuery.RequestPlan := True;
>// execute
>szResult := xQuery.Plan.Text;
>
>No idea how that works with StoredProcedures though, but there must be
>something similar?

I thought that so I went to look how you request a plan with an SP and I couldn't see anything, then I realised it wouldn't work anyway since there's an EXECUTE IMMEDIATE which means it wanders off by itself anyway.

However, it does make me wonder what happens if you just run it as a parameterised query. Peter can you try that please, and if it doesn't work then you can ask for a plan.

Roy Lambert

I changed the insert method to use ParamByName, but it didn't help. Checking the parameterised Query now.

 aProc.DatabaseName   := DMod1.EDBDatabase1.DatabaseName;
 aProc.SessionName    := DMod1.EDBDatabase1.SessionName;
 aProc.StoredProcName := 'SPInsTmpXList';
 aProc.Prepare;  //STableN  ClaimNum, ClientID, XListD, IsActive, XDescr
 aProc.ParamByName('TableN').AsString  := STableN;
//  if SClaim >'' then
  aProc.ParamByName('ClaimNum').AsString := SClaim;
 aProc.ParamByName('ClientID').AsInteger := IClient;
 aProc.ParamByName('XListD').AsDate    := CreateD;
 aProc.ParamByName('IsActive').AsBoolean := IsAct;
 aProc.ParamByName('XDescr').AsString  := STxt;
 try
  aProc.ExecProc;

The XList table is presently empty. This INSERTED data is written to a temp table in case the user chickens out before an invoice is saved.

Regards, Peter

Ok, I wrote this poste haste, and it didn't help.
function RunSPInsTmpXList(const STableN, SClaim, STxt: string; CreateD: TDate; IsAct: boolean; IClient: integer): boolean;
const
INS_XLIST = 'INSERT INTO "%s" (ClaimNum, ClientID, XListD, IsActive, XDescr)'+
            ' VALUES (:ClaimNum, :ClientID, :XListD, :IsActive, :XDescr)';
var aQry: TEDBQuery;
begin
Result := False;
aQry  := TEDBQuery.Create(nil);
try
 aQry.DatabaseName   := DMod1.EDBDatabase1.DatabaseName;
 aQry.SessionName    := DMod1.EDBDatabase1.SessionName;
 aQry.SQL.Text       := Format(INS_XLIST, ['TmpXList']);
 aQry.Prepare;
 aQry.ParamByName('ClaimNum').AsString := SClaim;
 aQry.ParamByName('ClientID').AsInteger := IClient;
 aQry.ParamByName('XListD').AsDate    := CreateD;
 aQry.ParamByName('IsActive').AsBoolean := IsAct;
 aQry.ParamByName('XDescr').AsString  := STxt;
 try
  aQry.ExecSQL;
 except
  raise;
  Exit;
 end;
 Result := True;
finally
 aQry.Free;
end;
end;

I called it so:
RunSPCreateTmpXList('TmpXList', False);
RunSPInsTmpXList('TmpXList', '454@De\ha', 'Unsigned letter', Date, True, 3);
... but EDBMgr says that the table does not exist in the default schema. (Clue?)
The RunSPCreateTmpXList method appears to work, as the insert query doesn't whine about the missing table.

In EDBMgr I cycle through each of these lines in turn, and it works a treat.
--CREATE TEMPORARY TABLE "TmpXList" (LIKE "XList")
--INSERT INTO "TmpXList" (ClaimNum, ClientID, XListD, IsActive, XDescr) VALUES ('abc[676]', 712, DATE '2005-01-01', True, 'Leather bound email')
select * from TmpXList
If I run the insert line a number of times, that many lines appear when I run the 'Select' statement.

Very baffling.

Regards, Peter.

The problem isn't in the fact that the table isn't being created or populated, it Is in that I can't see that temp table in EDBMgr unless I create it in EDBMgr. Following your suggestion, I used the code in the OLH to RequestPlan, and found that indeed there was a table, and the INSERT was a success: 1 rows affected in .0031 secs.

I can even programmatically count the rows, so it clearly *isn't failing* to create the table at all. There is something different about this temp table that doesn't allow it to be visible outside the process that made it. I suppose that is Ok too - it suits my purposes exactly.

Oh well, I'll go back to inserting the rows from the temp table into XList. Thanks for your input chaps.

Regards, Peter.

Peter


Brain suddenly went into gear. TEMPORARY tables are session specific. From the manual (now I know what I'm looking for:

<<Use the TEMPORARY clause to specify that the table should be
created as a local temporary table that is only visible to the current session.>>

On that basis SPCreateMTTmpXList must be run in a different session from SPInsTmpXList

When you run them in EDBManager you'll be calling them both from the same session.

Roy Lambert

Roy, Mathew

<<Use the TEMPORARY clause to specify that the table should be
created as a local temporary table that is only visible to the current session.>>

Of course, thanks for pointing that out. I read the OLH but I failed to grasp the relevance of the statement - I was convinced I had a different problem.

Thanks again

Regards, Peter

Peter


I only remembered because I was going to suggest using a memory table and then remembered about how they could now be accessed by every session which made me remember that temporary tables couldn't.

Roy Lambert