Managing temporary tables

Developers Programming
#1

In a blank application I want to create a temporary table, then insert record into this temporary table.

I get the following debug list:

[I][INDENT]INDENT: CREATE TEMP TABLE “pg_temp_4”.patito (LIKE basura EXCLUDING ALL) ON COMMIT DROP
(postgres7): INSERT INTO “pg_temp_4”.patito ( basid, csiid, basclave, basnombre) VALUES ( 2, 1, 2, ‘DOS’)
Error
pg_query(): Query failed: ERROR: no existe la relaci?n ?pg_temp_4.patito? LINE 2: INSERT INTO “pg_temp_4”.patito ( ^
-1: ERROR: no existe la relaci?n ?pg_temp_4.patito? LINE 2: INSERT INTO “pg_temp_4”.patito ( ^

ADOConnection._Execute(
INSERT INTO “pg_temp_4”.patito (
basid,
csiid,
basclave,
basnombre)
VALUES (
2,
1,
2,
‘DOS’), false) % line 1085, file: adodb.inc.php
[/INDENT][/INDENT][/I]

This indicates that there is no relation “pg_temp_4”.patito but I did not get error creating the table.

This code works fine in plsql

#2

Is …

INSERT INTO pg_temp_4.patito ( basid, csiid, basclave, basnombre) VALUES ( 2, 1, 2, 'DOS')

… (without “”) working?

#3

No, exactly the same error. I had already tried before.

#4

Are these statements executed from the same context? I have a feeling that the temp table is not available somehow (or already deleted). Have you set the debug options to show which sqlstatements are actually fired upon your database?

#5

I’m working on a blank application and this command immediately after the other, show the code:

[I]$sql = "
SELECT nspname
FROM pg_namespace
WHERE oid = pg_my_temp_schema()";

sc_lookup(ds,$sql);

$tabla = “patito”;
[nTabla] = {ds[0][0]}.".".$tabla ;

$sql = "
CREATE TEMP TABLE “.[nTabla].” (LIKE basura EXCLUDING ALL) ON COMMIT DROP";

sc_exec_sql($sql);

$sql = "
INSERT INTO “.[nTabla].” (
basid,
csiid,
basclave,
basnombre)
VALUES (
2,
1,
2,
‘DOS’)";

sc_exec_sql($sql);

//sc_redir(basura_pruebas_temporales.php);

echo “OK.<br>”;
[/I]

#6

Albert is quite right. Since you are working with a blank application you don’t have your db-operations embedded in a transaction like in a from. You have to handle this by yourself.
Because of that your temporary table is dropped right after creation. To see if your statements work, remove the ‘ON COMMIT DROP’ clause.

jsb