ORACLE - Whatsamatta with this?

[danthewebguy]

I'm trying to query an Oracle DB with the following select statement - I'm seeing if these two columns when joined together with no spaces = a variable I have already stored from the URL. My query always comes back "Record not found" when I know it does! What's going on here? Thanks for any help!

Dim oConnection, sSQL, rs, FNAME

sSQL = "SELECT * FROM " & DBOWNER & "PW_IDEA_SEC_CAM_MARKETINGLIST WHERE FNAME || LNAME = '" & sSourcePage & "' order BY FNAME, LNAME"

Set oConnection = Server.CreateObject("dbTools.dbSql")
Set rs = oConnection.dbSQLselect(sSQL, DB_NAME_PW)

If NOT rs.EOF Then
		'If its a valid login then set the session variable to True
		Session("blnValidMember") = True
		
		'redirect the user to the proper page as they have logged in properly
		Response.write "<strong>Guess what "& rs("FNAME") &"?</strong> <br><br>I found you in my database. You are a winner!"
        Response.write "<br><br>Your last name wouldn't be <strong>"& rs("LNAME") &"</strong> by any chance, would it?"
        'Response.Redirect "/main.asp"
Else
        'if not valid set the session to false
        Session("blnValidMember") = False

        'Redirect to the  not a valid user page
        Response.write "uh oh! You're not in my database!"
        'Response.Redirect "/custom_errors/404.asp"
End If

'Close Objects and free up memory 
'rs.Close
Set rs = Nothing
'oConnection.Close
Set oConnection=Nothing

[skhanal]

Is LNAME CHAR datatype in the table?. In that case the left and right side of = should be same length to match. If you change CHAR to VARCHAR2 then it should match.

[jkoopmann]

can you supply the table definition here and just run the SQL command from SQL*Plus?

Wondering if columns are defined as CHAR with trailing or leading spaces or VARCHAR2 with leading spaces. may be a need to TRIM the columns and get rid of the spaces.

you might also want to wrap the function LENGTH around the columns and see if they are what you expect for the data that is in them.

[danthewebguy]

Well, here's my SQL script from the table . . .

CREATE TABLE PW_IDEA_SEC_CAM_MARKETINGLIST
(
  FNAME     VARCHAR2(50 BYTE),
  LNAME     VARCHAR2(50 BYTE),
  TITLE     VARCHAR2(50 BYTE),
  COMPANY   VARCHAR2(50 BYTE),
  ADDRESS1  VARCHAR2(50 BYTE),
  ADDRESS2  VARCHAR2(50 BYTE),
  CITY      VARCHAR2(50 BYTE),
  STATE     VARCHAR2(50 BYTE),
  ZIP       VARCHAR2(50 BYTE)
)
TABLESPACE KSTABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );


GRANT INSERT, SELECT, UPDATE ON  PW_IDEA_SEC_CAM_MARKETINGLIST TO APP_USER;

[danthewebguy]

I can run the following SQL statement in SQL Editor (TOAD) and it returns the name just fine

SELECT * FROM PW_IDEA_SEC_CAM_MARKETINGLIST WHERE FNAME || LNAME='DanNolan' order BY FNAME, LNAME

[jkoopmann]

I would be curious about the LENGTH function around the columns.
can you run :
SELECT FNAME, length(FNAME), LNAME, length(LNAME) FROM PW_IDEA_SEC_CAM_MARKETINGLIST WHERE FNAME || LNAME='DanNolan'

also wondering if the script lang you are using might be putting in spaces where not needed. I would suggest hard-codeing in 'DanNolan' for the sSourcePage variable or changing FNAME || LNAME to FNAME||LNAME.

[danthewebguy]

okay, i did what you said and it did not work, BUT, I hand-coded 'dannolan' in the select statement as lowercase and typed in my url "www.company.com/dannolan" and it worked (sSourcePage is a variable that I'm puuling out of the url after the slash) but when I type DanNolan it does not work - case sensitive????

[jkoopmann]

sounds like TOAD may have some form of Case-Insensitivity turned on.
I would look at the data straight through SQL*Plus and see what the data looks like.

also, here is an article I wrote on case-insensitivity that might help.
http://databasejournal.com/features/...le.php/3494646

[danthewebguy]

scratch that - I was wrong - no case sensitivity going on here - one minute it worked, the next it didn't . . . I'm not sire what's going on here!

[danthewebguy]

also, when I " response.write sSourcePage " it returns the proper value in my browser . . .

[danthewebguy]

damned! it is a case sensitivity issue . . . gonna go check out your article -

[danthewebguy]

sSQL = "SELECT * FROM " & DBOWNER & "PW_IDEA_SEC_CAM_MARKETINGLIST WHERE UPPER(FNAME||LNAME)='" & UCase(sSourcePage) & "' "

WORKED!

Thanks man -