also aufgrund deiner .de-mailadresse denke ich mal, dass du deutsch sprichst.
da mein englisch nicht immer vom besten ist, hier nochmal auf deutsch:
du solltest einen kurzen aber pregnanten test-fall an die mailingliste schicken, den man mal so eben in SQL-Studio ausfuehren koennte.
also von der Form
CREATE TABLE ...
//
INSERT ...
//
INSERT ...
//
...
//
SELECT ...
das mit Perl und ODBC iss zwar ne prima sache, aber die Statements sollten auch so wie Perl sie an den ODBC treiber schickt auch im SQL Studio klappen.
SAPDB mochte uebrigens zumindest bei mir keine Zahlen in zusammenhang mit boolean spalten.
Toni Epple wrote:
Hi Sven and Elke, thank you for your answers! Sven K�hler wrote:Please post more on how to reproduce the bug. I even doubt that such a bug exists.
Well, I find it unbelievable myself, but it exists, and it's reproducable!
First , I would like to answer Elkes questions regarding the table, then I'm giving you a detailed View of what I was doing to produce the error:
Elke Zabach wrote:
> I would like to see the data stored in the primary key (without where-clause)
- select cid , assay from SPHERES where cid ='1'
result:
CID ASSAY
1 FALSE
1 FALSE
1 TRUE
>and some info if we are talking about a table or a view(ok, table), if there are
indices around (...) and if "USER"."SPHERES" and SPHERES are really the same table, i.e. the user working is the user named "USER". - It is a table, there is no Index on this table, and only the owner ("USER")of the table has inserted,updated and selected data.
> and how the explain-output looks like for the two different
selects (=false/ !=true)
- explain select * from SPHERES where CID='1' and ASSAY != true
OWNER TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT O D T M USER SPHERES RANGE CONDITION FOR KEY COLUMN 83 CID (USED KEY COLUMN) USER RESULT IS NOT COPIED , COSTVALUE IS 1
-explain select * from SPHERES where CID='1' and ASSAY = false
OWNER TABLENAME COLUMN_OR_INDEX STRATEGY PAGECOUNT O D T M USER SPHERES EQUAL CONDITION FOR KEY COLUMN 83 CID (USED KEY COLUMN) ASSAY (USED KEY COLUMN) USER RESULT IS COPIED , COSTVALUE IS 1
Some Additional Info you may need to reproduce this error:
I'm running sapdb 7.3.0.20 , Linux.
As I wrote in my first mail, I'm trying to insert/update rows of a table via PERL ODBC.
The initial values that reside in the table where copied (by the owner of the table) via PERL ODBC from another table.
8<-------------------------------------->8
$stmt = "SELECT CID, SPHERE2,SPHERE3, ASSAY FROM SPHERES_OLD WHERE CID=?";
$sth = $dbh->prepare($stmt);
my $stmt_up = "INSERT INTO SPHERES (CID, SPHERE2,SPHERE3, ASSAY) VALUES (?,?,?,?)";
$sth_up = $dbh_up->prepare($stmt_up);
foreach my $locusid ( sort by_num keys %cids ) {
$sth->execute("$cid");
while ( my ( $db_cid, $SPHERE2, $SPHERE3, $ASSAY ) = $sth->fetchrow_array() ) {
$sth_up->execute("$db_cid", "$SPHERE2", "$SPHERE3", "$ASSAY" );
}
8<-------------------------------------->8
That's what I do to insert the duplicates:
First I'm checking for presence of the row. If it's already there I'm updating one column, if not I'm inserting a new row:
8<-------------------------------------->8
$sth_up = $dbh_up->prepare("SELECT CID FROM SPHERES WHERE CID=? AND ASSAY = $is_assay");
$sth_up->execute($CID_item);
if ( $sth_up->fetchrow_array ) {
$sth_up = $dbh_up->prepare("UPDATE SPHERES SET SPHERE1=? WHERE CID=? AND ASSAY = $is_assay");
$sth_up->execute($upload, $CID_item );
}
else {
$sth_up = $dbh_up->prepare("INSERT INTO SPHERES(CID, SPHERE1, ASSAY) VALUES (?,?, $is_assay)");
$sth_up->execute($CID_item, $upload);
}
8<-------------------------------------->8
When I perform the first statement via SQL-Studio like this:
SELECT CID FROM SPHERES WHERE CID=1 AND ASSAY = false
it returns one row. But when I execute the statement via Perl-ODBC ( $is_assay = "false"; $CID_item = "1"):
$sth_up = $dbh_up->prepare("SELECT CID FROM SPHERES WHERE CID=? AND ASSAY = $is_assay");
$sth_up->execute($CID_item);
it returns no row, and the following statement succeeds:
$sth_up = $dbh_up->prepare("INSERT INTO SPHERES(CID, SPHERE1, ASSAY) VALUES (?,?, $is_assay)");
$sth_up->execute($CID_item, $upload);
Result: The table now contains three rows with a CID of "1", which should be impossible.
When I select these rows via SQL-Statement (SQL-STUDIO), they are shown:
select * from SPHERES where CID = '1'
=> 3 rows! CID ASSAY SPHERE1 SPHERE2 SPHERE3
1 FALSE <long> ? ?
1 FALSE ? <long> <long>
I do not understand how this can happen. Maybe it's got to do with the internal representation of boolean values (1 and 0) and the external representation (true and false)?
thanks for your help
Toni
-- Anton Epple Genomatix Software GmbH Landsberger Strasse 6 D-80339 Muenchen http://www.genomatix.de
_______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
