Christian Wende wrote:
>
> Hello,
> I am using
> Server :
> SAPDB 7.3.0.29
> on a WIN 2000 Professional
> Client :
> Win ME
> Python (ActivePython) 2.2.2
> SAPDB-API Python 7.4.3.12a
>
> My Problem:
> I try to use SapDB_Result.getLastSerial() from an INSERT-Statement.
> When I create the table and insert a row I get a SapDB_Result.
> When I create a trigger on insert for this table and then
> insert a row I
> get a 1.
> The trigger updates a field in the inserted row.
>
> My questions:
> Is this a bug?
Unfortunately: yes.
Will be fixed soon. Thank you for reporting.
> Is there another way to generate the value for the field
> COUNTER by the
> database?
Ok, perhaps you read my mail according to GROUP-BY and HAVING-usage
written in the meantime.
Use HAVING if you want to check the result of the group.
But if you only want to have the result of one group or have restrictions
on single columns : use the WHERE-clause.
In your test-scenario it will not make big difference if ALL rows in
your table are checked, for ALL different names in your table a row
is prepared in which the number of occurances is counted.
But for German Telekom with around (let's say) 40 000 000 telefon-numbers
with (let's say) 650 000 different names it is hard work for the database
if you justed wanted to know how many people are named 'Wende', for example.
That means, in your example the other 649 999 group-rows are prepared for show
(and for bad performance and for storage overflow).
In your case instead of INSERT + trigger
insert into "TBL_TEST" (NAME, COUNTER) VALUES
(:name,
(select count(name) + 1 from "TBL_TEST" where name = :name))
would help (if you have one of the newer versions of SAP DB and not 7.3.0.15 or so)
Elke
SAP Labs Berlin
> Any help is appreciated.
> Christian Wende
>
>
> Here is my source which I used for testing.
>
> import sys
> import sapdb
> import time
>
> user = 'TEST'
> pwd = 'TEST'
> dbname = 'tst'
> host = '192.168.70.74'
>
>
> # connect to db
> session = sapdb.connect (user, pwd, dbname, host, 'yes')
>
> try:
> stmt = 'DROP TABLE "TBL_TEST"'
> result = session.sql(stmt)
> except:
> pass
>
> stmt = 'CREATE TABLE "TBL_TEST"'
> stmt = stmt + '('
> stmt = stmt + ' ID Fixed (10,0) DEFAULT SERIAL (1),'
> stmt = stmt + ' NAME Char (30) ASCII,'
> stmt = stmt + ' COUNTER smallint default 0,'
> stmt = stmt + ' PRIMARY KEY ("ID"),'
> stmt = stmt + ' UNIQUE (NAME, COUNTER) '
> stmt = stmt + ')'
> result = session.sql(stmt)
>
> # insert with COUNTER set by program
> stmt = 'INSERT INTO "TBL_TEST" (NAME, COUNTER) VALUES (\'Name\', 1)'
> result = session.sql(stmt)
>
> # insert with COUNTER set by program
> stmt = 'INSERT INTO "TBL_TEST" (NAME, COUNTER) VALUES (\'Name\', 2)'
> result = session.sql(stmt)
> print 'result without trigger'
> #print result.getLastSerial()
> print result
>
> # create trigger that generates a the next value for Zaehler
> stmt = 'CREATE TRIGGER trig_tbl_Test '
> stmt = stmt + 'FOR "TBL_TEST" '
> stmt = stmt + 'AFTER INSERT '
> stmt = stmt + 'EXECUTE '
> stmt = stmt + '('
> stmt = stmt + ' VAR iCounter INT;'
> stmt = stmt + ' '
> stmt = stmt + ' SELECT COUNT(Name) '
> stmt = stmt + ' INTO :iCounter '
> stmt = stmt + ' FROM TEST."TBL_TEST" '
> stmt = stmt + ' GROUP BY Name '
> stmt = stmt + ' HAVING :NEW.NAME = NAME;'
> stmt = stmt + ' '
> stmt = stmt + ' UPDATE TEST."TBL_TEST" '
> stmt = stmt + ' SET COUNTER = :iCounter '
> stmt = stmt + ' WHERE :NEW.NAME = NAME '
> stmt = stmt + ' AND :NEW.COUNTER = COUNTER;'
> stmt = stmt + ' '
> stmt = stmt + ')'
>
> result = session.sql(stmt)
>
> # insert with COUNTER set by database (trigger)
> stmt = 'INSERT INTO "TBL_TEST" (NAME) VALUES (\'Name\')'
> result = session.sql(stmt)
> print 'result with trigger'
> #print result.getLastSerial()
> print result
>
> session.release ()
> print '\nSession released\n\n'
>
>
>
>
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
