Android Question Need a Sample login screen app connecting to MySQL using RDC

Anser

Well-Known Member
Licensed User
Hi all,

I am new to B4A and this is my first thread in the forum. I am a newbie and very much new to Android app development. I am not even sure whether this is the right forum to ask this question. Its only few days since I started reading articles on the forum.

I read the article regarding the RDC and I have done a setup on my PC and it is working fine. So that part is clear.

I have been searching this forum for a sample app which does the following using RDC

  1. When I run my app it should bring up a Login window that contains 2 EditText controls to key in the Login username and password. A button to start the login verification process on a remote MySQL server.
  2. The username and password should be validated with records in a MySQL Table (I know that it is not a good habit to keep the username and password on table inside the database, but to start off, I will use this method initially). If its a valid username & password, then the app should take the user to another screen where he will have multiple menu to do other required jobs. If the user typed an invalid username and password, then the app should inform the user that is is a an invalid login and should remain in the Login screen itself.

The MySQL database will have a Table named 'users' with 2 columns 'usename' and 'password'

I don't know whether what I am asking is too much or not. I feel that I need an initial push from the experienced people in the group to get me started.

Any help will be appreaciated.

Thanks & Regards

Anser
 

Anser

Well-Known Member
Licensed User
Dear Erel and KMatle,

Thank you very much.

Please forgive me, If I am asking very silly questions. Just keep in mind that I am very much new to Android development as well as to VisulBasic.

I was not sure how to move from one screen to another screen. I discovered that the code StartActivity('ActivityName') to do this and is a turning point for me.

After reading many threads here, I understand from Erel that RDC is the best/fastest and the safest way. Unfortunately, I couldn't locate any examples (may be I am yet to discover) other than what specified in this thread. http://www.b4x.com/android/forum/th...c-connect-to-any-remote-db.31540/#post-183817



Regards

Anser
 

Peter Simpson

Expert
Licensed User
Your MySQL will read something like this
B4X:
"SELECT * FROM `users` WHERE `username`='" & TxtUsername.Text & "' AND `password`='" & TxtPassword.Text & "'"
This query will compare what was typed into the Edittext `username` and `passwords` boxes with what's in the `users` table of the MySQL database. If the `username` and `passwords` matches it will then retrieve all the data in that column.

1.png
 
Last edited:

Anser

Well-Known Member
Licensed User
Dear Peter Simpson,

Thank you very much for your support.

In fact my doubt is regarding how to handle the program flow using B4A.

I am happy that now I am able to create a simple app with a login screen which validates the login against a table in MySQL server connected via RDC.

With the use of StartActivity('ActivityName'), I am able to move to a next screen using the following code. Unfortunately, I don't know whether this is the right way.

Here is my code for Sub JobDone in my first activity ie validating the login. Can anyone confirm that whether this is the right way to go ahead ?.

What about calling Job.Release before moving to the SecondActivity from the JobDone ?. How can I return a Value from JobDone ie dor eg a value either 1 or 2 ie 1 for success and 2 for failed, so that I can handle the app flow (ie calling the second activity) from outside the Sub JobDone

B4X:
Sub JobDone(Job As HttpJob)
    Dim LoginStatus As Int
    LoginStatus=0
'    Msgbox(LoginStatus,"Value")
  If Job.Success = False Then
     Log("Error: " & Job.ErrorMessage)
  Else
     If Job.JobName = "DBRequest" Then
       Dim result As DBResult = reqManager.HandleJob(Job)
             If result.Tag = "login" Then 'query tag
            reqManager.PrintTable(result)
                 If result.Rows.size > 0 Then
                  LoginStatus=1
'                     Msgbox("Valid user "& LoginStatus,"Login successful")
                    StartActivity("Search")
                 Else
                    LoginStatus=0               
                     Msgbox("Invalid user "& LoginStatus,"Login UnSuccessful")
                 End If
             End If
     End If
  End If

'   Job.Release   && This line errors out
'    Msgbox(LoginStatus,"Value")
'    If LoginStatus = 1
'        StartActivity("Search")
'    End If
    Job.Release
End Sub
I am not sure about the Job.Release part. Whether that should be the last statement in the Sub JobDone(Job As HttpJob)

Initially I though that, if the login username and password is correct ie a valid user then I will follow the following sequence

B4X:
Job.Release  ' Job.Release should be done first, so that the memory is free and the user moves to the second activity screen
StartActivity("Search")
unfortunately the above code gives compilation error. Any advice regarding this will be appreciated.

Problem No 1:

Another issue faced is that after successful login, I move to the second activity. I don't want my user to go back to the initial login page again by pressing the back button from the second activity. In fact it is not right to go back to the login page once again after the user already logged in successfully.

I don't know how to control this. What I need is that, from the second activity, if the user press the back key, then the App should ask the user whether to Exit the app or not. If the user opted to exit the app then I need to quit the app without going back to the previous screen ie the login screen.

When the app asked the user "Exit the App ? Yes OR No ?" and if the user opted No, then the app should remain in the same screen

Problem No 2:

As I already said after successful login, the app takes the user to the second activity named 'Search"

This activity contains the following views

Spinner -> To provide the user an option to search on ItemCode or ItemName
EditText -> For the user to key in the item to be searched
Button -> to start the search
ListView -> to display the items that matched the search condition

Now, when I run my app, what happens is that after the user typed the search item in the EditText and clicks the "Search" button my app suddenly goes back to the previous screen ie the login screen. I beleive that this is something wrong with the way that I am coding.

Here is the code that I am using in my second activity named "Search". Please remember this activity is called from the FirstActivity. I don't know where I have went wrong. I understand that the Sub JobDone on this particular activity is not getting executed

B4X:
Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    Private btnSearch As Button
    Private edtSearchWord As EditText
    Private lvLocationList As ListView
    Private spnSearchType As Spinner
  
    Dim SelectedSrchType As String
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("Search")
    If FirstTime Then
        spnSearchType.Add("ItemCode wise")
        spnSearchType.Add("ItemName wise")
        spnSearchType.SelectedIndex=0 'Make the first item as selected by default
    End If
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub


Sub btnSearch_Click
    If edtSearchWord.Text = "" Then
        Msgbox("Please enter the item to search","Invalid search")
    Else
        searchparts(edtSearchWord.Text)
    End If
  
End Sub
Sub spnSearchType_ItemClick (Position As Int, Value As Object)
    SelectedSrchType=Value  
End Sub

Sub searchparts(SearchString)
  Msgbox(SearchString,"SearchString Val")
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name="select_parts"
    cmd.Parameters=Array As Object(SearchString)
    Main.reqManager.ExecuteQuery(cmd, 0, "search")

End Sub

Sub JobDone(Job As HttpJob)
  Msgbox("reached JobDone","Test") ' This is not getting executed
  If Job.Success = False Then
     Log("Error: " & Job.ErrorMessage)
  Else
     If Job.JobName = "DBRequest" Then
       Dim result As DBResult = Main.reqManager.HandleJob(Job)
             lvLocationList.Clear
             If result.Tag = "search" Then 'query tag
            Main.reqManager.PrintTable(result)
                 Msgbox(result.Rows.size,"No of records")
                 For Each records() As Object In result.Rows
                  lvLocationList.AddSingleLine( records(result.Columns.Get("Branch_Code")) & ":" & records(result.Columns.Get("Curr_Qty")) )
                    lvLocationList.SingleLineLayout.ItemHeight=40
                    lvLocationList.SingleLineLayout.Label.TextSize=20
                    lvLocationList.SingleLineLayout.Label.TextColor=Colors.Black
                    lvLocationList.SingleLineLayout.Label.Color=Colors.White
               
                 Next
                 Msgbox("Data loaded", "see this")
             End If
     End If
  End If
  Job.Release
End Sub
Appreciate your help.

Regards

Anser
 

DonManfred

Expert
Licensed User
Now, when I run my app, what happens is that after the user typed the search item in the EditText and clicks the "Search" button my app suddenly goes back to the previous screen ie the login screen.
NO! NOT suddenly...

You wrote
B4X:
Sub searchparts(SearchString)
  Msgbox(SearchString,"SearchString Val")
    Dim cmd As DBCommand
    cmd.Initialize
    cmd.Name="select_parts"
    cmd.Parameters=Array As Object(SearchString)
    Main.reqManager.ExecuteQuery(cmd, 0, "search")

End Sub
So you are calling the job in MAIN activity.... The result will return to main too.... In your case the job done in your main is called. This is expected and not suddenly...

Create a service who manages the jobs you call. job done in service too.
When a job finishes then delegate the result to the activity where it belongs too.
 

Anser

Well-Known Member
Licensed User
Dear DonManfred,

Thank you for pointing me to the right direction.

I thought that whatever declared in Sub Process_Globals is Global/Public variable and can be accessed from all other modules including other Activities too. Hence, I tried to use the same reqManager (declared in the main) in all other Actrivity Module too.

So you mean to say that in each Activity Module I should declare and use separate connection reqManager.Initialize(Me, "http://192.168.0.100:17178") as given below.
B4X:
Sub Process_Globals
  'These global variables will be declared once when the application starts.
  'These variables can be accessed from all modules.
End Sub
Sub Globals
'These global variables will be redeclared each time the activity is created.
'These variables can only be accessed from this module.
  Private reqManager AS ...........
End Sub
Sub Activity_Create(FirstTime AsBoolean)
  If FirstTime Then
     reqManager.Initialize(Me, "http://192.168.0.100:17178")
  End If
End Sub
Shall try your suggestion. Thank you. :)

My concerns:-

So is there any way OR is there a need to close these database connections when we quit the program. If we are supposed to close the connection to the database, then how we do it ?.

In other programming languages, we establish a connection to the database during the program start and maintain that connection object till we quit the application. While we quit the application, we disconnect the connection object from the database and free the used resources. Is there such need here in Android and B4A ?

I have shared below the code that I have used. Can anybody confirm whether this is the correct way to start a new activity from Sub JobDone. From the Sub JobDone, I am calling the StartActivity() first and then Job.Release, ie before calling the Job.Release, I am calling StartActivity(). So what happens to the Job.Release ? Is this the right way ?

If I do Job.Release and then call StartActivty() then it gives compilation error.

Can anybody please confirm whether the following code is the right way to do.

B4X:
Sub JobDone(Job As HttpJob)
    Dim LoginStatus As Int
    LoginStatus=0
'    Msgbox(LoginStatus,"Value")
  If Job.Success = False Then
     Log("Error: " & Job.ErrorMessage)
  Else
     If Job.JobName = "DBRequest" Then
       Dim result As DBResult = reqManager.HandleJob(Job)
             If result.Tag = "login" Then 'query tag
            reqManager.PrintTable(result)
                 If result.Rows.size > 0 Then
                  LoginStatus=1
                    StartActivity("Search") ' Is this the right way?. So what happens to the Job.Release ?
                 Else
                    LoginStatus=0              
                     Msgbox("Invalid user "& LoginStatus,"Login UnSuccessful")
                 End If
             End If
     End If
  End If

' My idea was to use a variable to store a value to understand whether the login was successful or not
' ie
' If result.Rows.size > 0 Then
'   LoginStatus = 1
' Else
'   LoginStatus = 0
' End If
' Job.Release   && But calling Job.Release before StartActivty gives compile error
' If LoginStatus = 1 Then
'   StartActivity("Search")
' Else
' End if

  Job.Release
End Sub

' Is there any way to return a value from Sub JobDone ?
Regards

Anser
 

DonManfred

Expert
Licensed User
I thought that whatever declared in Sub Process_Globals is Global/Public variable and can be accessed from all other modules including other Activities too
I did not tell something other. You CAN access them and you DID and get no error. But you need to know about what happens when you start a http job (the job done is raised on that activity where the job is started from). THIS you must always have in mind...

So is there any way OR is there a need to close these database connections when we quit the program. If we are supposed to close the connection to the database, then how we do it ?.
No. You are calling an RDC. The RDC (on server side) opens the db, retrieve and post back the data and then rdc closed the connection to the database. You dont need to close in your app (as you did NOT open any database)

In other programming languages, we establish a connection to the database during the program start and maintain that connection object till we quit the application. While we quit the application, we disconnect the connection object from the database and free the used resources. Is there such need here in Android and B4A ?
The resources are NOT used in your app. They are used in the RDC on server side. The rdc need to get rid of closing databases and so on. You dont have to care about this when you are using an rdc.
So what happens to the Job.Release ? Is this the right way ?
if you for ex starts a new activity or finish an running activity... This command is executed "WHEN THE SUB EXISTS".

B4X:
Sub JobDone(Job As HttpJob)
    ' Do whatever you need to do here...
    ' You can start a new activity for ex.
   '
    ' NOTE that the next line must ALWAYS be the last command in your job done sub....
    '  Remember that you dont need to care about open databases as the rdc is resposive for that.
    '
    ' But YOU are responsive to free the memoy the jobs uses with job.Release
    Job.Release
End sub
If I do Job.Release and then call StartActivty() then it gives compilation error.
Dont say "i got an error" and then you did NOT post the error you got! That´s the wrong way to get help from us.
 

DonManfred

Expert
Licensed User
NOTE that the next line must ALWAYS be the last command in your job done sub...
Addition:

It must NOT be the last line.... You can call it whenever you want inside the job done sub. But the Job MUST be free. Be careful. When you free the job you are NOT ALLOWED to use the variable "Job" anymore inside this sub after freeing.... Keep this in mind.

Usually the best place for this command is the end of the job done sub.
 

Anser

Well-Known Member
Licensed User
Dear DonManfred,

Thank you very much for clearing my doubts regarding releasing the RDC connections as well as about the Job.Release.

Now, when I run my app, what happens is that after the user typed the search item in the EditText and clicks the "Search" button my app suddenly goes back to the previous screen ie the login screen. I beleive that this is something wrong with the way that I am coding.
Your suggestions resolved my issue stated above in the Quote. I declared and opened a new Private reqManager As DBRequestManager in the Sub Globals inside my second activity and it is working fine as expected.

If I do Job.Release and then call StartActivty() then it gives compilation error.
The above statement made by me is wrong. I tried calling Job.Release first, then called StartActivity("SecondActivity") and found that it is working fine. I assume that I might have went wrong somewhere else. I am really sorry. I am correcting it here so that it doesn't mislead other newbies.

Need one more help regarding the below given issue. It is not an issue, but I don't know how to get it done. How do we get this done ie prevent the user from going back to a previous screen/activity especially prevent the user from going back to the login screen.

Problem No 1:

Another issue faced is that after successful login, I move to the second activity. I don't want my user to go back to the initial login page again by pressing the back button from the second activity. In fact it is not right to go back to the login page once again after the user already logged in successfully.

I don't know how to control this. What I need is that, from the second activity, if the user press the back key, then the App should ask the user whether to Exit the app or not. If the user opted to exit the app then I need to quit the app without going back to the previous screen ie the login screen.

If the app asked the user "Exit the App ? Yes OR No ?" and if the user opted No, then the app should remain in the same screen
Regards

Anser
 

DonManfred

Expert
Licensed User
Have a look at this Class. When you delegate the user from login (after it was successful!) to another activity then you can use
B4X:
[..]
Activity.Finish
Startactivity(second)
Job.Release
end sub
and then you add the class to the second activity

You can also use a global variable to remember the login-state.
 

Anser

Well-Known Member
Licensed User
How do I use the SQL statement LIKE '%spring%' using RDC

Required SQL statement

SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%spring%'

Config.properties
sql.select_itemnames=SELECT Item_code, item_description FROM items_master WHERE item_description LIKE %?%

I get the following Error
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '%'spring'%' at line 1

Regards

Anser
 

Anser

Well-Known Member
Licensed User
After applying your suggestion, it does not give error, unfortunately it is not fetching any records from the database. It says 0 records

Tag: search, Columns: 3, Rows: 0

when I run the same query directly on MySQL using HeidiSQL, it is retrieving 67 records.

Is there any way to know whether the following query is formatted as expected on RDC, something like

B4X:
reqManager.PrintTable(result)
B4X:
sql.select_itemnames=SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%?%';
Regards

Anser
 

Anser

Well-Known Member
Licensed User
Dear Peter Simpson,

The statement suggested by you (ie with the double quotes around the SQL statement) is throwing SQL error
sql.select_itemnames="SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%spring%'"

In Config.Properties file, if I directly write the SQL statement (without the Parameter ?), then it is working fine and retrieveing around 65 records
B4X:
sql.select_itemnames=SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%spring%'

But my requirement is to search for the items based on the value typed by the user in the EditText box
B4X:
sql.select_itemnames=SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%?%'
The above statement does not retrieve any rows.

Is there any way to view the final SQL statement generated by the RDC ?. For eg. something similar to
B4X:
reqManager.PrintTable(SqlStatement)
I am stuck at this point. Any help will be appreciated.

Regards

Anser[/QUOTE]
 

Anser

Well-Known Member
Licensed User
Dear Friends,

Any help regarding the usage of the following SQL command via RDC.

Config.Properties
B4X:
sql.select_itemnames=SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%?%'
I am NOT getting the desired output. No errors, but NO records are fetched. Is this a limitation of RDC ?

When I tried the SQL query directly on HeidiSQL, I am getting the desired output. For Eg.
B4X:
SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%spring%'
I am stuck at this point.

Regards
Anser
 
Top