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

Discussion in 'Android Questions' started by Anser, Jan 17, 2015.

  1. Anser

    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    What have you tried to implement? It should be quite simple.

    You need to send a request and then handle the result in JobDone. The query should be something like:
    Code:
    SELECT * FROM users WHERE user = ? AND password = ?
    If there is 1 result for this query then the password is correct and you should move to the second activity.
     
  3. KMatle

    KMatle Expert Licensed User

  4. 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
     
  5. Peter Simpson

    Peter Simpson Expert Licensed User

    Your MySQL will read something like this
    Code:
    "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: Jan 19, 2015
  6. Anser

    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

    Code:
    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

    Code:
    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

    Code:
    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
     
  7. DonManfred

    DonManfred Expert Licensed User

    NO! NOT suddenly...

    You wrote
    Code:
    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.
     
    Douglas Farias and Peter Simpson like this.
  8. Anser

    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.
    Code:
    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.

    Code:
    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
     
  9. DonManfred

    DonManfred Expert Licensed User

    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...

    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)

    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.
    if you for ex starts a new activity or finish an running activity... This command is executed "WHEN THE SUB EXISTS".

    Code:
    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
    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.
     
    lemonisdead and Douglas Farias like this.
  10. DonManfred

    DonManfred Expert Licensed User

    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.
     
    lemonisdead and Douglas Farias like this.
  11. Anser

    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.

    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.

    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.

    Regards

    Anser
     
  12. DonManfred

    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
    Code:
    [..]
    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.
     
  13. Anser

    Anser Well-Known Member Licensed User

    Thank you DonManred. It worked. Excellent support and excellent product.
     
  14. Anser

    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
     
  15. DonManfred

    DonManfred Expert Licensed User

    try
    Code:
    sql.select_itemnames=SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%?%';
     
    lemonisdead likes this.
  16. 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

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

    Anser
     
  17. Peter Simpson

    Peter Simpson Expert Licensed User

     
    DonManfred likes this.
  18. 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

    In Config.Properties file, if I directly write the SQL statement (without the Parameter ?), then it is working fine and retrieveing around 65 records
    Code:
    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
    Code:
    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
    Code:
    reqManager.PrintTable(SqlStatement)
    I am stuck at this point. Any help will be appreciated.

    Regards

    Anser[/QUOTE]
     
  19. Anser

    Anser Well-Known Member Licensed User

    Dear Friends,

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

    Config.Properties
    Code:
    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.
    Code:
    SELECT Item_code, item_description FROM items_master WHERE item_description LIKE '%spring%'
    I am stuck at this point.

    Regards
    Anser
     
  20. Erel

    Erel Administrator Staff Member Licensed User

    It should be: LIKE ?
    And you should add the percentage in the value you send.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice