How to query a fingerprint template to return data/row in mysql

[Jim_21]

Here is a sample of my code where I retrieve a fingerprint template stored in mysql database. I have stored the fingerprint template as BLOB type. I have a table with two fields or columns, CaseFileNumber and FingerP. I have stored the fingerprint template in the FingerP column and also case file number in the CaseFileNumber column When l verify the fingerprint it is able check against the database and verify if it exist or not. My problem is, how can l query the fingerprint to return the CaseFileNumber it is associated with. Here is my sample code below,

    Try
    Dim max As Integer = 2

    cn.Open()
    sqlCommand.CommandText = "Select * from ecms.fingerprint"
    sqlCommand.CommandType = CommandType.Text
    sqlCommand.Connection = cn
    Dim lrd As MySqlDataReader = sqlCommand.ExecuteReader()
    Dim usr
    While lrd.Read()
        usr = lrd("FingerP")
    End While
    bytes = Nothing
    bytes = usr
    template = New DPFP.Template()
    template.DeSerialize(usr)
    'Perform match
    matcher.Verify(FeatureSet, template, matchResult)
    If matchResult.Verified Then
        EventHandlerStatus = Gui.EventHandlerStatus.Success
        MessageBox.Show("Verified!")
        'Me.Hide()
        MessageBox.Show("Done")

    Else
        EventHandlerStatus = Gui.EventHandlerStatus.Failure
        MessageBox.Show("Verification failed, User does not exist")
    End If
    cn.Close()
Finally
    cn.Dispose()
End Try

sqlCommand.CommandText = " select CaseFileNumber from ecms.fingerprint  where FingerP like %bytes% "
cn.Open()
'Dim arrImage() As Byte
Dim publictable As New DataTable
Try
    'cn.Open()
    da.SelectCommand = sqlCommand
    da.Fill(publictable)
    txtcasenumber.Text = publictable.Rows(0).Item(1)
Finally
    da.Dispose()
    cn.Close()

End Try

It throws me the exception:: An exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll but was not handled in user code

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%bytes%' at line 1 If there is a handler for this exception, the program may be safely continued.

Any suggestions to fix this error is highly appreciated. Thanks

[cereal]

Hi,

%bytes% must be surrounded by quotes, because it's a string search, so change this:

sqlCommand.CommandText = " select CaseFileNumber from ecms.fingerprint  where FingerP like %bytes% "

to:

sqlCommand.CommandText = " select CaseFileNumber from ecms.fingerprint  where FingerP like '%bytes%' "

and see if it works.

[Jim_21]

hello, l tried your suggestion, but then it gives me another error:: An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code

Additional information: There is no row at position 0.

If there is a handler for this exception, the program may be safely continued.::
I tried changing a couple of things but no luck. Is there a way l can get the value l want and assing it into the textbox(txtcasenumber)? Thanks

[cereal]

The error says:

Additional information: There is no row at position 0.

So the query is not returning results, I read that da.Fill() can return the number of rows found by the query statement, so you can get that value and use a conditional statement to verify if the result is bigger than 0, and only in that case you attempt to access Rows(0), something like this:

(int)numRows = (int)da.Fill(publictable)

if(0 < numRows)
{
    txtcasenumber.Text = publictable.Rows(0).Item(1)
}

And you could also define a default case to manage empty result sets. Please, note that I'm not confident with VB.Net, so my above code could be wrong.

[Jim_21]

Hi, thanks your response. l tried your suggestion still no luck.

[Jim_21]

I suspect the fault to be from this block

        sqlCommand.CommandText = " select StudentID from student  where 
        print like '%template%' "

        Dim publictable As New DataTable
        Try

            adapter.SelectCommand = sqlCommand

            adapter.Fill(publictable)
            txtstudentID.Text = publictable.Rows(0).Item(1)
        Finally

            adapter.Dispose()
            cn.Close()
        End Try

I am passing a variable to an mysql statement. Now the variable resides in the code and not the database so querying it will return no results which l am currently getting. Is there a way to use select statement to query mysql to retrieve the studentID associated with the fingerprint template in the database(BLOB).

[peter_62]

Hi, thanks for your response. l tried your suggestion still no luck

[cereal]

You can add parameters to the selectCommand property, see these examples:

• https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldataadapter.selectcommand(v=vs.110).aspx
• https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

In your case, in pseudo code, try something like:

sqlCommand.CommandText = "select StudentID from student  where print like '%' + @template + '%'"
sqlCommand.Parameters.Add("@template", SqlDbType.NVarChar, 40, "Value Here")

Follow the examples in the documentation.