[ajug-members] prepared statements: returning excessive rows

[Saltysiak, Byron]

Are you sure that this returns only one row?
----------------------------
Select Distinct obj_id, 'D',1, ?, 'N', 1 ");
            sbr.append(" from dm_ecrs_jobs ");
            sbr.append(" Minus Select distinct obj_id, 'D', 1, ?, 'N', 1 ");
            sbr.append(" from dm_idx_ecrs_impact_dist where IPT = ?)");
----------------------------

Also - I would suggest using a NOT EXISTS instead of a minus if you can.

- Byron

-----Original Message-----
From: Tom Boyce [mailto:tom.boyce at wellfound.com]
Sent: Thursday, June 10, 2004 11:52 AM
To: ajug-members at ajug.org
Subject: [ajug-members] prepared statements: returning excessive rows


Hi all,

I'm trying to do a prepared statement that in testing should insert one row
into a table, but instead, inserts 1594!!

The statement has one variable entered 3 times (ipt name).  I am using a
custom class, getSetDataFromDB, that takes a variety of inputs and makes the
connection to the db.  For prepared statements it takes an object array for
the variables.  The resultset object (System.out.println("getNames size is:
"+gsdfdb_getNames.getRowLength());) returns 1.  I loop through the resultset
and put the values into the object array o 3 times - one for each position
needed in the statement.  Here is the code:

public void addToImpact()
    {
        System.out.println("addToImpact");
        //need to get the taskname variable
        getSetDataFromDB gsdfdb_getNames = new getSetDataFromDB(ppo, "Select
ipt_name, task_name from tmp_ecrs_ipt_lov WHERE operation = 'Add'");
        System.out.println("getNames size is:
"+gsdfdb_getNames.getRowLength());
        for(int i=0; i<gsdfdb_getNames.getRowLength();i++)
        {
            Object o[] = new Object[3];
            o[0] = gsdfdb_getNames.getValueAt(i, 0);
            o[1] = gsdfdb_getNames.getValueAt(i, 0);
            o[2] = gsdfdb_getNames.getValueAt(i, 0);
            StringBuffer sbr = new StringBuffer();
            sbr.append("Insert into dm_idx_ecrs_impact_dist ");
            sbr.append("(Select Distinct obj_id, 'D',1, ?, 'N', 1 ");
            sbr.append(" from dm_ecrs_jobs ");
            sbr.append(" Minus Select distinct obj_id, 'D', 1, ?, 'N', 1 ");
            sbr.append(" from dm_idx_ecrs_impact_dist where IPT = ?)");
            //System.out.println(sbr.toString());
            getSetDataFromDB gsdfdb_ati = new getSetDataFromDB(ppo,
sbr.toString(), o, "PS");
        }
    }

Any help would be appreciated!!!

Tom Boyce
Junior Software Engineer
WellFound Technologies, Inc
770.424.4645 ext 105

*****NOTICE*****
This electronic mail transmission may contain confidential information and
is intended only for the person(s) named.  Any use, copying, or disclosure
by any other person is strictly prohibited.  If you have received this
transmission in error, please notify the sender immediately via e-mail, and
delete it from your computer.  Although this e-mail and any attachments are
believed to be free of any virus or other defect that might negatively
affect any computer system into which it is received and opened, it is the
responsibility of the recipient to ensure that it is virus free and no
responsibility is accepted by WellFound Technology, Inc. or the sender for
any loss or damage arising in any way in the event that such a virus or
defect exist.
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.687 / Virus Database: 448 - Release Date: 5/16/2004
 


_______________________________________________
ajug-members mailing list
ajug-members at ajug.org
http://www.ajug.org/mailman/listinfo/ajug-members
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.ajug.org/pipermail/ajug-members/attachments/20040610/7594e5d4/attachment.html