Android Question (ask) dynamic database connection

hugoyaw

Member
bro can you show me a link / a good way so I don't have to write the database name in

JdbcUrl = jdbc: mysql: // localhost / test? CharacterEncoding = utf8

so that I can set which database that I will select via the client side.

this is an example that more or less I mean

sql.operator_count = SELECT COUNT (file_operator.RECNO) 'COUNT' FROM? .file_operator WHERE file_operator.ID =? AND file_operator.PASSWORD_FINAL =? .System_string_encrypt (?)

I have tried but the cmd parameter thinks that the database name I entered has quotes so the query is wrong.
 

Albert Kallal

Active Member
Licensed User
There is nothing stopping you from say setting up a config screen. I have this screen for example:
so, like now how do you save or have any settings in your application?

1611039013457.png


So, from above, I build up the connection string like this:
B4X:
ub MySave
    '
    ' save all form data + controls
    ' add the constring, + USER + password as keys (main form will use these to connect)
    
    Main.driver  = "net.sourceforge.jtds.jdbc.Driver"
    Main.jdbcUrl = "jdbc:jtds:sqlserver://" & txtServer.Text & _
                            ";DatabaseName=" & txtDatabase.Text & _
                            ";instance=" & txtSQLInstance.Text
                            
    Main.Username = txtUser.Text
    Main.Password = txtPassword.Text
    Main.strSQLSelect = txtSQL.Text
    Main.strSQLSelectDetails = txtSQLDetails.Text
    
    StateManager.SetSetting("jdbcUrl", Main.jdbcUrl)

So I shove/save/have a ready made connection string based on the driver + information.

Then to open the database, I do this:

B4X:
    Main.mysql.InitializeAsync("mysqlWAIT", Main.driver, Main.jdbcUrl, Main.Username, Main.Password)
    Wait For mysqlWAIT_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success

so, then at this point, I am able to do any query I want against that database. Eg this:
B4X:
    ' assume valid connection
    ' query database for all tables
    Dim strSQL As String = "SELECT TABLE_NAME FROM " & txtDatabase.Text & ".INFORMATION_SCHEMA.TABLES " & _
                            "WHERE TABLE_TYPE = 'BASE TABLE'"
    Dim rst As JdbcResultSet
    
    ProgressBar1.Visible = True
    ListView1.Clear
    ListView1.SingleLineLayout.ItemHeight = 40dip
    Try
        Dim sf As Object = Main.mysql.ExecQueryAsync("mysqlWAIT", strSQL ,Null)
        Wait For (sf) mysqlWAIT_QueryComplete(Success As Boolean,   rst As JdbcResultSet)

        If Success Then
            Dim s As String = ""
            Do While rst.NextRow
                s = rst.GetString(rst.GetColumnName(0))
                ListView1.AddSingleLine2(s,s)
            Loop
            rst.Close
etc. etc. etc.

I suppose I could EVEN add the driver name to the above screen, and thus it would in theory connect to MySQL, or whatever server I am using. In this case, I am using a direct connection to SQL server (MSSQL), but the idea will work for just about any database.
So, the "driver" and things like the database name, IP address etc. certainly does not need to be hard coded. But you do have to "kind of" roll your own UI if you looking to have the user setup the database. The above query returns a list of tables in the database, and if you select a table, then I use that to build up a few ready to go sql queries that allows one to type in raw sql, or of course in most cases sql in your code.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
 
Upvote 0

hugoyaw

Member
Untitled.png


I have many offline clients based on desktop (made using vb.net).
Now I'm trying to make a complementary android application from my desktop version so I made it like that.
My current challenge is because I am trying to build using jRDC which is totally new to me so I think it will take a long time to research.
 
Upvote 0

Albert Kallal

Active Member
Licensed User
I use jts driver - which is a direct connect to the server. I set this up in "main" (as a global).

So, now in any other place in my database I am free to simple write code to update the database, or say pull records:

B4X:
Sub LoadData As ResumableSub

    If CustomListView1.Size > 0 Then
        Log("listview has data")
        Return True
    End If

    ProgressBar1.Visible = True
    'CustomListView1.Clear
  
    Try
        Dim sf As Object = mysql.ExecQueryAsync("mysqlWAIT", "SELECT TOP 55 ID, HotelName, City, FirstName FROM dbo.tblHotels ORDER BY ID",Null)
      
        Wait For (sf) mysqlWAIT_QueryComplete(Success As Boolean,   Crsr2 As JdbcResultSet)
        If Success Then
         
            Do While Crsr2.NextRow
                MyRowCount = MyRowCount + 1
                CustomListView1.Add(CreateItem(Crsr2), Crsr2.GetString(Crsr2.GetColumnName(0)))
            Loop
            Crsr2.Close
        End If
    Catch
        Success = False
        Log(LastException)
    End Try
  
    ProgressBar1.Visible = False
.etc.

If the code is outside of the "main" first routine, then the code becomes:

B4X:
Dim sf As Object = main.mysql.ExecQueryAsync("mysqlWAIT", "SELECT TOP 55 ID, HotelName, City, FirstName FROM dbo.tblHotels ORDER BY ID",Null)
      
        Wait For (sf) main.mysqlWAIT_QueryComplete(Success As Boolean,   Crsr2 As JdbcResultSet)
        If Success Then

So, once I have setup the connection - then the rest of the code does not have to deal with a conneciton.

So, in above, that code can go in any activty module - or quite much any place - just prefix "mysql" with "main".

the main connection setup as noted does this:

B4X:
    ProgressBar1.Visible = True
    Wait For (MyConnect) Complete (Result As Boolean)

    If (Result= True) Then
        LabelM1.Text = "Connect ok"
        ButtonM2.Visible = True
    Else
        LabelM1.Text = "Connect Fail"
    End If
    ProgressBar1.Visible = False

---- and routine MyConnect is this:

[code]
Sub MyConnect As ResumableSub
  
    mysql.InitializeAsync("mysqlWAIT", driver, jdbcUrl, Username, Password)
    Wait For mysqlWAIT_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success

So once I have made the connection, then I am simple free throughout the application to do sql query, updates or whatever. So, at this point the process is really quite much the same as .net.

of course I become REALLY tired of writing code over and over to "load up" and shove values into controls on a view. So I made a "general" routine that takes a row of data, and shoves it into the controls. I simply adopted a "made up" standard that I put the column name in the "tag" of the controls that I want to automatic fill out. This works VERY well, since then I don't even have to "generate" an instance of the control in code - I simply loop all controls in the view, and the ones that have a tag that matches a column, then I push out (set) the value of those controls.

And I adopted the standard of pulling the one row into a MAP.

So, I have:
reocrd (one row) to MAP
MAP to view/form
And then the reverse:
Form view to MAP.
MAP to update one row.

So to load up a form - set all controls, i do this:
B4X:
Sub MyLoad

    Dim strSQL As String
    strSQL = "SELECT * FROM Customers where CUSTOMERIDA = " & gID
  
    Dim rst As JdbcResultSet
    rst = Main.mysql.ExecQuery(strSQL)
    oldRecord.Initialize
    oldRecord = MyCode.RecordToMaps(rst)

    MyCode.FloaderMAP(Activity,oldRecord)

  
End Sub

So I pull the one row into a resultset. Send Reocrd to MAP, and then send MAP to the form in question (activty is the current view/form).
Now, of course I RARE but RARE write code that uses jdbc direct to update data in a view/form. I use sqlite as a local database, and then write my own "sync" routine based on lastupdate column that I add to each table.

To save edits in the view/form BACK to the table, then I do this:

B4X:
    Dim cMap As Map
    cMap.Initialize
    cMap = MyCode.FreaderMap(Activity)   ' form controls data to MAP
  
    ' Now save
    MyCode.FwriterMAP(gID,"CustomerIDA","Customers",oldRecord,cMap,False)
  
End Sub

So, I build a few helper routines. This allows me to rapid create a form (view) and not have to write code each time to "load up" + set the controls.

So for each view, I declare oldRecord - the record data before edits.

And then I wrote the above routine "FwriterMAP". It takes the old map (old record), the new map (new record - or edits by user).

But all in all, at any given point in time?

main.mySQL is a instance of the jdbc object, and that gives me the ability at any time and in any place in code the ability to execute SQL commands. And it in fact tends to be LESS code then say vb.net, since as above query shows - I don't have to deal with a connect object.

However, while this gives "easy" pull of data in code?

I also adopted the concept that when editing data, such data (one row) will be placed in a MAP.

As noted, I don't really recommend CRUD editing using a direct database connection (but if using a bridge as reocmmened - then no probelm).
However, my "sync" routines do in fact work very well, and I use a direct connection to the database (server) for the sync routines - and this worked out very well indeed.

So, one has to decide if they are going to write say a sql pull, and THEN write code to shove that data into controls (write that code each time, or write a "general" routine that does this dirty work - since it really is the same idea darn near each time (ie: take a row of data - load up the controls on that view).

This approach gives me "close" to a data bound form experience. So, I am able to bang out a form in minutes to edit data, since 99% of the work is simply using the designer to drop controls on a view - and I just set the "tags" to the column names. As noted, I like this a lot, since if I drop 10 controls on a view, I don't have to generate ONE control or define each control that I want to modify - my Fwriter routines to push (or pull) the current view into a MAP does all that work - and the code is "general". And then I have the two other routines (map to database update), or (database row to map).

When i get some time - I'll re-write these routines to work with the newer B4Xpages.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
 
Last edited:
Upvote 0

hugoyaw

Member
I use jts driver - which is a direct connect to the server. I set this up in "main" (as a global).

So, now in any other place in my database I am free to simple write code to update the database, or say pull records:

B4X:
Sub LoadData As ResumableSub

    If CustomListView1.Size > 0 Then
        Log("listview has data")
        Return True
    End If

    ProgressBar1.Visible = True
    'CustomListView1.Clear
 
    Try
        Dim sf As Object = mysql.ExecQueryAsync("mysqlWAIT", "SELECT TOP 55 ID, HotelName, City, FirstName FROM dbo.tblHotels ORDER BY ID",Null)
     
        Wait For (sf) mysqlWAIT_QueryComplete(Success As Boolean,   Crsr2 As JdbcResultSet)
        If Success Then
        
            Do While Crsr2.NextRow
                MyRowCount = MyRowCount + 1
                CustomListView1.Add(CreateItem(Crsr2), Crsr2.GetString(Crsr2.GetColumnName(0)))
            Loop
            Crsr2.Close
        End If
    Catch
        Success = False
        Log(LastException)
    End Try
 
    ProgressBar1.Visible = False
.etc.

If the code is outside of the "main" first routine, then the code becomes:

B4X:
Dim sf As Object = main.mysql.ExecQueryAsync("mysqlWAIT", "SELECT TOP 55 ID, HotelName, City, FirstName FROM dbo.tblHotels ORDER BY ID",Null)
     
        Wait For (sf) main.mysqlWAIT_QueryComplete(Success As Boolean,   Crsr2 As JdbcResultSet)
        If Success Then

So, once I have setup the connection - then the rest of the code does not have to deal with a conneciton.

So, in above, that code can go in any activty module - or quite much any place - just prefix "mysql" with "main".

the main connection setup as noted does this:

B4X:
    ProgressBar1.Visible = True
    Wait For (MyConnect) Complete (Result As Boolean)

    If (Result= True) Then
        LabelM1.Text = "Connect ok"
        ButtonM2.Visible = True
    Else
        LabelM1.Text = "Connect Fail"
    End If
    ProgressBar1.Visible = False

---- and routine MyConnect is this:

[code]
Sub MyConnect As ResumableSub
 
    mysql.InitializeAsync("mysqlWAIT", driver, jdbcUrl, Username, Password)
    Wait For mysqlWAIT_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success

So once I have made the connection, then I am simple free throughout the application to do sql query, updates or whatever. So, at this point the process is really quite much the same as .net.

of course I become REALLY tired of writing code over and over to "load up" and shove values into controls on a view. So I made a "general" routine that takes a row of data, and shoves it into the controls. I simply adopted a "made up" standard that I put the column name in the "tag" of the controls that I want to automatic fill out. This works VERY well, since then I don't even have to "generate" an instance of the control in code - I simply loop all controls in the view, and the ones that have a tag that matches a column, then I push out (set) the value of those controls.

And I adopted the standard of pulling the one row into a MAP.

So, I have:
reocrd (one row) to MAP
MAP to view/form
And then the reverse:
Form view to MAP.
MAP to update one row.

So to load up a form - set all controls, i do this:
B4X:
Sub MyLoad

    Dim strSQL As String
    strSQL = "SELECT * FROM Customers where CUSTOMERIDA = " & gID
 
    Dim rst As JdbcResultSet
    rst = Main.mysql.ExecQuery(strSQL)
    oldRecord.Initialize
    oldRecord = MyCode.RecordToMaps(rst)

    MyCode.FloaderMAP(Activity,oldRecord)

 
End Sub

So I pull the one row into a resultset. Send Reocrd to MAP, and then send MAP to the form in question (activty is the current view/form).
Now, of course I RARE but RARE write code that uses jdbc direct to update data in a view/form. I use sqlite as a local database, and then write my own "sync" routine based on lastupdate column that I add to each table.

To save edits in the view/form BACK to the table, then I do this:

B4X:
    Dim cMap As Map
    cMap.Initialize
    cMap = MyCode.FreaderMap(Activity)   ' form controls data to MAP
 
    ' Now save
    MyCode.FwriterMAP(gID,"CustomerIDA","Customers",oldRecord,cMap,False)
 
End Sub

So, I build a few helper routines. This allows me to rapid create a form (view) and not have to write code each time to "load up" + set the controls.

So for each view, I declare oldRecord - the record data before edits.

And then I wrote the above routine "FwriterMAP". It takes the old map (old record), the new map (new record - or edits by user).

But all in all, at any given point in time?

main.mySQL is a instance of the jdbc object, and that gives me the ability at any time and in any place in code the ability to execute SQL commands. And it in fact tends to be LESS code then say vb.net, since as above query shows - I don't have to deal with a connect object.

However, while this gives "easy" pull of data in code?

I also adopted the concept that when editing data, such data (one row) will be placed in a MAP.

As noted, I don't really recommend CRUD editing using a direct database connection (but if using a bridge as reocmmened - then no probelm).
However, my "sync" routines do in fact work very well, and I use a direct connection to the database (server) for the sync routines - and this worked out very well indeed.

So, one has to decide if they are going to write say a sql pull, and THEN write code to shove that data into controls (write that code each time, or write a "general" routine that does this dirty work - since it really is the same idea darn near each time (ie: take a row of data - load up the controls on that view).

This approach gives me "close" to a data bound form experience. So, I am able to bang out a form in minutes to edit data, since 99% of the work is simply using the designer to drop controls on a view - and I just set the "tags" to the column names. As noted, I like this a lot, since if I drop 10 controls on a view, I don't have to generate ONE control or define each control that I want to modify - my Fwriter routines to push (or pull) the current view into a MAP does all that work - and the code is "general". And then I have the two other routines (map to database update), or (database row to map).

When i get some time - I'll re-write these routines to work with the newer B4Xpages.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

Thank you very much mr. Albert this will probably take some time for me to understand and implement.

Can I ask for help to be guided to work on my project because I just happened to start making this Android version. I hope if mr. Albert helped me, I didn't do too many mistakes in the beginning so this project will be done well.

If possible, may I ask for your whatsapp number mr. Albert?
 
Upvote 0

Albert Kallal

Active Member
Licensed User
How do you eat an elephant?
Answer: One bite at a time!


It is best we keep the questions and answers in this "public" community. That way everyone ELSE benefits too!

So by you asking a questions in public here. then not just "you" gains, but everyone else here is helped and enriched.

So, create small bite sized questions. Ask (or search) in the forms for those bite sized issues and answers. And by putting all those bite sized questions and issues together? The result is then a elephant in reverse!

(You can cobble together a rather nice application on the above elephant concept - and achieve that goal for free!!

And more amazing you actually wind up helping other people here!

So, what this means?
You can get boat-loads of free help from this amazing community and group of people here. But those people give that time and knowledge away because it can help MANY people and not just one person.

This also means that you can't ask people here to cook your dinner or write your software.

The elephant point is the key concept here. You can break down your problems, your ideas, your designs into very small and EASY bite size problems and questions. And because they are "small" bite sized? Then you have 1000's of people here that can and will answer your questions!

If you string EACH small problem together, then everything is a small bite sized question and issue. One that the community here can answer and help you with. When you put all of those small bite size questions and issues you have together?

You wind up with a elephant, or in this case a wonderful software package. And that package will have been created and written with the help of amazing minds and people here!

So try to keep the questions and issues you have in this public form - that way everyone here benefits and not just you.

You would have to come up with a good reason why we would not share our ideas and answers here for OTHERS to benefit from those efforts!

That means you have to keep the questions easy and small (bite sized), else few will answer and help you. But if you are smart about this, you can achieve your goals by adopting this concept of adding up bite sized questions over time.

In some forums, there are people that I seen write VERY amazing applications - and they had little or no experience. But they broke things down into small questions and issues - and in most forums (including this one), you get 10 people answering, and each of those 10 people likely have 100+ combined years of software experience - you can't even get that kind of help if you paid for it, let alone for free and based on acts of charity by the wonderful people here!

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
 
Upvote 0

hugoyaw

Member
Thank you mr. Albert I apologize for my selfishness.
I hope you can understand my mistake.
I got enlightened after reading your reply.
I will try to be better.
 
Upvote 0
Top