Object Arrays - Very Interesting "Watchas"

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Object Arrays - Very Interesting "Watchas"

Peter Jakobsson-2
Hi

Apologies if this has been discussed before - I haven’t kept up with all threads on the topic but noticed this interesting behaviour just now. I thought it was a bug at first but now I see it’s a feature, albeit a slightly un-intuitive one based on what we’re used to.

If I do this……..

=================== CODE BLOCK 1 ====================

ARRAY TEXT($arrayTEXT;0)

$myName:=“Ian”
APPEND TO ARRAY($arrayTEXT;$myName)

$myName:=“Sandra”
APPEND TO ARRAY($arrayTEXT;$myName)

$myName:=“george”
APPEND TO ARRAY($arrayTEXT;$myName)

====================================================

….then each array element will contain a distinct value. i.e. {1}=“Ian"  {2}=“Sandra” {3}=“George”


On the other hand, if I do this…
=================== CODE BLOCK 2 ====================

ARRAY OBJECT($arrOBJECTS;0)

$myObject=JSON Parse("{}”) (Create an empty object)

OB SET($myObject ;"myName”;”Ian”)
APPEND TO ARRAY($arrOBJECTS;$myObject)

OB SET($myObject ;"myName”;”Sandra”)
APPEND TO ARRAY($arrOBJECTS;$myObject)

OB SET($myObject ;"myName”;”George”)
APPEND TO ARRAY($arrOBJECTS;$myObject)
====================================================

…then simply assigning $myObject assigns EVERY value in the array. i.e. my array now contains [1]=“George” [2]=“George” [3]=“George”

So in this respect, objects are more like pointers than Blobs because you’re just passing around a reference to a single memory space.

However, what’s even weirder is if we now change $arrOBJECTS to a process array but leave $myObject as a local and allow it to go out of scope. Can anybody predict what will happen now ? I couldn’t - just had to try it :-)

In fact what occurs is this (calls made in the parent procedure once CODE BLOCK 2 drops back into caller):

=================== CODE BLOCK 3 ====================

OB SET(arrOBJECTS{1};"myName";"Mickey”) `     ALL elements now contain  [1]=“ Mickey” [2]=“ Mickey” [3]=“ Mickey”
OB SET(arrOBJECTS{2};"myName";"Goofy") `     ALL elements now contain  [1]=“ Goofy” [2]=“ Goofy” [3]=“ Goofy”
OB SET(arrOBJECTS{3};"myName";"Pluto") `     ALL elements now contain  [1]=“ Pluto” [2]=“ Pluto” [3]=“ Pluto”
====================================================

So by assigning a key-value in one element of the object array, I’m potentially assigning them all. I have to know myself whether they all reference the same object or distinct objects. Further, even though my original local went out of scope, the actual object “space” that it was referencing hasn’t. 4D seems to realise that the object space is referenced by a variable somewhere which remains in scope after the instantiating method has finished executing.

This is very clever of 4D IMO. There must be some memory management gymnastics going on under the hood to make this work so consistently with other variable types. Note the difference with pointer arrays - when a process level pointer array contains a pointer to a local variable that’s out of scope, the debugger reports its name ok but its value is “UNDEFINED”. On the other hand, when an process level object array contains an object reference that was originally held in a local variable thats now dropped out of scope, the object array CONTINUES to hold a reference to the underlying data and you can continue to do OB Get on it.

The question is, what does 4D do to object spaces that are only referenced by locals ? I imagine that to avoid memory leaks, it must keep track of all references and delete the object space when all references have dropped out of scope. Conversely it seems to imply that if we reference an object with a process variable then the object space will never be deleted until 4D quits = another “Watcha” !

Hope this is of interest to other observers and sorry for the “DUH” factor if you already understood this as implicitly as pouring a cup of tea ! :)

Regards

Peter

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Object Arrays - Very Interesting "Watchas"

Jeremy French
HI Peter,

Have you tried using OB Copy?

That is, you would do:

APPEND TO ARRAY($arrOBJECTS;OB Copy($myObject))

Using OB Copy will retain the object value at time of assignment to the array element.

More details at:
 http://doc.4d.com/4Dv15R5/4D/15-R5/OB-Copy.301-2936604.en.html

- Jeremy French


> On Oct 29, 2016, at 7:28 AM, Peter Jakobsson <[hidden email]> wrote:
>
> If I do this……..
>
> =================== CODE BLOCK 1 ====================
>
> ARRAY TEXT($arrayTEXT;0)
>
> $myName:=“Ian”
> APPEND TO ARRAY($arrayTEXT;$myName)
>
> $myName:=“Sandra”
> APPEND TO ARRAY($arrayTEXT;$myName)
>
> $myName:=“george”
> APPEND TO ARRAY($arrayTEXT;$myName)
>
> ====================================================
>
> ….then each array element will contain a distinct value. i.e. {1}=“Ian"  {2}=“Sandra” {3}=“George”
>
>
> On the other hand, if I do this…
> =================== CODE BLOCK 2 ====================
>
> ARRAY OBJECT($arrOBJECTS;0)
>
> $myObject=JSON Parse("{}”) (Create an empty object)
>
> OB SET($myObject ;"myName”;”Ian”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
>
> OB SET($myObject ;"myName”;”Sandra”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
>
> OB SET($myObject ;"myName”;”George”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
> ====================================================
>
> …then simply assigning $myObject assigns EVERY value in the array. i.e. my array now contains [1]=“George” [2]=“George” [3]=“George”

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Object Arrays - Very Interesting "Watchas"

Peter Jakobsson-2
Hi Jeremy

Thats a nice way of doing it. I’ll use that in future in this scenario I think.

Thanks !

Peter


On 29 Oct 2016, at 16:45, Jeremy French <[hidden email]> wrote:

> Have you tried using OB Copy?
>
> That is, you would do:
>
> APPEND TO ARRAY($arrOBJECTS;OB Copy($myObject))

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Object Arrays - Very Interesting "Watchas"

Peter Bozek-2
In reply to this post by Peter Jakobsson-2
On Sat, Oct 29, 2016 at 1:28 PM, Peter Jakobsson <[hidden email]>
wrote:
>
> The question is, what does 4D do to object spaces that are only
referenced by locals ? I imagine that to avoid memory leaks, it must keep
track of all references and delete the object space when all references
have dropped out of scope. Conversely it seems to imply that if we
reference an object with a process variable then the object space will
never be deleted until 4D quits = another “Watcha” !


I assume objects - it means, object variables - are just a references to
some internal storage that contain name-value pairs. The storage is
ref-counted, what means it tracks how many references point to it.

I prefer a 'reference' to 'pointer' as under pointer I imagine a single
number that does not know anything about what it points to.

In your example  'CODE BLOCK 2', $myObject is reference to some storage
with one reference ($myObject.)
APPEND TO ARRAY($arrOBJECTS;$myObject) does not create another storage, it
just increases a reference count - both myObject and $arrOBJECTS[1] points
to it. At the end, you still have only one storage containing value pair
"myName'="George", referenced by four (local) variables (myObject and three
elements of $arrOBJECTS.)

YEs, 4D need to handle destruction of local variables at the end of method
and destruction of process variables at the termination of process, but
with current OO programming languages this is not a big deal.

Situation will fast get more interesting - imagine following code

ARRAY OBJECT($arrOBJECTS;0)
C_OBJECT($myObject)
APPEND TO ARRAY($arrOBJECTS;$myObject)
APPEND TO ARRAY($arrOBJECTS;$myObject)
OB SET(arrOBJECTS{1};"myName";"Mickey”) `
OB SET(arrOBJECTS{2};"myName";"Goofy")  //  [1]=“ Mickey” [2]=“ Goofy”

but

ARRAY OBJECT($arrOBJECTS;0)
C_OBJECT($myObject)
$object=JSON Parse("{}”) // init object
APPEND TO ARRAY($arrOBJECTS;$myObject)
APPEND TO ARRAY($arrOBJECTS;$myObject)
OB SET(arrOBJECTS{1};"myName";"Mickey”) `
OB SET(arrOBJECTS{2};"myName";"Goofy")  //  [1]=“ Goofy” [2]=“ Goofy”

Object can contain other object, what seems to mean in 4D that object (its
storage) contain reference to another object (another piece in the storage.)

C_OBJECT($object1;$object2;$object3)
$object1:=JSON Parse("{}") // init object"
$object2:=JSON Parse("{}") // init object"

OB SET($object1;"object";$object2)
$object3:=OB Copy($object1)

Now $object3 contain a copy of $object1, does its "object" property contain
a reference to the same object ($object2) or did 4D copied an included
object as well - i.e. are object properties of $object1 and $object3
referring the same or different storage? It can be tested with

OB SET($object2;"name";"George")

and it seems it is the later case..

You can do circular references:
C_OBJECT($object1;$object2)
$object1:=JSON Parse("{}”) // init object
$object2:=JSON Parse("{}”) // init object

OB SET($object1;"object"; $object2)
OB SET($object2;"object"; $object1)

Now $object1 and $object2 create a circular reference (and debugger will
show it cannot display such object), they may not be cleared from memory
when method ends (will not be cleared ever.)

Now, you may decide to disconnect the loop by calling

OB SET($object2;"object";JSON Parse("{}"))

You would say that should reset the loop - $object1 will still contain
reference to $object2, but $object2 will not contain reference to $object1.
Alas, not. Try it.

So you can have a lot of fun with objects!

--


Peter Bozek
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Object Arrays - Very Interesting "Watchas"

Peter Jakobsson-2

On 29 Oct 2016, at 19:41, Peter Bozek <[hidden email]> wrote:

> Now $object1 and $object2 create a circular reference (and debugger will
> show it cannot display such object), they may not be cleared from memory
> when method ends (will not be cleared ever.)

Hi Peter

Thats an interesting one. Will need to explore it !

Thanks for your examples.

Peter

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Object Arrays - Very Interesting "Watchas"

Bernard Escaich-3
In reply to this post by Peter Jakobsson-2
Hi Peter,

Cannon Smith has written a component OBJ_Module, free for the community.
It's amazingly useful !

I have selected this from read me that explains the behavior you have seen

  //--------------------------------------
  //  Reference Counting vs Copying
  //--------------------------------------

  //Native 4D objects are reference counted. This is a good thing because it increases speed and saves on memory for
  //typical operations. But it can also be a bit confusing if you're not used to it. With ref counting, when an object
  //is assigned to another object (including when being passed as a parameter), only the reference is passed. Think of
  //it like a pointer. For example, if you have an object $oObject, you might do this: $oDuplicate:=$oObject. If it was
  //like most other 4D types we are used to, there would now be two objects and if we made a change to $oObject it would
  //not affect $oDuplicate. But this is not the case with objects. $oObject and $oDuplicate both point to the same actual
  //object in memory. Making a change to $oObject effectively changes $oDuplicate as well. Often you want to use objects
  //this way (ref counting), but sometimes you want an actual copy and 4D provides a way to do this with OB Copy.

  //This module attempts to make this a bit more natural by doing the most likely thing by default, but providing a way
  //to do the other thing as an option for the methods where this makes sense. For example, OBJ_Set_Object and
  //OBJ_Get_Object both have an option to set/get the object by reference or by copying.

You can download here http://files.synergyfarmsolutions.com/4DNug/OBJ_Module.zip <http://files.synergyfarmsolutions.com/4DNug/OBJ_Module.zip>
Cherry on the cake, you can use dot notation.

0,02 €,

Bernard Escaich


> Le 29 oct. 2016 à 13:28, Peter Jakobsson <[hidden email]> a écrit :
>
> Hi
>
> Apologies if this has been discussed before - I haven’t kept up with all threads on the topic but noticed this interesting behaviour just now. I thought it was a bug at first but now I see it’s a feature, albeit a slightly un-intuitive one based on what we’re used to.
>
> If I do this……..
>
> =================== CODE BLOCK 1 ====================
>
> ARRAY TEXT($arrayTEXT;0)
>
> $myName:=“Ian”
> APPEND TO ARRAY($arrayTEXT;$myName)
>
> $myName:=“Sandra”
> APPEND TO ARRAY($arrayTEXT;$myName)
>
> $myName:=“george”
> APPEND TO ARRAY($arrayTEXT;$myName)
>
> ====================================================
>
> ….then each array element will contain a distinct value. i.e. {1}=“Ian"  {2}=“Sandra” {3}=“George”
>
>
> On the other hand, if I do this…
> =================== CODE BLOCK 2 ====================
>
> ARRAY OBJECT($arrOBJECTS;0)
>
> $myObject=JSON Parse("{}”) (Create an empty object)
>
> OB SET($myObject ;"myName”;”Ian”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
>
> OB SET($myObject ;"myName”;”Sandra”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
>
> OB SET($myObject ;"myName”;”George”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
> ====================================================
>
> …then simply assigning $myObject assigns EVERY value in the array. i.e. my array now contains [1]=“George” [2]=“George” [3]=“George”
>
> So in this respect, objects are more like pointers than Blobs because you’re just passing around a reference to a single memory space.
>
> However, what’s even weirder is if we now change $arrOBJECTS to a process array but leave $myObject as a local and allow it to go out of scope. Can anybody predict what will happen now ? I couldn’t - just had to try it :-)
>
> In fact what occurs is this (calls made in the parent procedure once CODE BLOCK 2 drops back into caller):
>
> =================== CODE BLOCK 3 ====================
>
> OB SET(arrOBJECTS{1};"myName";"Mickey”) `     ALL elements now contain  [1]=“ Mickey” [2]=“ Mickey” [3]=“ Mickey”
> OB SET(arrOBJECTS{2};"myName";"Goofy") `     ALL elements now contain  [1]=“ Goofy” [2]=“ Goofy” [3]=“ Goofy”
> OB SET(arrOBJECTS{3};"myName";"Pluto") `     ALL elements now contain  [1]=“ Pluto” [2]=“ Pluto” [3]=“ Pluto”
> ====================================================
>
> So by assigning a key-value in one element of the object array, I’m potentially assigning them all. I have to know myself whether they all reference the same object or distinct objects. Further, even though my original local went out of scope, the actual object “space” that it was referencing hasn’t. 4D seems to realise that the object space is referenced by a variable somewhere which remains in scope after the instantiating method has finished executing.
>
> This is very clever of 4D IMO. There must be some memory management gymnastics going on under the hood to make this work so consistently with other variable types. Note the difference with pointer arrays - when a process level pointer array contains a pointer to a local variable that’s out of scope, the debugger reports its name ok but its value is “UNDEFINED”. On the other hand, when an process level object array contains an object reference that was originally held in a local variable thats now dropped out of scope, the object array CONTINUES to hold a reference to the underlying data and you can continue to do OB Get on it.
>
> The question is, what does 4D do to object spaces that are only referenced by locals ? I imagine that to avoid memory leaks, it must keep track of all references and delete the object space when all references have dropped out of scope. Conversely it seems to imply that if we reference an object with a process variable then the object space will never be deleted until 4D quits = another “Watcha” !
>
> Hope this is of interest to other observers and sorry for the “DUH” factor if you already understood this as implicitly as pouring a cup of tea ! :)
>
> Regards
>
> Peter
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Object Arrays - Very Interesting "Watchas"

Peter Jakobsson-2
Hi Bernard

Yes indeed - I saw his component last year and thought it was an excellent piece of work. Good description of the behaviours also.

Peter

On 29 Oct 2016, at 23:31, Bernard Escaich <[hidden email]> wrote:

> Cannon Smith has written a component OBJ_Module, free for the community.
> It's amazingly useful !

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Object Arrays - Very Interesting "Watchas"

KirkBrooks
In reply to this post by Peter Jakobsson-2
Peter,


On Sat, Oct 29, 2016 at 4:28 AM, Peter Jakobsson <[hidden email]>
wrote:

> On the other hand, if I do this…
> =================== CODE BLOCK 2 ====================
>
> ARRAY OBJECT($arrOBJECTS;0)
>
> $myObject=JSON Parse("{}”) (Create an empty object)
>
> OB SET($myObject ;"myName”;”Ian”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
>
> OB SET($myObject ;"myName”;”Sandra”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
>
> OB SET($myObject ;"myName”;”George”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
> ====================================================
>
> …then simply assigning $myObject assigns EVERY value in the array. i.e. my
> array now contains [1]=“George” [2]=“George” [3]=“George”
>
JSON Parse("{}”)
​ creates and empty object and also clears the previous object. ​so:

ARRAY OBJECT($arrOBJECTS;0)

$myObject=JSON Parse("{}”)
OB SET($myObject ;"myName”;”Ian”)
APPEND TO ARRAY($arrOBJECTS;$myObject)

$myObject=JSON Parse("{}”)
OB SET($myObject ;"myName”;”Sandra”)
APPEND TO ARRAY($arrOBJECTS;$myObject)

​will behave as we would usually expect.

​There's another aspect of this behavior too. It's touched on obliquely in
the SELECTION TO JSON documentation.

Consider these two commands:

$obj=JSON Parse("{}”) ​

​OB SET($obj;\
  "fieldOne";->[table]field1;\​
  "fieldTwo";->[table]field2;\​
  "fieldThree";->[table]field3)​

​and

$obj=JSON Parse("{}”) ​

​OB SET($obj;\
  "fieldOne";[table]field1;\​
  "fieldTwo";[table]field2;\​
  "fieldThree";[table]field3)​​


​Both of these could be used to create a 'base class' or 'template' of
[table]. If there is a current record when it's called $obj will obviously
have the values of the record. It there is no current record $obj has the
keys and empty or zero values (but not null).

That's pretty useful right there. But the pointers make a big difference.
OB COPY has the option to translate pointers. So if you call $obj2:=OB
COPY($obj;true) $obj2 receives the ​values of the current record of table.
This can be coupled with non-pointer keys as well. For example:

$obj=JSON Parse("{}”) ​

​OB SET($obj;\
  "fieldOne";->[table]field1;\​
  "fieldTwo";->[table]field2;\​
  "fieldThree";->[table]field3);\
  "counter";selected record number([table]);\
  "static";"This is constant")

​Now you can use $obj as a template for SELECTION TO JSON:

$jsonString :=Selection to JSON([table];$$obj)

json parse array($jsonSTring;$aObjArray)


 or in your own loop like so:

For($i;1;10)

append to array($aObjArray;OB COPY($obj;true))

next record([table])

end for​

​and each element of $aObjArray will get the current values of [table] and
selected record number. Each element will have the same static value. ​

​But wait - there's more.

Since we put pointers into the object to start with we can get them back
too.

$fieldOnePtr:=ob get($obj;"fieldOne";is pointer)​


​Obviously this only works when the object is defined by 4D with a pointer
since there's no equivalent anywhere else. ​

This really makes these sort of "template" objects really useful.

--
Kirk Brooks
San Francisco, CA
=======================
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Object Arrays - Very Interesting "Watchas"

Lee Hinde
In reply to this post by Peter Jakobsson-2
I haven’t read all the responses, but this is how I’d write this method:


ARRAY OBJECT($arrOBJECTS;0)
C_OBJECT($myObject)  // <— New

OB SET($myObject;"myName";"Ian")
APPEND TO ARRAY($arrOBJECTS;$myObject)
CLEAR VARIABLE($myObject)  // <— New

OB SET($myObject;"myName";"Sandra")
APPEND TO ARRAY($arrOBJECTS;$myObject)
CLEAR VARIABLE($myObject)  // <— New

OB SET($myObject;"myName";"George")
APPEND TO ARRAY($arrOBJECTS;$myObject)
CLEAR VARIABLE($myObject)  // <— New



> On Oct 29, 2016, at 4:28 AM, Peter Jakobsson <[hidden email]> wrote:
>
> On the other hand, if I do this…
> =================== CODE BLOCK 2 ====================
>
> ARRAY OBJECT($arrOBJECTS;0)
>
> $myObject=JSON Parse("{}”) (Create an empty object)
>
> OB SET($myObject ;"myName”;”Ian”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
>
> OB SET($myObject ;"myName”;”Sandra”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
>
> OB SET($myObject ;"myName”;”George”)
> APPEND TO ARRAY($arrOBJECTS;$myObject)
> ====================================================

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

v15, SQL query using "__ROW_ID" into "where"

Gianluca Rigotti
Hi all,
I try to locate a record using a SQL query knowing only the record number. Reading SQL manual I know about the virtual field that contain the record ID (that is the record number). So I wrote something like this:

Begin SQL
   select field123
   from table1
   where __ROW_ID = 10
   into :$myVar
End SQL

and works. The problem is that the virtual “ __ROW_ID” field seems to be mantained as "no indexed” field, and the query is sequential and really slow for very large tables.
So my question is: there is a way to fast locate a record using the record number into a SQL query?

TIA,
Gianluca

Gianluca Rigotti
----------------------------------
Via Rupi di via XXIX Settembre, 27
60125 Ancona (AN)
ITALY
----------------------------------
T: +39 071 9987145
M: +39 338 4550245, +39 335 1360105
mail: [hidden email], [hidden email]
skype: gianluca.rigotti

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: v15, SQL query using "__ROW_ID" into "where"

Charles Miller
On Tue, Nov 15, 2016 at 9:10 AM, Gianluca Rigotti <[hidden email]>
wrote:

> Hi all,
> I try to locate a record using a SQL query knowing only the record number.
> Reading SQL manual I know about the virtual field that contain the record
> ID (that is the record number). So I wrote something like this:
>
> Begin SQL
>    select field123
>    from table1
>    where __ROW_ID = 10
>    into :$myVar
> End SQL
>
> and works. The problem is that the virtual “ __ROW_ID” field seems to be
> mantained as "no indexed” field, and the query is sequential and really
> slow for very large tables.
> So my question is: there is a way to fast locate a record using the record
> number into a SQL query?
>

I would assume not as I do not think record number is indexed so selecting
by it using sql would be sequential

You could also try a fn call in sql. I would not recommend this however

this is one of those cases where you are better off using 4D so it would
look like this

goto record([table1];10)
this is instantaneous

Regards
Chuck


--
-----------------------------------------------------------------------------------------
 Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
 Informed Solutions, Inc.
 Brookline, MA 02446 USA Registered 4D Developer
       Providers of 4D, Sybase & SQL Sever connectivity
          http://www.informed-solutions.com
-----------------------------------------------------------------------------------------
This message and any attached documents contain information which may be
confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution, disclosure,
printing, copying, storage, modification or the taking of any action in
reliance upon this transmission is strictly prohibited.  Delivery of this
message to any person other than the intended recipient shall not
compromise or waive such confidentiality, privilege or exemption
from disclosure as to this communication.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: v15, SQL query using "__ROW_ID" into "where"

Keisuke Miyako
you are most certainly better off using 4D language, most of time.

haven't tried,
but maybe you could do some preprocessing using 4D language,
where you effectively convert records numbers to primary keys (which are always indexed)
and use SQL SELECT with that.

> 2016/11/16 6:34、Charles Miller <[hidden email]> のメール:
> this is one of those cases where you are better off using 4D



宮古 啓介
セールス・エンジニア

株式会社フォーディー・ジャパン
〒150-0043
東京都渋谷区道玄坂1-10-2 渋谷THビル6F
Tel: 03-6427-8441
Fax: 03-6427-8449

[hidden email]
www.4D.com/JP

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************