Android Question SQL ExecQuery fails silently

[alistair]

Hi All,

I am trying to use DBUtils.ExecuteHTML

I have a couple of tables with data in them. No problems with getting data in. I am now trying a simple table display using a webview just to get the hang of things.

I start the Result activity using this in a button click;

Sub btnResult_Click   
   'show the choice buttons
   'btnSurvey.Visible=True
   'btnResults.visible=True
   Dim chosenAction As Int
   Dim ChoiceList As id
   
   userChoice.Initialize
     userChoice.AddAll(Array As String("ORS","SRS"))
   
  chosenAction = ChoiceList.InputList1(userChoice,"Results Choice")
   
  'start the activity which displays the results
  'use the chosen action to direct the setup details
  CallSubDelayed2(Results,"SetUpFormDetails", chosenAction)
   
End Sub

I then try to set up the table view within the SetUpForm sub

Sub Activity_Create(FirstTime As Boolean)
   'Do not forget to load the layout file created with the visual designer. For example:
   Activity.LoadLayout("lytResults")

End Sub

Sub SetUpFormDetails(listChoice As Int)
   Dim selectDataString As String
   
   wbvDataTable.Visible = True
   
   Select listChoice
     
     Case 0
       
       Activity.Title= "ORS Table- " & Main.CurrentClientName
       
       'set up data from ORS
       selectDataString = "SELECT date as Date, orsIndiv , orsIntPer, orsSocial, orsSumm,orsAve FROM tbORS WHERE ClientID=" _
       & Main.CurrentClientID & " ORDER BY date"
       
       Main.SQL.BeginTransaction
       Try
       wbvDataTable.LoadHtml(DBUtils.ExecuteHtml(Main.SQL, selectDataString, Null, 0, False))
       Main.SQL.TransactionSuccessful
       Catch
         ToastMessageShow("The database transaction failed", True)
       End Try
       Main.SQL.EndTransaction
  
     Case 1
       
       Activity.Title="SRS Table- " & Main.CurrentClientName
       
       'set up data from SRS
       
       selectDataString = "SELECT date as Date, srsRelate , srsGoal, srsMeth, srsOverall , srsAve FROM tbSRS WHERE ClientID=" _
       & Main.CurrentClientID & " ORDER BY date"
       
         
       Main.SQL.BeginTransaction
       Try
       wbvDataTable.LoadHtml(DBUtils.ExecuteHtml(Main.SQL, selectDataString, Null, 0, False))
       Main.SQL.TransactionSuccessful
       Catch
         ToastMessageShow("The database transaction failed", True)
       End Try
       Main.SQL.EndTransaction
       
   End Select
End Sub

I have pointed to the database in my Main activity. It is in the right place on my tablet and there is no problem interacting with the database in the Main activity or in another activity (Measures) where my data entry is done.

The problem I have seems to be in what I am passing to the DBUtils.ExecuteHTML sub. Program execution stops on line 317, which is when it tries to pass my query off to the SQL library.

cur = SQL.ExecQuery(Query)

The problem is it fails silently. No error is logged. So I put in the Try... Catch and transaction statements above. Just so it failed a bit more elegantly.

I've checked in the debugger and the correct database is being pointed to, the SELECT statement is the one I've entered. So, I am missing something obvious and quite basic- it's just beyond me to know how to look for it.

Any ideas?

cheers

Alistair, Brisbane

 

[edgar_ortiz]

Hi,

Are you shure that:

• "listChoice" is 0 or 1? AND
• "ClientID" is a NUMBER?

I recommend you to "log" this variables.

Regards,

Edgar

 

[alistair]

Hi,

Thanks for looking and helping. I have figured it out. Know it was simple. It's that darned syntax for query statements.

this

'set up data from ORS
       selectDataString = "SELECT date, orsIndiv , orsIntPer, orsSocial, orsSumm,orsAve FROM tbORS WHERE clientID = " & Main.CurrentClientID & ORDER BY date"

should be this

'set up data from ORS
       selectDataString = "SELECT date, orsIndiv , orsIntPer, orsSocial, orsSumm,orsAve FROM tbORS WHERE clientID = '" & Main.CurrentClientID & "' ORDER BY date"

I'm thinking I need to have a look at using execquery2 if I don't want to try and figure out where all the dots and dashes go.

cheers

Alistair, Brisbane

 

[edgar_ortiz]

Yes...

If "clientID" is a "character" field you need to "enclose in quotes" the value to compare.

 

[Erel]

Always use parameterized queries (ExecQuery2). Never build the query by concatenating strings.

 

[alistair]

Thanks Erel,

This is my learning app. So, I'm learning... a lot.

I have rewritten the statements like this

'set up data from ORS
       selectDataString = "SELECT date, orsIndiv , orsIntPer, orsSocial, orsSumm,orsAve FROM tbORS WHERE clientID = ? ORDER BY date"
      
       Main.mySQL.BeginTransaction
       Try
       wbvDataTable.LoadHtml(DBUtils.ExecuteHtml(Main.mySQL, selectDataString, Main.CurrentClientID, 0, False))
       Main.mySQL.TransactionSuccessful
       Catch
         ToastMessageShow("The database transaction failed", True)
       End Try
       Main.mySQL.EndTransaction

I'm not sure if I need to do something with the question mark though. Should it be...

selectDataString = "SELECT date, orsIndiv , orsIntPer, orsSocial, orsSumm,orsAve FROM tbORS WHERE clientID = (?) ORDER BY date"

or just left as it is?

cheers

 

[Erel]

The parenthesis are not needed.

 

[alistair]

Thanks Erel,

I get an error with the original...

cannot cast type: {Type=String, Rank=0, RemoteObject=True} to {Type=String, Rank=1, RemoteObject=True}

So, that clearly means that the string type that I am passing (Main.CurrentClientID) isn't in the right form.

Can you tell from this message what form it should be in?

cheers

 

[Erel]

The third parameter is an array of strings with all the values that will go into the query.

DBUtils.ExecuteHtml(Main.mySQL, selectDataString, Array As String(Main.CurrentClientID), 0, False)

 

[alistair]

Thanks so much. Works perfectly.