Only works once, fails every time afterwards

[bnpmunson]

Hi Guys and Gals,

I am new to B4A however I am experienced with Basic (I go all the way back to the ARPANET days.) I have a test program which reads a location from the phone and sends it to a mySQL database when I click a button. The routine works fine the first click however it fails on every additional attempt until I close the program and re-load. Then I get one more successful iteration. I am sure its that I am not closing something properly however from the minimal documentation I can find I cannot figure what is the issue. Please take a look at my code and tell me if you see anything wrong. I am using LocationManagerEx and one of the mySQL libraries. Forget which one as I have tried 3 so far. I am clicking Button1 to run the test.

#Region  Project Attributes 
   #ApplicationLabel: com.android.lalo
   #VersionCode: 1
   #VersionName: PreRel_1
   'SupportedOrientations possible values: unspecified, landscape or portrait.
   #SupportedOrientations: portrait
   #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes 
   #FullScreen: False
   #IncludeTitle: True
#End Region

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.
   Dim Button1 As Button 
   Dim Button2 As Button
   Dim lv1 As ListView 
   Dim la As Double 
   Dim lo As Double 
   Dim AdoConn As AdoConnection 
   Dim AdoRec As AdoRecordset
   Dim iResult As Int 
   Dim sResult As String 
   Dim LabelResults As Label
   Dim myLoc As LocationManagerEx
   Dim LocResult As Location 
   
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("Test2Layout1")
   LabelResults.Text = "Ready."
End Sub

Sub Activity_Resume
   LabelResults.Text = "Ready."
End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub Button1_Click
   Button1.Color = Colors.Magenta
   myLoc.Initialize("Loc1")
   myLoc.requestMobileLocation
End Sub

Sub Loc1_locationchanged(locArg As Location)
   Dim bResult As Boolean 
   Dim tmpStr As String 
   
   Button1.Color = Colors.Green
   la = locArg.Latitude
   lo = locArg.Longitude 
   
   'connect to the cloud server
   AdoConn.ConnectionString( "23.22.237.49","3161","","reminders","xxxxxxxx","xxxxxxxx")
   AdoConn.ConnectionTimeout = 2
   AdoConn.LoginTimeout = 2
   AdoConn.SocketTimeout = 2
   
   tmpStr = "INSERT INTO latlon (user_id, lat, lon) VALUES (1, " & la & ", " & lo & ")"
   AdoConn.InitializeEvent 
   AdoConn.Open 
   'iResult = AdoConn.ExecuteInsertCommand(tmpStr)
   iResult = AdoConn.Execute(tmpStr)
   LabelResults.Text = iResult
   AdoConn.CommitTrans
   AdoConn.Close 
   Button1.Color = Colors.White
End Sub

 

[yttrium]

While you do save and close the transaction, you never actually do

AdoConn.BeginTrans

though I don't know if that will fix your issue. It might.

 

[Erel]

I'm not familiar with the MySQL library you are using. However it is probably a mistake to make these variables a Global variables. They should be Process_Globals and should only be initialized once.

Generally speaking I recommend you to use the web service based approach to connect to the MySQL server.

 

[mc73]

Generally speaking I recommend you to use the web service based approach to connect to the MySQL server.

I agree with this. After playing with some connectors, which were faster in execution and this was the reason I gave them a try, I've noticed some inefficiencies, sometimes small other times serious. Don't know if it's a problem of the libs' coding, or of the connector itself. So, for now, php is by far the most stable solution.

 

[bnpmunson]

I thought about the php solution however I am completely unfamiliar with PHP and from what I can tell there is no web service associated with the mysql server computer. I have no control over that computer or the software allowed to run on it. I am only allowed to work with the existing server.

I am attempting to extend the functionality of our existing inventory tracking database to be able to use android phones/tablets to track inventory as it moves thru our process. This is a much preferable solution to the problem as the other option is to purchase at $2000 a pop hand held windows mobile scanner/computers (we need 20 to start the upgrade). With the barcode lib that is available for b4a and a working mysql connector it would be easy to use phones or tablets to do the same job.

I also need a postgreSQL connector for another project. That one is a non-starter as far as I can tell since there are no postgre connectors.

 

[bnpmunson]

While you do save and close the transaction, you never actually do

AdoConn.BeginTrans

though I don't know if that will fix your issue. It might.

I tried adding that command however it did not change anything. Still can only run one command per session.

 

[yttrium]

I tried adding that command however it did not change anything. Still can only run one command per session.

Erel's comment about variable placement is probably the actual answer.