Question For Each {1} - Pass table name as argument

[philippoommen1]

Hi ,

Can I do something like below

For each {1}:
Disp
{1}.Chg-date
{1}.Chg-time.
end.

I should be able to pass the table name as argument to this procedure. Chg-date and Chg-time are fields common across more than 100 tables.

TIA
Philip

 

[Cringer]

Don't do that. Please. Even if you get it working it's terrible! I'm not even sure it's possible. You're better off using a dynamic query.

 

[Cecil]

I was about to say: "Don't ask question like that. Somebody will have your guts for garters". You might have opened a hornets nest.
All jokes aside as Cringer said use a Dynamic Query. Unless you are using version 8.1C and that would be another onslaught forum drama.

 

[RealHeavyDude]

You need to be aware that your desing is resolved at compile time. At compile time the placeholders will be replaced with the contents of the arguments. Therefore you would need to be able to compile the procedure on-the-fly in the runtime environment in order to be successful. Don't get me wrong, but I don't think that is a good thing. But this kinda stuff is still used in the data dictionary code by you guess who ...
That is one of the reasons as to why you need a developer license to use the data dictionary ...

Heavy Regards, RealHeavyDude.

 

[philippoommen1]

Thank a lot for the replies.

 

[GregTomkins]

Put that code in a .i file, say foo.i, then in some other file, say foo.p, do this:

{foo.i orders}
{foo.i customers}

Not a great idea for the reasons mentioned, but, an order of magnitude simpler than a dynamic query, OK if you're just hacking off something in a hurry. Do it under someone else's ID

 

[TomBascom]

Dynamic queries aren't *that* hard...

/* dyn.p
 */

define variable tbl as character no-undo.
define variable b  as handle no-undo.
define variable q  as handle no-undo.

tbl = "customer".
create query  q.
create buffer b for table tbl.

q:set-buffers( b ).

q:query-prepare( "for each " + tbl ).
q:query-open.
q:get-next().

do while q:query-off-end = false:

  display
  b:buffer-field( 'CustNum' ):buffer-value()
  b:buffer-field( 'Name'  ):buffer-value()
  b:buffer-field( 'State'  ):buffer-value()
  with
  down
  .

  down 1.

  q:get-next().

end.

return.

 

[Vincent Bourbeau]

How do you update a value in this case?

Dynamic queries aren't *that* hard...

/* dyn.p
*/
 
define variable tbl as character no-undo.
define variable b  as handle no-undo.
define variable q  as handle no-undo.
 
tbl = "customer".
create query  q.
create buffer b for table tbl.
 
q:set-buffers( b ).
 
q:query-prepare( "for each " + tbl ).
q:query-open.
q:get-next().
 
do while q:query-off-end = false:
 
  display
  b:buffer-field( 'CustNum' ):buffer-value()
  b:buffer-field( 'Name'  ):buffer-value()
  b:buffer-field( 'State'  ):buffer-value()
  with
  down
  .
 
  down 1.
 
  q:get-next().
 
end.
 
return.

 

[GregTomkins]

Just use a normal assignment, as in: b:buffer-field('CustNum'):buffer-value() = "FOO".

Beware: while dynamic queries are definitely a good thing, one of their drawbacks (besides explosive verbosity) is lack of compile time checking (hence, 'dynamic'). In other words, if you mistype 'CustNum' as 'CustNumb', you won't find out about it until your about-to-be-angry user runs that line of code. Whereas, with static queries, you found out at compile time.

 

[Vincent Bourbeau]

Just use a normal assignment, as in: b:buffer-field('CustNum'):buffer-value() = "FOO".

Beware: while dynamic queries are definitely a good thing, one of their drawbacks (besides explosive verbosity) is lack of compile time checking (hence, 'dynamic'). In other words, if you mistype 'CustNum' as 'CustNumb', you won't find out about it until your about-to-be-angry user runs that line of code. Whereas, with static queries, you found out at compile time.

Is exist a static way to past a table name as a variable?

 

[Cringer]

Not without doing what the OP was asking.

 

[GregTomkins]

Tom's example uses a table name in a variable ('tbl'), but it's dynamic. To do the same type of thing statically, yeah, you have to do some kind of old-school include-parameter thing as in the OP's example.

BTW, I hesitate greatly to impugn RHD's response in any way, but we do this all over the place (it's old code! From the late 1990's! I swear!) with a regular runtime-only licence. This is OK as long as the file containing the include references is precompiled, encrypted, etc. etc.

 

[Vincent Bourbeau]

Is exist a static way to past a table name as a variable?

Just use a normal assignment, as in: b:buffer-field('CustNum'):buffer-value() = "FOO".

Beware: while dynamic queries are definitely a good thing, one of their drawbacks (besides explosive verbosity) is lack of compile time checking (hence, 'dynamic'). In other words, if you mistype 'CustNum' as 'CustNumb', you won't find out about it until your about-to-be-angry user runs that line of code. Whereas, with static queries, you found out at compile time.

I try b:buffer-field('CustNum'):buffer-value() = "FOO". but I've always got a NO-LOCK error ...?

 

[TomBascom]

You might want to try getting the record with an exclusive-lock...

GET-NEXT( ) method

Moves a query object’s result list pointer ahead one row.

Return type:

LOGICAL
Applies to:

Query object handle

Syntax
GET-NEXT ( NO-LOCK | SHARE-LOCK [ , NO-WAIT ]
| EXCLUSIVE-LOCK [ , NO-WAIT ] )

NO-LOCK
Specifies that no lock is applied to the record. This applies to all buffers in a join. Unless explicitly specified otherwise, this is the default lock type for this method.

SHARE-LOCK
Specifies that the record is share locked. This applies to all buffers in a join.

EXCLUSIVE-LOCK
Specifies that the record is exclusively locked. This applies to all buffers in a join.

NO-WAIT
Specifies that the method returns immediately if the record cannot be accessed because it is locked by another user. If you do not use the NO-WAIT option, the method waits until the record can be accessed. This applies to all buffers in a join. If you specify NO-WAIT and the record is locked by another user, the record is returned to you with NO-LOCK and the LOCKED function returns TRUE for the record.
GET-NEXT() returns TRUE if the next record in the query is found. If the query is not open or the next record cannot be found (query is empty or the query result list pointer is on the last row), the method returns FALSE. If the query object handle is invalid, the method returns the Unknown value (?).

 

[Vincent Bourbeau]

You might want to try getting the record with an exclusive-lock...

GET-NEXT( ) method
 
Moves a query object’s result list pointer ahead one row.
 
Return type:
 
LOGICAL
Applies to:
 
Query object handle
 
Syntax
GET-NEXT ( NO-LOCK | SHARE-LOCK [ , NO-WAIT ]
| EXCLUSIVE-LOCK [ , NO-WAIT ] )
 
NO-LOCK
Specifies that no lock is applied to the record. This applies to all buffers in a join. Unless explicitly specified otherwise, this is the default lock type for this method.
 
SHARE-LOCK
Specifies that the record is share locked. This applies to all buffers in a join.
 
EXCLUSIVE-LOCK
Specifies that the record is exclusively locked. This applies to all buffers in a join.
 
NO-WAIT
Specifies that the method returns immediately if the record cannot be accessed because it is locked by another user. If you do not use the NO-WAIT option, the method waits until the record can be accessed. This applies to all buffers in a join. If you specify NO-WAIT and the record is locked by another user, the record is returned to you with NO-LOCK and the LOCKED function returns TRUE for the record.
GET-NEXT() returns TRUE if the next record in the query is found. If the query is not open or the next record cannot be found (query is empty or the query result list pointer is on the last row), the method returns FALSE. If the query object handle is invalid, the method returns the Unknown value (?).

Thank you it's work...

I need your opinion on my project. It's in Syteline ERP.

"Item" field is a primary key in the system. This field is on hundred table. So we can't change item name as soon as a transaction is made.

My program does this:

- Search all table that have the field "item"
- search in all these table each record that have some item value.
- Change value for new one.

So wherever ther is an item value is changed to a new one.

my program works ... but I'm not sure if it is a good idea? What is your opinion?

 

[TomBascom]

I don't know much about Syteline internals.

In general that isn't how I would design a system in the first place -- you should be using a "surrogate key" rather than something that anyone would ever want to change.

I would also question the need to change a field like you describe. It only takes one or two tables where someone decided to call it "itm" instead of "item" or a few tables having the same field name used for a different purpose to make the whole approach go up in flames.

Even if it works I would hope that that sort of thing would be an extremely rare event that you would do only under the utmost dire circumstances.

 

[Vincent Bourbeau]

I don't know much about Syteline internals.

In general that isn't how I would design a system in the first place -- you should be using a "surrogate key" rather than something that anyone would ever want to change.

I would also question the need to change a field like you describe. It only takes one or two tables where someone decided to call it "itm" instead of "item" or a few tables having the same field name used for a different purpose to make the whole approach go up in flames.

Even if it works I would hope that that sort of thing would be an extremely rare event that you would do only under the utmost dire circumstances.

Yes the system don't use surrogate key. Each link is done by the item name. And of course if I want to change something in the name I can't I need to create a copy of the item and I loose all the history.

I don't espect use that often but if someone already do something like this for syteline it'll be helpful.

 

[Rob Fitzpatrick]

Yes the system don't use surrogate key. Each link is done by the item name. And of course if I want to change something in the name I can't I need to create a copy of the item and I loose all the history.

I don't espect use that often but if someone already do something like this for syteline it'll be helpful.

Do you not have vendor support?

 

[Vincent Bourbeau]

Do you not have vendor support?

Not anymore, we have old syteline 5 version.