FoxPro Programming
Nesting and Array Objects
Gravatar is a globally recognized avatar based on your email address. Nesting and Array Objects
  Harvey Mushman
  All
  Dec 17, 2014 @ 06:50pm
I have a case when my JSON result will be made up of simple properties and it needs to include an array of objects.

My cursor includes an empty memo field that I want to fill with a JSON array of objects. It looks something like the following

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

SCAN
REPLACE myArray with "[{val : 1, lbl : 'each'},{ val:2,lbl:'lbs'}]"
ENDSCAN

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("cursor_rawarray:tCursor")

Response.Write(lcJSON)


But my result in the browser includes an extra set of double quotes around the square brackets so it is NOT being interpreted as an Array but rather a strange string.

Is there a trick to doing this sort of thing?

Gravatar is a globally recognized avatar based on your email address. Re: Nesting and Array Objects
  Rick Strahl
  Harvey Mushman
  Dec 18, 2014 @ 02:07am

LOL!

Tables are flat structures and you're trying to turn them into hierarchical objects. I don't think you can do what you want with cursors. Strings will be JSON encoded - always - because cursors are FLAT!

You can do what you want with objects. Run the cursor to a collection or an array, then walk through each item and attach an object or collection that has the values you want to persist.

+++ Rick ---



I have a case when my JSON result will be made up of simple properties and it needs to include an array of objects.

My cursor includes an empty memo field that I want to fill with a JSON array of objects. It looks something like the following

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

SCAN
REPLACE myArray with "[{val : 1, lbl : 'each'},{ val:2,lbl:'lbs'}]"
ENDSCAN

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("cursor_rawarray:tCursor")

Response.Write(lcJSON)


But my result in the browser includes an extra set of double quotes around the square brackets so it is NOT being interpreted as an Array but rather a strange string.

Is there a trick to doing this sort of thing?



Rick Strahl
West Wind Technologies

Making waves on the Web
from Maui

Gravatar is a globally recognized avatar based on your email address. Re: Nesting and Array Objects
  Harvey Mushman
  Rick Strahl
  Dec 18, 2014 @ 06:03am
Darn... that will be to slow to populate an AutoComplete control. I will have to get the result on the user selection instead. It means one more server hit. :-(

--hm

LOL!

Tables are flat structures and you're trying to turn them into hierarchical objects. I don't think you can do what you want with cursors. Strings will be JSON encoded - always - because cursors are FLAT!

You can do what you want with objects. Run the cursor to a collection or an array, then walk through each item and attach an object or collection that has the values you want to persist.

+++ Rick ---



I have a case when my JSON result will be made up of simple properties and it needs to include an array of objects.

My cursor includes an empty memo field that I want to fill with a JSON array of objects. It looks something like the following

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

SCAN
REPLACE myArray with "[{val : 1, lbl : 'each'},{ val:2,lbl:'lbs'}]"
ENDSCAN

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("cursor_rawarray:tCursor")

Response.Write(lcJSON)


But my result in the browser includes an extra set of double quotes around the square brackets so it is NOT being interpreted as an Array but rather a strange string.

Is there a trick to doing this sort of thing?




--hm--

Gravatar is a globally recognized avatar based on your email address. Re: Nesting and Array Objects
  Rick Strahl
  Harvey Mushman
  Dec 18, 2014 @ 11:20am
Why would you say that? Just make sure the autocomplete response is nit very large since autocomplete should be limited to a dozen or so entries anyway. Building an object in memory is fast. Even better run a single denomalized query to get all the data in one pass then build from that with a single scan loop.

Rick


Darn... that will be to slow to populate an AutoComplete control. I will have to get the result on the user selection instead. It means one more server hit. :-(

--hm

LOL!

Tables are flat structures and you're trying to turn them into hierarchical objects. I don't think you can do what you want with cursors. Strings will be JSON encoded - always - because cursors are FLAT!

You can do what you want with objects. Run the cursor to a collection or an array, then walk through each item and attach an object or collection that has the values you want to persist.

+++ Rick ---



I have a case when my JSON result will be made up of simple properties and it needs to include an array of objects.

My cursor includes an empty memo field that I want to fill with a JSON array of objects. It looks something like the following

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

SCAN
REPLACE myArray with "[{val : 1, lbl : 'each'},{ val:2,lbl:'lbs'}]"
ENDSCAN

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("cursor_rawarray:tCursor")

Response.Write(lcJSON)


But my result in the browser includes an extra set of double quotes around the square brackets so it is NOT being interpreted as an Array but rather a strange string.

Is there a trick to doing this sort of thing?





Rick Strahl
West Wind Technologies

Making waves on the Web
from Hood River

Gravatar is a globally recognized avatar based on your email address. Re: Nesting and Array Objects
  Harvey Mushman
  Rick Strahl
  Dec 18, 2014 @ 12:25pm
...can you define "denomalized query" ?

--hm


Why would you say that? Just make sure the autocomplete response is nit very large since autocomplete should be limited to a dozen or so entries anyway. Building an object in memory is fast. Even better run a single denomalized query to get all the data in one pass then build from that with a single scan loop.

Rick


Darn... that will be to slow to populate an AutoComplete control. I will have to get the result on the user selection instead. It means one more server hit. :-(

--hm

LOL!

Tables are flat structures and you're trying to turn them into hierarchical objects. I don't think you can do what you want with cursors. Strings will be JSON encoded - always - because cursors are FLAT!

You can do what you want with objects. Run the cursor to a collection or an array, then walk through each item and attach an object or collection that has the values you want to persist.

+++ Rick ---



I have a case when my JSON result will be made up of simple properties and it needs to include an array of objects.

My cursor includes an empty memo field that I want to fill with a JSON array of objects. It looks something like the following

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

SCAN
REPLACE myArray with "[{val : 1, lbl : 'each'},{ val:2,lbl:'lbs'}]"
ENDSCAN

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("cursor_rawarray:tCursor")

Response.Write(lcJSON)


But my result in the browser includes an extra set of double quotes around the square brackets so it is NOT being interpreted as an Array but rather a strange string.

Is there a trick to doing this sort of thing?






--hm--

Gravatar is a globally recognized avatar based on your email address. Re: Nesting and Array Objects
  Rick Strahl
  Rick Strahl
  Dec 18, 2014 @ 12:38pm
What's wrong with:

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

lnCount = _Tally

loColl = CursorToCollection("TCursor")
FOR lnX = 1 to lnCount
loItem = loColl[lnX]

loChildList = CREATEOBJECT("Collection")

loChild = CREATEOBJECT("EMPTY")
APPENDPROPERTY(loChild,"val",1)
APPENDPROPERTY(loChild,"lbl",'each')
loChildList.Add(loChild)

loChild = CREATEOBJECT("EMPTY")
APPENDPROPERTY(loChild,"val",2)
APPENDPROPERTY(loChild,"lbl",'lbs')
loChildList.Add(loChild)

ADDPROPERTY(loColl,"Items",loChildList)
ENDFOR

If the child list is dynamic you can run another query, or have the outer scan loop run over a denormalized list. Creating a collection and objects this way is fast as long as you're not creating thousands of them at a time.

And if you want to make it faster, precreate the objects...

+++ Rick ---

LOL!

Tables are flat structures and you're trying to turn them into hierarchical objects. I don't think you can do what you want with cursors. Strings will be JSON encoded - always - because cursors are FLAT!

You can do what you want with objects. Run the cursor to a collection or an array, then walk through each item and attach an object or collection that has the values you want to persist.

+++ Rick ---



I have a case when my JSON result will be made up of simple properties and it needs to include an array of objects.

My cursor includes an empty memo field that I want to fill with a JSON array of objects. It looks something like the following

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

SCAN
REPLACE myArray with "[{val : 1, lbl : 'each'},{ val:2,lbl:'lbs'}]"
ENDSCAN

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("cursor_rawarray:tCursor")

Response.Write(lcJSON)


But my result in the browser includes an extra set of double quotes around the square brackets so it is NOT being interpreted as an Array but rather a strange string.

Is there a trick to doing this sort of thing?





Rick Strahl
West Wind Technologies

Making waves on the Web
from Maui

Gravatar is a globally recognized avatar based on your email address. Re: Nesting and Array Objects
  Rick Strahl
  Harvey Mushman
  Dec 18, 2014 @ 12:43pm

Query that contains both parent and child items. Essentially an inner join query:

select customers.*, order.orderId, order.orderDate, order.orderTotal ;
from customers, orders ;
where orders.custId == customers.id

You'll get records that hold all orders that also include all the customer information. The customer information is duplicated many times for each order.

You can use that in a SCAN loop to add new customers when the customer changes, and then add the detail. This allows you to run 1 query and use a single SCAN loop to create your object. Much more efficient than retrieving customers, then running many more queries to get the orders for each although for small enough number of records that's still pretty quick, especially if you're talking bout local FoxPro data. Not so much with a remote SQL Server though.

+++ Rick ---



...can you define "denomalized query" ?

--hm


Why would you say that? Just make sure the autocomplete response is nit very large since autocomplete should be limited to a dozen or so entries anyway. Building an object in memory is fast. Even better run a single denomalized query to get all the data in one pass then build from that with a single scan loop.

Rick


Darn... that will be to slow to populate an AutoComplete control. I will have to get the result on the user selection instead. It means one more server hit. :-(

--hm

LOL!

Tables are flat structures and you're trying to turn them into hierarchical objects. I don't think you can do what you want with cursors. Strings will be JSON encoded - always - because cursors are FLAT!

You can do what you want with objects. Run the cursor to a collection or an array, then walk through each item and attach an object or collection that has the values you want to persist.

+++ Rick ---



I have a case when my JSON result will be made up of simple properties and it needs to include an array of objects.

My cursor includes an empty memo field that I want to fill with a JSON array of objects. It looks something like the following

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

SCAN
REPLACE myArray with "[{val : 1, lbl : 'each'},{ val:2,lbl:'lbs'}]"
ENDSCAN

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("cursor_rawarray:tCursor")

Response.Write(lcJSON)


But my result in the browser includes an extra set of double quotes around the square brackets so it is NOT being interpreted as an Array but rather a strange string.

Is there a trick to doing this sort of thing?








Rick Strahl
West Wind Technologies

Making waves on the Web
from Maui

Gravatar is a globally recognized avatar based on your email address. Re: Nesting and Array Objects
  Harvey Mushman
  Rick Strahl
  Dec 18, 2014 @ 04:23pm
Thanks, that is an interesting concept. At this point I followed the idea of creating objects in containers. The hit times are down around 0.08 sec. for about 20 items. And that is on my (old) laptop <g> which should only improve in production. So I think I'm good for now but will keep the technique below in mind for the next time.

Thanks again.

--hm

Query that contains both parent and child items. Essentially an inner join query:

select customers.*, order.orderId, order.orderDate, order.orderTotal ;
from customers, orders ;
where orders.custId == customers.id

You'll get records that hold all orders that also include all the customer information. The customer information is duplicated many times for each order.

You can use that in a SCAN loop to add new customers when the customer changes, and then add the detail. This allows you to run 1 query and use a single SCAN loop to create your object. Much more efficient than retrieving customers, then running many more queries to get the orders for each although for small enough number of records that's still pretty quick, especially if you're talking bout local FoxPro data. Not so much with a remote SQL Server though.

+++ Rick ---



...can you define "denomalized query" ?

--hm


Why would you say that? Just make sure the autocomplete response is nit very large since autocomplete should be limited to a dozen or so entries anyway. Building an object in memory is fast. Even better run a single denomalized query to get all the data in one pass then build from that with a single scan loop.

Rick


Darn... that will be to slow to populate an AutoComplete control. I will have to get the result on the user selection instead. It means one more server hit. :-(

--hm

LOL!

Tables are flat structures and you're trying to turn them into hierarchical objects. I don't think you can do what you want with cursors. Strings will be JSON encoded - always - because cursors are FLAT!

You can do what you want with objects. Run the cursor to a collection or an array, then walk through each item and attach an object or collection that has the values you want to persist.

+++ Rick ---



I have a case when my JSON result will be made up of simple properties and it needs to include an array of objects.

My cursor includes an empty memo field that I want to fill with a JSON array of objects. It looks something like the following

SELECT *, cast('' as M) as myArray from myTable INTO CURSOR tCursor

SCAN
REPLACE myArray with "[{val : 1, lbl : 'each'},{ val:2,lbl:'lbs'}]"
ENDSCAN

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("cursor_rawarray:tCursor")

Response.Write(lcJSON)


But my result in the browser includes an extra set of double quotes around the square brackets so it is NOT being interpreted as an Array but rather a strange string.

Is there a trick to doing this sort of thing?









--hm--

© 1996-2024