Обсуждение: batch example

Can someone point me to an example using a batch statement for Postgresql?

thank you!

Troy Campano

unsubscribe

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

unsubscribe

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Hello Troy,

> Can someone point me to an example using a batch statement for
> Postgresql?
>
Maybe this helps... Don't be confused about the creation of the
SQL-Statements, the point is "sth.addBatch" and "sth.executeBatch".

    public void saveChanges() throws SQLException {
      if (! dataChanged) return;

      Statement sth = PDMObject.this.con.createStatement();
      for (int i = 0; i < attributeCount; i++) {
        try {
          if (attributes[i].isNew()) {
            sth.addBatch("INSERT INTO PDM_Spaltennamen (name, typid,
text_de, text_en, text_fr, pos, menueIndex) VALUES (" +
            "'" + attributes[i].getName() + "', " +
            "(SELECT id FROM PDM_Typen WHERE (objektTyp = '" +
relationName + "')), " +
            ((attributes[i].getText("de") != null) ? ("'" +
attributes[i].getText("de") + "'") : "NULL") + ", " +
            ((attributes[i].getText("en") != null) ? ("'" +
attributes[i].getText("en") + "'") : "NULL") + ", " +
            ((attributes[i].getText("fr") != null) ? ("'" +
attributes[i].getText("fr") + "'") : "NULL") + ", " +
            + attributes[i].getPosition() + ", " +
            0 + ")"
            );
          } else {
            sth.addBatch("UPDATE PDM_Spaltennamen SET " +
                         ((attributes[i].getText("de") != null) ?
("text_de = '" + attributes[i].getText("de") + "'") : "text_de = NULL")
+ ", " +
                         ((attributes[i].getText("en") != null) ?
("text_en = '" + attributes[i].getText("en") + "'") : "text_en = NULL")
+ ", " +
                         ((attributes[i].getText("fr") != null) ?
("text_fr = '" + attributes[i].getText("fr") + "'") : "text_fr = NULL")
+ ", " +
                         "pos = " + attributes[i].getPosition() + ", " +
                         "menueIndex = " + 0 +
                         " WHERE (name = '" + attributes[i].getName() +
"') AND " +
                         "(typid = (SELECT id FROM PDM_Typen WHERE
(objekttyp = '" + relationName + "')))"
                         );
          }
        } catch (WrongArgumentException e) { // sollte nicht passieren,
sonst Programmierfehler
          e.printStackTrace();
        }
      }
      sth.executeBatch();
      sth.close();
      dataChanged = false;
    }


Greetings

Thorsten

I have notices that when you want to use the upper() function, you must also
use the trim().

For example,
In a situation where a column names lastname is char(40):
SELECT * FROM users WHERE lastname = 'Pringle'   returns one row.

SELECT * FROM users WHERE UPPER(lastname) = UPPER('Pringle')   will return
zero rows.

SELECT * FROM users WHERE TRIM(UPPER(lastname) = TRIM(UPPER('Pringle'))
will return one row.

This seems odd to me.  Can someone explain?

What does select * from ... where trim(upper(lastname)) ==
upper('Pringle') return?

Dave
On Fri, 2003-01-10 at 18:45, Hale Pringle wrote:
> I have notices that when you want to use the upper() function, you must also
> use the trim().
>
> For example,
> In a situation where a column names lastname is char(40):
> SELECT * FROM users WHERE lastname = 'Pringle'   returns one row.
>
> SELECT * FROM users WHERE UPPER(lastname) = UPPER('Pringle')   will return
> zero rows.
>
> SELECT * FROM users WHERE TRIM(UPPER(lastname) = TRIM(UPPER('Pringle'))
> will return one row.
>
> This seems odd to me.  Can someone explain?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Dave Cramer <Dave@micro-automation.net>

"Hale Pringle" <halepringle@yahoo.com> writes:
> In a situation where a column names lastname is char(40):
> SELECT * FROM users WHERE lastname = 'Pringle'   returns one row.
> SELECT * FROM users WHERE UPPER(lastname) = UPPER('Pringle')   will return
> zero rows.
> SELECT * FROM users WHERE TRIM(UPPER(lastname) = TRIM(UPPER('Pringle'))
> will return one row.

lastname is actually 'Pringle                                 ' because
of char(N)'s implicit space-padding.

When you compare it to an untyped literal 'Pringle', the comparison
is assumed to be done under the rules of the char(N) datatype, in
which trailing spaces are not significant --- so you get TRUE.

When you feed lastname to UPPER(), the result is of type TEXT, because
we only have one form of UPPER() and it takes and returns TEXT.  And
trailing spaces *are* significant according to TEXT's equality operator.
So your second case reduces to
  'PRINGLE                                 '::text = 'PRINGLE'::text
which is false.

There has been some talk of altering the coercion rules so that
CHAR(n)-to-TEXT coercion is not direct but implicitly invokes rtrim()
to get rid of the trailing spaces.  This would cause your example to
be effectively
  SELECT * FROM users WHERE UPPER(RTRIM(lastname)) = UPPER('Pringle')
which would avoid most of the surprise factor.  There are probably still
some unpleasant corner cases in this approach, though.

In the meantime: my recommendation is to avoid char(N) like the plague,
except in cases where the data naturally has a fixed width (US state
postal abbreviations, for example, are inherently char(2)).  You should
be using varchar(40) or text for lastname.  char(40) loses on
efficiency, storage space, and every other dimension as well as this
one.

            regards, tom lane

PS: this is not a JDBC issue, but a backend issue.  If you want to
discuss it further, please respect the followup-to: pgsql-sql.

On Fri, Jan 10, 2003 at 06:45:43PM -0500, Hale Pringle wrote:
> I have notices that when you want to use the upper() function, you must also
> use the trim().

No.  But. . .

> For example,
> In a situation where a column names lastname is char(40):
> SELECT * FROM users WHERE lastname = 'Pringle'   returns one row.
>
> SELECT * FROM users WHERE UPPER(lastname) = UPPER('Pringle')   will return
> zero rows.
>
> SELECT * FROM users WHERE TRIM(UPPER(lastname) = TRIM(UPPER('Pringle'))
> will return one row.
>
> This seems odd to me.  Can someone explain?

. . .yes.  The problem has to so with the char() data type, which is
padded per spec.  So if you do anything on it, you need to trim() it.
I believe there is a special case in place in the spec just for the
SELECTs, but someone more familiar with the specification can help.

If you don't really need exactly 40 characters, use varchar() or text().

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110