Problem with MySql access to data

[JTmartins]

Hello everyone.

I'm having a problem while accessing a mysql database.

In this particular case, I need to access 2 tables from the same database.

I fetch the data from the first table, and it works OK. I can see it on a list view.


Then I get the data from the the type.field where I have a value which is an ID to the second table.

I need to go to the second table using this ID and fetch the value.

That's where the problem starts.

The code is similar to the one below. and all variables apear to be properly DIMed in the correct Globals or Process Globals

It maybe a little messed at the moment, as I've tryed lots of diferent things.

The problem is that I have all the data from the first table, but while looping trough it, reading the ID i need and trying to read the values from the second table,I only get the first one.

It's like if the loop isn't working, but it is, because the toastmessage in sub ler_dados_aluno shows the correct values.

Can someone help me with this ? What am I doing wrong ?

Thanks in advance.

Sub Activity_Create(FirstTime As Boolean)
   If FirstTime Then
      hc.Initialize("hc")
   End If
   

   
   Activity.LoadLayout("1")
   ler_dados_agenda 

Sub ler_dados_agenda
   ProgressDialogShow("Lendo_agenda")
   ExecuteRemoteQuery("SELECT id_agenda,data,hora_inicio,hora_fim,id_aluno,id_instrutor,matricula,id_status FROM agenda ORDER BY id_agenda", ler_agenda) 
End Sub


Sub ler_dados_aluno (aluno As String)

id=aluno
ExecuteRemoteQuery("SELECT nome,apelido FROM alunos WHERE id_aluno='" & id &"'", ler_aluno) 

ToastMessageShow (id, False) - ALL ID VALUES APPEAR . NO PROBLEM HERE
   
End Sub

Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
   Dim req As HttpRequest
   req.InitializePost2("bla bla bla..my server and service, Query.GetBytes("UTF8"))
   hc.Execute(req, TaskId)
End Sub

Sub hc_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
   Log("Erro: " & Reason & ", codigo: " & StatusCode)
   If Response <> Null Then
      Log(Response.GetString("UTF8"))
      Response.Release
   End If
   ProgressDialogHide
End Sub


Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
   Dim res As String
   res = Response.GetString("UTF8")
   Log("resposta do servidor: " & res)
   Dim parser As JSONParser
   parser.Initialize(res)
   
   
   Select TaskId
      Case ler_agenda
         
         Dim agenda As List
         agenda = parser.NextArray 'devolve em maps
         For i = 0 To agenda.Size - 1
            Dim m As Map
            m = agenda.Get(i)
            
            Dim tl As linhas_agenda
            tl.campo1 = m.Get("id_agenda")
            tl.campo2 = m.Get("data")
            tl.campo3 = m.Get("hora_inicio")
            tl.campo4 = m.Get("hora_fim")
            tl.campo5 = m.Get("id_aluno")
            tl.campo6 = m.Get("id_instrutor")
            tl.campo7 = m.Get("matricula")
            tl.campo8 = m.Get("id_status")
            
         


            
            
   
   
   
   ler_dados_aluno (tl.campo5)
   ToastMessageShow (id, False)
   
         'ListView1.AddSingleLine(tl.campo1&" "& tl.campo2&" "&tl.campo3&" "&tl.campo4&" "&tl.campo5&" "&tl.campo6&" "&tl.campo7&" "&tl.campo8)
            
         
         Next
         ProgressDialogHide
      
         
         
         
      Case ler_aluno
   
      
         Dim alunos As List
         alunos = parser.NextArray 'devolve em maps
         For j = 0 To alunos.Size - 1
            Dim n As Map
            n = alunos.Get(j)
            
            la.nome = n.Get("nome")
            la.apelido = n.Get("apelido")
            nome2=n.Get("nome")
            apelido2=n.Get("apelido")
         
            
         Next

   
   End Select
   
   Response.Release
End Sub

 

[Erel]

Please use [ code ] [ /code ] tags (without spaces) when posting code.

It might be related to the common mistake described here: Variables & Objects in Basic4android

 

[JTmartins]

Code flow

Hi Erel,

Actually, when I debug step by step, it appears to me that it has to do with code flow.

I sure I haven't understand it properly.

I was expecting that when the program finds a XX.Execute(req, TaskId) after the endsub it would follow to XX_ResponseSuccess and execute the code in there, but that is not the case.

That's where I'm lost. I've read almost eerything I could find, but still do not understand the way code flows in this situations.

Can you help in some way ?

Thanks
Jose

 

[JTmartins]

Code flow

I managed to understand how the code flows...

But I still am around the problem...(the code is now very different from the one above)

If I understand well we can not reach hc_ResponseSuccess before the end sub, so if we are inside a for...next loop, it will never work.

I've tried for..next loops if..endif with counters..whatever I could remember, always with the same problem.

So how can we do a loop and inside that loop send a query to the server in each iteration ? I think this is the my real question !

What am I missing ?

Thanks

 

[Erel]

If I understand well we can not reach hc_ResponseSuccess before the end sub, so if we are inside a for...next loop, it will never work.

That is correct.

I recommend you to use HttpUtils2. It will make your code simpler. You can then send the next query each time in JobDone sub.

 

[JTmartins]

Similar problem

Hi Erel,

This is driving me nuts...

I will post a skeleton to try to explain my problem

This works without any problem

Sub Activity_Create

variables, etc

job1.initialize
job1.poststring (whatever)

endSub


sub jobdone

case 1

   code
   listview


case 2

   code



end select

end sub

But this does not work. Just one line added

Sub Activity_Create

variables, etc

  job1.initialize
  job1.poststring (whatever)

execute_anoter_sub


end sub

Sub execute_another_sub
 job2.initialize
 job2.poststring (whatever)
end sub

sub jobdone

case job1

   code
   listview


case job2

   code



end select

end sub

In other words, as soon as I add the other sub, the code never reaches JobDone. It continues to the new sub, without executing job1. So my problem persists, and I have tryed many different forms of solving the problem. (or at least the ones my programming skills can remember)

I need to read data from a table in a SQL server and store it on local DB - This is working OK

Then I need to go trough all records on the local DB, get the ID from a table, query the server again and retrieve more data based on the ID already stored localy.

This implies a loop (reading every line of the local DB and querying the server). And I can't make it work.

Ideas please !!!

Thanks
Jose

 

[DouglasNYoung]

Do you need the data in two tables on the local database?

Have you considered retrieving all the data with a SQL join?

e.g. select * from table1, table2 where table1.ID = table2.ID

Might solve your problem..

Douglas

 

[JTmartins]

N

 

[JTmartins]

My problem

Thanks Douglas


In the local DB I only use one table. In the server there are 2 tables (actually more)

From the first table I select all records where date=today. (today's student class like a time table, but it changes everyday)
I get the data from the server and store it locally in a table. One of the fields is the student_ID. No problem here. Working ok

Now, I have to read all the records from the local database table where date=today, and get the value from the field student_ID

The student data (name, phone number, etc) is stored in the server in another table.

So I have to loop trough all local records and in each iteration request to the server all student data based on the local ID.

I can get the data...the problem is that "firing" jobs inside the loop is always causing me problems. The first one works...then puffff.

I'm sure I'm doing something wrong. Everything is being done in the same activity, I don't know if that can lead to problems.

All ideas are welcome. Thanks

 

[JTmartins]

Found it

I think I found the problem...

I have to (re) Dim Job as httpjopb in every loop iteration.

I will do some more tests just to confirm..