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