JSON 2 x sqlite queries combined - only 1 row

I try to create JSON with 2 sqlite queries. The problem is I only get 1st row from table customers with order_content back as JSON although there are more rows available for generating JSON response.

[code] data = db.SQLSelect (“SELECT id, customer_code_id FROM customers”)
If db.Error then
else
If data <> Nil Then

    Dim order_combined As New JSONItem
    Dim order As New JSONItem
    Dim content As New JSONItem
    Dim content_combined As New JSONItem
    
      While Not data.EOF
      
      order = New JSONItem
      Dim i As Integer
      For i = 1  to data.FieldCount
        order.Value(data.IdxField(i).Name) = data.IdxField(i).Value
      Next
      
      Dim id As String = data.Field("id").StringValue
      data = db.SQLSelect ("SELECT * FROM orders_content WHERE order_id = '"+id+"' ")
      
      If data <> Nil Then
        While Not data.EOF
          content = New JSONItem
          Dim ii As Integer
          For ii = 1  to data.FieldCount
            content.Value(data.IdxField(ii).Name) = data.IdxField(ii).Value
          Next
          data.MoveNext
          content_combined.Append content
        Wend
       
      End if
      
      data.MoveNext
      order.Value("content") = content_combined
      order_combined.Append order
    Wend
    
    data.Close
    db.Close
    
    order_combined.Compact = True
    Dim s As String =order_combined.ToString
    TextField1.text =s[/code]

data = db.SQLSelect ("SELECT id, customer_code_id FROM customers")
and

data = db.SQLSelect ("SELECT * FROM orders_content WHERE order_id = '"+id+"' ")

I may be wrong, but if you use twice data (the same variable), you will get only one returned data from the DataBase…

Use instead Data_Customer for the first and data_Order for the second.

Do not forget to change the old data occurences and replace them with the appropriate ones.

I have tried that one too, Dim data_order AS RecordSet + change old data occurences but it doesn’t work, I don’t get JSON created now.

[code] data = db.SQLSelect (“SELECT id, customer_code_id FROM customers”)
If db.Error then
else
If data <> Nil Then

    Dim order_combined As New JSONItem
    Dim order As New JSONItem
    Dim content As New JSONItem
    Dim content_combined As New JSONItem
    
      While Not data.EOF
      
      order = New JSONItem
      Dim i As Integer
      For i = 1  to data.FieldCount
        order.Value(data.IdxField(i).Name) = data.IdxField(i).Value
      Next
      
      Dim id As String = data.Field("id").StringValue
      Dim data_order As RecordSet

      data_order= db.SQLSelect ("SELECT * FROM orders_content WHERE order_id = '"+id+"' ")
      
      If data_order<> Nil Then
        While Not data_order.EOF
          content = New JSONItem
          Dim ii As Integer
          For ii = 1  to data_order.FieldCount
            content.Value(data_order.IdxField(ii).Name) = data_order.IdxField(ii).Value
          Next
          data_order.MoveNext
          content_combined.Append content
        Wend
       
      End if
      
      data.MoveNext
      order.Value("content") = content_combined
      order_combined.Append order
    Wend
    data_order.Close
    data.Close
    db.Close
    
    order_combined.Compact = True
    Dim s As String =order_combined.ToString
    TextField1.text =s[/code]

What said the debugger ?

No error, but also no JSON is created that way. I have no idea what could be wrong.

The code extract miss two end If (at most)

The line data_order.Close generate an error because you dim data_order inside the While …/… Wend loop.

Place a code break at the left of the order = New JSONItem line. Then run the project. When in the debugger, click in the step to next line (down arrow) and waht what happens (what is stored in the different variables.

BTW: I understand what you want to try.

The code below compiles here, but will it works in your project is a different question.

[code]
Dim data AS RecordSet // I added this line
Dim db As SQLiteDatabase // I added this line

data = db.SQLSelect (“SELECT id, customer_code_id FROM customers”)
If db.Error then
else
If data <> Nil Then

  Dim order_combined As New JSONItem
  Dim order As New JSONItem
  Dim content As New JSONItem
  Dim content_combined As New JSONItem
  
  While Not data.EOF
    
    order = New JSONItem
    Dim i As Integer
    For i = 1  to data.FieldCount
      order.Value(data.IdxField(i).Name) = data.IdxField(i).Value
    Next
    
    Dim id As String = data.Field("id").StringValue
    Dim data_order As RecordSet
    
    data_order= db.SQLSelect ("SELECT * FROM orders_content WHERE order_id = '"+id+"' ")
    
    If data_order<> Nil Then
      While Not data_order.EOF
        content = New JSONItem
        Dim ii As Integer
        For ii = 1  to data_order.FieldCount
          content.Value(data_order.IdxField(ii).Name) = data_order.IdxField(ii).Value
        Next
        data_order.MoveNext
        content_combined.Append content
      Wend
      
    End if
    
    data.MoveNext
    order.Value("content") = content_combined
    order_combined.Append order

    data_order.Close  // I moved up this line before Wend
  Wend
  
  data.Close
  db.Close
  
  order_combined.Compact = True
  
  Dim s As String = order_combined.ToString
  
  TextField1.Text = s
End If // I added this line

End If // I added this line[/code]

New idea: what if you extract the data from the data base and place them in your order of appearance directly in TextField1. Once the text in TextField1 is correct, re-install the json code lines ?

I got data_order = Nil in the line below (using a db of mine and modifying the SELECT parameters.

data_order = db.SQLSelect ("SELECT * FROM orders_content WHERE order_id = '"+id+"' ") *

Moving data_order.Close // I moved up this line before Wend line was a bad idea. Better move the Dim data_order As RecordSet line at near the top of the code.

  • This error may comes from my data base file.

I haven’t copy whole code at the end there are 2x End If.
I got the same error data_order = db.SQLSelect (“SELECT * FROM orders_content WHERE order_id = '”+id+"’ ") = NIL using data_order As RecordSet, but if I use data as RecordSet i got 1 row in JSON.
I have declared data_order As RecordSet at beginning and changed data to data_order, but it just doesn’t work.
PS. It does work if I declare

[code]Dim dbFile As FolderItem
Dim db2 As New SQLiteDatabase
dbFile = GetFolderItem(“db.sqlite”)
db2.DatabaseFile = dbFile
Dim data_order As RecordSet

If db2.Connect Then
db2.SQLExecute(“BEGIN TRANSACTION”)…[/code]

But result is the same. 1 row

The content_combined JSONItem does not seem to be needed. And because you don’t clear it each time through the loop, your data is getting exponentially larger with each iteration. This code (slightly tweaked to use the Eddie’s Electronics SQLite DB structure) seems to work for me:

  If Not DBConnect Then Return
  
  Dim customers As RecordSet
  customers = db.SQLSelect("SELECT ID, LastName FROM Customers")
  If db.Error Then
    
  Else
    If customers <> Nil Then
      Dim order_combined As New JSONItem
      Dim order As JSONItem
      Dim content As JSONItem
          
      While Not customers.EOF
        order = New JSONItem
        Dim i As Integer
        For i = 1 to customers.FieldCount
          order.Value(customers.IdxField(i).Name) = customers.IdxField(i).Value
        Next
             
        Dim id As String = customers.Field("id").StringValue
        Dim orders As RecordSet
        orders = db.SQLSelect ("SELECT * FROM Invoices WHERE CustomerID = '" + id + "'")
             
        If orders <> Nil Then
          While Not orders.EOF
            content = New JSONItem
            Dim ii As Integer
            For ii = 1 to orders.FieldCount
              content.Value(orders.IdxField(ii).Name) = orders.IdxField(ii).Value
            Next
            orders.MoveNext
          Wend
                
        End if
             
        customers.MoveNext
        order.Value("content") = content
        order_combined.Append(order)
      Wend
          
      order_combined.Compact = True
      Dim s As String = order_combined.ToString
      TextArea1.Text = s 
    End If
  End If

My testing data base file had something strange. Once I generated a brand new one, the code worked, the result is a bit strange. Maybe Paul is right. I watch carefuly my result and try to understand.
My code adds to the first Table (Customers) entry the whole contents of the second table (Invoices); of course, the contents of my own db Tables…

Edit:
I follow Paul steps and get what he said (certainly). So, John, beware of your original db file. Follow Paul advice and use the Eddie's Electronics.sqlite example file until you really get what you want, then check your db file (or start by checking your db file to get what you want).

I will rewrite the code according to Paul example.
But I still don’t get it why I got correct JSON but only for 1st record.

This line in your original code have been changed (the left part) content.Value(data.IdxField(ii).Name) = data.IdxField(ii).Value.

I may not see other change (beside the Table Names / Field Names).

Because you were were building the JSON wrong, your “content_combined” variable would contain all the results of every subquery. In my tests, the first row was never correct. It always had too much data even when I just limited the initial select to just a few rows. And when I let it run on all the data, it never finished (because it was exponential).

Your code work perfect Paul, the only problem is I only get one order per customer generated in JSON although they have multiple.

I can’t speak to your DB design. Did you update the SQL accordingly? For the Eddie’s Electronics DB, this gets all the invoices for each customer.

Yes I update SQL accordingly. If I collect all output from 2 queries in string and pass it to TextField I get all customers with all orders, same queries with JSON output only one (the last) order per each customer is generated.

Paul and Emile thank you for help. I have added content_combined JSONItem for each customer to hold all orders for selected customer, it seemed to work OK. My code is

[code] If db.Connect Then

Dim customers As RecordSet
customers = db.SQLSelect("SELECT ID, LastName FROM Customers")
If db.Error Then
  
Else
  If customers <> Nil Then
    Dim order_combined As New JSONItem
    Dim order As JSONItem
    Dim content As JSONItem
    Dim content_combined As New JSONItem
    
    
    While Not customers.EOF
      order = New JSONItem
      Dim i As Integer
      For i = 1 to customers.FieldCount
        order.Value(customers.IdxField(i).Name) = customers.IdxField(i).Value
      Next
      
      Dim id As String = customers.Field("id").StringValue
      Dim orders As RecordSet
      orders = db.SQLSelect ("SELECT * FROM Invoices WHERE CustomerID = '" + id + "'")
      
      If orders <> Nil Then
        content_combined = New JSONItem
        While Not orders.EOF
          content = New JSONItem
          Dim ii As Integer
          For ii = 1 to orders.FieldCount
            content.Value(orders.IdxField(ii).Name) = orders.IdxField(ii).Value
          Next
          orders.MoveNext
          content_combined.Append content
          
        Wend
        orders.Close
      End if
      
      customers.MoveNext
      order.Value("content") = content_combined
      order_combined.Append(order)
    Wend
    Customers.Close
    db.Close
    order_combined.Compact = True
    Dim s As String = order_combined.ToString
    TextField1.Text = s 
  End If
End If

End if[/code]