• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

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

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
g118481
Asked:
g118481
  • 4
  • 3
  • 3
1 Solution
 
CEHJCommented:
You could do

stat.executeQuery("SELECT VZID,USER_NAME FROM CBSTDTFI.CS_USER_INFO WHERE VZID='"+username.trim()+"' FOR FETCH ONLY WITH UR ");
0
 
g118481Author Commented:
I think i need to trim the "VZID".
How can I do that?
0
 
CEHJCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mukundha_expertCommented:
>>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
 
g118481Author Commented:
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
 
mukundha_expertCommented:
>>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
 
g118481Author Commented:
mukundha_expert,

I cannot view the server console.  This is running on Mainframe IBM Z/OS Websphere 5.0
0
 
mukundha_expertCommented:
ok, check the length of the string and see if it is equal to what you have thought

0
 
g118481Author Commented:
OK, the last trim of the variable worked.  I now get the result I expected.
Thanks for your help.

Cheers
0
 
CEHJCommented:
I suggested trimming the username ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now