Solved

How can I trim() inside the query or in the "String"?

Posted on 2006-07-18
10
316 Views
Last Modified: 2010-03-31
Experts,

I have the query below that take the user's authentication ID (login ID) and then call the database table to see if there is a match.  IF there is a match then it list it.

However, I am not getting any errors, but the login ID is not matching up to that ID in the table, and I have checked that the ID exists in the table.

I am thinking perhaps the VZID needs to be trimmed in the query.  
How can I do that?

Here is my code:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*,javax.sql.*,javax.naming.*"%>
<%@ page errorPage = "../public/errpage.jsp" %>
<%@ page import="java.io.*" %>

<%
String username1 = (String)session.getAttribute("j_username");
String username = username1.trim();
%>


<HTML>
<HEAD>
<%@ page
language="java"
contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"
%>
<META http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<META name="GENERATOR" content="IBM WebSphere Studio">
<TITLE>jusername2.jsp</TITLE>
</HEAD>
<BODY>
<%
try {
javax.sql.DataSource dss;
java.sql.Connection conn = null;
Context icc = new InitialContext();
dss = (javax.sql.DataSource) icc.lookup("java:comp/env/jdbc/mydb2");
conn = dss.getConnection();
conn.setAutoCommit(false);
  java.sql.Statement stat = conn.createStatement();
  java.sql.ResultSet cols = stat.executeQuery("SELECT VZID,USER_NAME FROM CBSTDTFI.CS_USER_INFO WHERE VZID='"+username+"' FOR FETCH ONLY WITH UR ");
 while(cols.next()) {
   String VZID  = cols.getString("VZID");
   String USER_NAME  = cols.getString("USER_NAME");
%>
<OPTION value="<%= VZID %>"><%= VZID %></OPTION>
<OPTION value="<%= USER_NAME %>"><%= USER_NAME %></OPTION>
 
<% } // close while loop %>
 
<%
cols.close();
stat.close();
conn.close();
 
} catch (SQLException sqle) { sqle.printStackTrace(); }
%>
<BR><BR><BR>
<%= username %>
</BODY>
</HTML>
0
Comment
Question by:g118481
  • 4
  • 3
  • 3
10 Comments
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
You could do

stat.executeQuery("SELECT VZID,USER_NAME FROM CBSTDTFI.CS_USER_INFO WHERE VZID='"+username.trim()+"' FOR FETCH ONLY WITH UR ");
0
 
LVL 1

Author Comment

by:g118481
Comment Utility
I think i need to trim the "VZID".
How can I do that?
0
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
You can try

stat.executeQuery("SELECT VZID,USER_NAME FROM CBSTDTFI.CS_USER_INFO WHERE rtrim(ltrim(VZID))='"+username.trim()+"' FOR FETCH ONLY WITH UR ");

depending on your db
0
 
LVL 10

Accepted Solution

by:
mukundha_expert earned 250 total points
Comment Utility
>>I think i need to trim the "VZID".
>>How can I do that?
username is the value for VZID so you need to trim username ( which is the VZID),


try,

System.out.println ("SELECT VZID,USER_NAME FROM CBSTDTFI.CS_USER_INFO WHERE VZID='"+username+"' FOR FETCH ONLY WITH UR ");

before executing the query, check whether the username is the same as you expect.

i got the same problem once, the reason was the username had spaces in between

where are you getting the username from?? is it entered by user or you are getting from some where ( like remoteuser, NLTM authorization)
0
 
LVL 1

Author Comment

by:g118481
Comment Utility
mukundha_expert,

I added the System.out.println... that you suggested.  the code still returns a blank page.  
I am doing a print of the username, and it shows up fine.  However, VZID that should match up to the username from the query shows a blank.

I know the ID exists in the table.  What am I doing wrong.  very frustrating.
Also, I am going to increase the points for this record.  Looks like this will be difficult.

Cheers
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 10

Expert Comment

by:mukundha_expert
Comment Utility
>>the code still returns a blank page.

it will be printed in the server console.

>> am doing a print of the username, and it shows up fine

are you using out.println(); if so the blank spaces between the string will be ignored. see it in the console
0
 
LVL 1

Author Comment

by:g118481
Comment Utility
mukundha_expert,

I cannot view the server console.  This is running on Mainframe IBM Z/OS Websphere 5.0
0
 
LVL 10

Expert Comment

by:mukundha_expert
Comment Utility
ok, check the length of the string and see if it is equal to what you have thought

0
 
LVL 1

Author Comment

by:g118481
Comment Utility
OK, the last trim of the variable worked.  I now get the result I expected.
Thanks for your help.

Cheers
0
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
I suggested trimming the username ...
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now