Saving values in UPPERCASE in a database

[Rune Runnestø]

Hi,

When connecting to a database from a JSP-file, I write for instance:
sql = "insert into person values(" + newNr + ", '" + forName + "', '" +
lastName + "')";

Statement stmt = null;
stmt.executeUpdate(sql);

The question is: How do I write the sql-sentence if I want to save the
variables in the database in UPPERCASE ?

Regards
Rune

 

[Malte]

Hi,

When connecting to a database from a JSP-file, I write for instance:
sql = "insert into person values(" + newNr + ", '" + forName + "', '" +
lastName + "')";

Statement stmt = null;
stmt.executeUpdate(sql);

The question is: How do I write the sql-sentence if I want to save the
variables in the database in UPPERCASE ?

Regards
Rune

In a small test window I did this:

create table strtest (test varchar2(64));


insert into strtest values (upper('test'));
insert into strtest values ('test');

commit;
select * from strtest;

Result:

TEST
test

drop table strtest;

You could also, of course, uppercase the String objects BEFORE you pass
them to the database.

 

[Malte]

BTW, connection to the database from the JSP could be constructed as a
poor design. I believe that many people would stick their database code
into a bean of sorts.

 

[Chris Uppal]

sql = "insert into person values(" + newNr + ", '" + forName + "', '" +
lastName + "')";

Unless "forName" and "lastName" come from a guaranteed safe source (i.e. /NOT/
a user typing it in, and definitely not anything on the Web), then this opens
up a potentially very serious security hole. If you don't understand what I'm
talking about then Google for "SQL injection attack".

-- chris

 

[Thomas Kellerer]

Hi,

When connecting to a database from a JSP-file, I write for instance:
sql = "insert into person values(" + newNr + ", '" + forName + "', '" +
lastName + "')";

Statement stmt = null;
stmt.executeUpdate(sql);

The question is: How do I write the sql-sentence if I want to save the
variables in the database in UPPERCASE ?

Regards
Rune

What's wrong with:

sql = "insert into person values(" + newNr + ", '" + forName.toUpperCase() + "',
'" + lastName.toUpperCase() + "')";

Thomas

 

[Rune Runnestø]

What's wrong with:

sql = "insert into person values(" + newNr + ", '" + forName.toUpperCase() + "',
'" + lastName.toUpperCase() + "')";

This code works. Thanks.
Rune

 

[shakah]

You're probably better off using a PreparedStatement and the database's
concept of upper case. It handles NULLs and allows you to avoid
worrying about single-quotes in your data (e.g. last names like
"O'Brien"):

// ...guessing on the first value's type (int?)
java.sql.PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO person VALUES(?,?,?)"
) ;
int nFld=0 ;
pstmt.setInt(++nFld, new Integer(newNr)) ;
pstmt.setString(++nFld, forName) ;
pstmt.setString(++nFld, lastName) ;
pstmt.executeUpdate() ;