Question Accessing tables from multiple db

[Shubhrant]

Hi guys,
I have 3 dbs connected such as "abcd.db" "abcdu.db" and "utlcomu.db". these are the logical names and abcd.db contains all the standard QAD tables where as abcdu.db and utlcomu.db contains customized tables. Now in a query i am taking input as the table name to search the fields. By default it is working for abcd.db but for other dbs it is not working. It's searching for a db extension before the table name, but from the front end it is not possible for me to pass the DB extension... please refer the example.

define variable m_tbl as character no-undo.
define variable m_db as character no-undo.
update m_tbl m_db with frame a.
find first _file where _file-name = m_tbl no-lock no-error.
if avail _file then
  disp _file with 2 col.

this is correctly working for abcd.db related table.
but for the tables related to other 2 tables are searching for a db extension such as "abcdu.xx_tbl". which is not possible for me to pass through m_db to run the query.

if i write the query as :

define variable m_tbl as character no-undo.
define variable m_db as character no-undo.
update m_tbl m_db with frame a.
find first m_db._file where _file-name = m_tbl no-lock no-error.
if avail m_db._file then
  disp m_db._file with 2 col.

Then i am getting error as m_db is not a valid database name.

please help to resolve this.

appreciate your response on this.

 

[Cringer]

You need to create a dynamic query for this.

 

[Rob Fitzpatrick]

Also, you can't use a character variable to hold the DB name in a fully-qualified table reference. Look up the CREATE ALIAS statement.

 

[Shubhrant]

Hey Cringer,
Thanks for the quick response. Could you please provide an example for this.
Appreciate your support.

 

[Shubhrant]

I tried the below query.

define variable m_db as character no-undo.
update
m_db with frame a side-labels.
create alias qad for database value(m_db).
for each qad._file where _file-name = "xx_gvs_det" no-lock:
disp qad._file with 2 col width 200.
end.

In this case i am getting the below error...

 

[RealHeavyDude]

The _File like other meta-schema tables without the logical database name is only valid for the DICTDB. That is a special DB alias that usually points to the first database connected in the session. The reason is that these meta schema tables are present in any Progress database.

You need to use the logical database name ( which is usually specified with -ld on the connection parameters ). Any alias you create should point to a logical database name for an existing connection. If you just provide a field where somebody can enter an arbitary string you either must know which databases are connected and what logical database name the use. I would use a selection list instead which I would populate from the connected databases.

This code will give you the logical database names of all connected databases from which you can then pick the one you need:

define variable dbNo  as integer  no-undo.
define variable dbList  as character  no-undo.
do dbNo = 1 to num-dbs:
  assign dbList = dbList + ( if dbList = "" then "" else "," ) + ldbname ( dbNo ).
end.
message dbList view-as alert-box info.

Then you can do a dynamic query similar to this:

create widget-pool no-error.
define variable queryHandle  as handle  no-undo.
define variable bufferHandle  as handle  no-undo.
define variable fieldHandle  as handle  no-undo.
define variable myDBName  as character  no-undo initial "sports2000".
define variable myFileName  as character  no-undo initial "customer".
define variable myFieldName  as character  no-undo initial "name".
create buffer bufferHandle for table substitute ( "&1.&2", myDBName, myFileName ).
assign fieldHandle = bufferHandle:buffer-field ( myFieldName ).
create query queryHandle.
queryHandle:set-buffers ( bufferHandle ).
queryHandle:query-prepare ( substitute ( "for each &1 no-lock", bufferHandle:name ) ).
queryHandle:query-open ( ).
queryHandle:get-first ( no-lock ).
repeat while not queryHandle:query-off-end:
  display fieldHandle:buffer-value.
  queryHandle:get-next ( no-lock ).
end.
queryHandle:query-close ( ).

Heavy Regards, RealHeavyDude.

 

[Rob Fitzpatrick]

create alias qad for database value(m_db).
for each qad._file where _file-name = "xx_gvs_det" no-lock:

Read the notes for CREATE ALIAS. You cannot reference an alias within the same procedure where you created it.

 

[Cringer]

Just to add to RHD's post, you should make sure you delete the handle to the query after you're done. Also, the repeat loop works better if you
do while queryhandle:get-next(no-lock):
end.

 

[RealHeavyDude]

Just to add to RHD's post, you should make sure you delete the handle to the query after you're done.

That's what the create widget-pool no-error is for.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Ah yes I missed that bit All the same, I'm of the school of thought that you should get into the habit of clearing up things implicitly, with the buffer pool there to catch anything you happen to miss. But that's just me

 

[RealHeavyDude]

Never mind. Most people are not used to widget-pools and how you can use them to your advantage for handle based objects.

 

[Shubhrant]

Thanks all for the response.
Finally i managed to put this in dynamic query. But my problem is i have multiple table join in the dynamic query.
And during the ADD-BUFFER process, all the tables are overwritten to the buffer by the last table buffer.

Hence i am getting output for the last table buffer.
Any idea how to keep the buffer for all the tables.

part of my query is like this:
CREATE QUERY qh.
repeat i = 1 to 2.
create buffer buf for table mTab.
qh:add-buffer(buf).
end.

qh:QUERY-PREPARE(cQuery).
qh:QUERY-OPEN.
DO WHILE NOT qh:QUERY-OFF-END:
qh:get-next().

 

[RealHeavyDude]

Please wrap any code you post here in CODE tags.

Either you did not post the complete code or is it just me that can't see where you change the value of "mTab".

Heavy Regards, RealHeavyDude.

 

[Shubhrant]

My code has been attached.
Now problem is in the display portion i am able to get the scope of the last buffered table.
in my case _field is only getting accessed. But i want both _file and _field to be within scope of the display.

appreciate your response.

 

[RealHeavyDude]

The variable "buf" which points to the dynamic buffer object is overwriten in your loop where you create the buffers - it will always point to the last buffer created in the loop. You can use extents like you do with mTab to solve the issue.

Heavy Regards, RealHeavyDude.

 

[Shubhrant]

Thank you so much "RealHeavyDude". It worked.

 

[Shubhrant]

One small help...
Is it possible to use FIRST-OF or LAST-OF condition during display.
if yes then how to do that because i am keep on getting error.

 

[RealHeavyDude]

first-of and last-of only work with a break in a static for each statement. As far as I know there is no pendant on a dynamic query.

Heavy Regards, RealHeavyDude.

 

[Shubhrant]

Thanks "RealHeavyDude".
Heavily appreciate your responses.

 

[Cringer]

You can break by in a dynamic query. I think it's a v11 feature though. You can use FIRST-OF(n) to reference which of the break bys you are testing. Will post some code shortly if I can find any.