[RESOLVED] [2005] Oracle Select Query Problem

[ydarb]

Hi, I am trying to pull data from an Oracle table using a Select query with a Where clause. When the Where clause contains 4 or less characters, it works fine. When I try something with more that 4, it won't pull anything. I have verified the data is actually in the table, and have tried hardcoded the criteria with no success. Any suggestions? thanks!

Dim objcommand As New OracleCommand("SELECT * FROM APP_SECURITY WHERE APP_SECURITY.USER_NAME = 'test1'", objConnection)

[bmahler]

Couple of questions,

What is tha MaxSize of the USER_NAME Field?
What Is the error that you are getting?

[ydarb]

Hi,

The size is set at 8, and I am not trying anything longer than that.
There is no actual error message, it just doesn't return anything when > 4 characters. Here is the code i am using to evaluated the results:

objDataReader = objcommand.ExecuteReader()
        If objDataReader.HasRows Then
            objDataReader.Read()
            MsgBox(objDataReader.Item(0))
        Else
            MsgBox("nuttin")
        End If

[bmahler]

is the user name test1 in the database?

[ydarb]

yes it is. i've tried other ones too that are in there...even copied directly from the database and no luck.

[GaryMazzone]

Is the username in test1 in the same case? To Oracle test1 and Test1 are two different things.

[ydarb]

Gary- Yes it is, I tried several others too copying directly from the tables.

[bmahler]

Can you post your complete code from this procedure

[ydarb]

Private Sub cmdLogin_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
        Me.APP_SECURITY1TableAdapter.Fill(Me.DataSet2.APP_SECURITY)
 
        'Initialize the Connection object
        objConnection = New OracleConnection(strConnectionString)
        objConnection.Open()
 
        Dim objcommand As New OracleCommand("SELECT * FROM APP_SECURITY WHERE APP_SECURITY.USER_NAME = '" & Me.txtID.Text & "'", objConnection)
 
        objDataAdapter = New OracleDataAdapter
        objDataAdapter.SelectCommand = objcommand
 
        'Initialize the DataTable object
        objDataTable = New DataTable
 
        'Populate the DataTable
        objDataAdapter.Fill(objDataTable)
 
 
        'Execute the query
        objDataReader = objcommand.ExecuteReader()
        If objDataReader.HasRows Then
            objDataReader.Read()
            MsgBox(objDataReader.Item(0))
        Else
            MsgBox("nuttin")
        End If
        objcommand.Dispose()
        objcommand = Nothing
        objDataAdapter.Dispose()
        objDataAdapter = Nothing
        objDataTable.Dispose()
        objDataTable = Nothing
        objConnection.Close()
 
        'Clean up   
 
        objcommand = Nothing
        objConnection.Dispose()
        objConnection = Nothing
 
    End Sub

[GaryMazzone]

This is what I use to login to a DB:

Dim bConnected As Boolean = False
        Dim strConn As String
        Dim oConn As System.Data.OracleClient.OracleConnection
        Dim oCmd As System.Data.OracleClient.OracleCommand
        Dim oDr As System.Data.OracleClient.OracleDataReader
        Dim strSQL As String = "SELECT * FROM APP_SECURITY WHERE APP_SECURITY.USER_NAME = '" & Me.txtID.Text.replace("'","''").Trim() & "'"
 
        strConn = mdlDatabase.BuildConnString()
        Try
            oConn = New System.Data.OracleClient.OracleConnection(strConn)
            oConn.Open()
            bConnected = True
 
            If bConnected = True Then
 
                oCmd = New System.Data.OracleClient.OracleCommand
               
                With oCmd
                    .Connection = oConn
                    .CommandType = CommandType.Text
                    .CommandText = strSQL
                    oDr = .ExecuteReader(CommandBehavior.SingleRow)
                End With
                if oDR.HasRows
                    oDR.Read()
                    MsgBox(oDR.Item(0).ToString()
                else
                    MsgBox("Problem with Select!" & system.enviorment.newline & strSQL)
                end if
                
            End If
 
        Catch exSQL As System.Data.OracleClient.OracleException
            MessageBox.Show("There was an error with the database." & System.Environment.NewLine & _
                            "The error is: " & exSQL.Message, "Data Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        Catch ex As Exception
            MessageBox.Show("An unexpected error occured in the application." & System.Environment.NewLine & _
                            "The error is: " & ex.Message, "Unexpected Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        Finally
            If Not oCmd Is Nothing Then
                If Not oDr Is Nothing Then
                    oDr.Close()
                End If
                oCmd.Dispose()
            End If
            If Not oConn Is Nothing Then
                oConn.Close()
                oConn.Dispose()
            End If
        End Try

[ydarb]

I tried this and it works fine with a user_id < 4 characters, but still doesn't work if it's more that 4. It gives the "Problem with Select!" message.

[GaryMazzone]

Post the exact error message and the exact SQL statemtent that is being sent to the database. The message you say was posted means that the select returned no rows. So I guess I still want to see the exact statemtent being sent to the DB

[ydarb]

I agree, that's what the error is, I just don't understand why it's working for the shorter strings. Here's the info you requested:

Dim strSQL As String = "SELECT * FROM APP_SECURITY WHERE APP_SECURITY.USER_NAME = '" & Me.txtID.Text.Replace("'", "''").Trim() & "'"

The error is the msgbox error "Problem with Select!" from this section:

If oDr.HasRows Then
                    oDr.Read()
                    MsgBox(oDr.Item(0).ToString())
                Else
                    MsgBox("Problem with Select!")
                End If

[GaryMazzone]

No I want the SQL from the application as it is running. Set a break point after you dim the SQL and fill the string. Then select the strSQL variable and Select the text visualizer and copy and paste the SQL string from there.

[ydarb]

Sorry, I misunderstood. Here it is:

SELECT * FROM APP_SECURITY WHERE APP_SECURITY.USER_NAME = 'wrightm'

[GaryMazzone]

I don't know then I use that same post I gave you for returning 'Gary Mazzone' as a user named in a table an have no issues.

[ydarb]

Well I appreciate your help. I will keep trying things and see what happens.

[GaryMazzone]

Does the select statement work from SQL*Plus?

[JAKSupport]

Think about using Stored Procedures instead of inline SQL. For one thing, security is an issue. Secondly, the performance is a lot worse using the above code. Third, rights is a big issue with companies, can anyone run that sql? Sure its inline, but sprocs you can assign specific permissions for.

These are just my suggestions.

[ydarb]

I figured out why it is happening! Apparently within the database there are multiple schemas. for some reason it is defaulting to a different one than I was looking at. Two of the schemas (or is it scheme?) have an app_security table with some of the same users I was trying. The ones that weren't in the schema I was using just happened to be longer. So...my question now is how can I specify what schema to use?

[ydarb]

JAKSupport, thanks for the suggestion. I thought since the table I am trying to use is small, I didn't think performance would be a concern. The other points you raised are very good and I will definately give it a try. Thanks!

[GaryMazzone]

If you have more then one schema the one that will be looked at is the one based on the user name you are loging into the Oracle database as. If you need to look at a table in another scehma then you need rights on the schema.table If you don't have rights you will not get a return. In order to access the table in another schema you can do one of two things.

1. Create a public synonm for the table (this will cause problems for the other user though).
2. Fully quallify the path to the table (this would include the schema name infront of the table name).

PS You will run into the exact same issue with SP's

[ydarb]

I went with option #2 and it's worked! Thanks again for your help!

[GaryMazzone]

If this solved your issue pleas mark the thread as resolved