Friends,

I have a requirement in which there will be table names in ascii file which I have to read and later I need to find if those tables has records in a company.

How can a script be written. Please help, if anyone knows. Would be nice if someone helps me with an idea. Thanks.

You would have to use seq.open() and read the file, get the table name - ppmmmxyz, then go through the table ttaad100 company-000 for all the companies or companies linked to the package combination and then compnr.check() or switch.to.company() and then count the number of rows for that table in that company using a dynamic sql or db.nr.rows()

Use dynamic sql and pass the table name. I think I actually have a routine that counts the rows. Below is one sample:

Mark

|******************************************************************************
| Function to get a description or data field.
|******************************************************************************
function get.a.description( string field.info(255), | Field Information.
string ref.table(16), | Ref. Table name to check
string ref.table.field(32), | Field to find record.
string ref.table.desc(32)) | Field to select in ref. table
{
string sql(120)
long sql_id, found, expid

| error.message = ref.table.desc
| expid = expr.compile(error.message & ":=""""")
expid = expr.compile(ref.table.desc & ":=""""")
sql = s.expr$(expid)
expr.free(expid)

sql = " select " & strip$(ref.table.desc)
sql = sql & " from " & strip$(ref.table)
sql = sql & " where " & strip$(ref.table.field) & "=" & chr$(34) & strip$(field.info) & chr$(34)
sql = sql & " as set with 1 rows"
sql_id = sql.parse(sql)
sql.exec(sql_id)
found = sql.fetch(sql_id)
error.bypass = 0
sql.break(sql_id)
sql.close(sql_id)
display(ref.table.desc)
}
|******************************************************************************
| Function not yet used.
|******************************************************************************
function create.postfix.for.project()
{
string sql(120)
long sql_id
domain tcbool found, new

error.bypass = 1
found = false
sql = "select max(tppdm600.cprj) from tppdm600"
sql = sql & " where tppdm600.cprj like """ & pref & ".*"" as set with 1 rows"
sql_id = sql.parse(sql)
sql.exec(sql_id)
sql.fetch(sql_id)
error.bypass = 0
sql.break(sql_id)
sql.close(sql_id)
post = tppdm600.cprj(3;4)

curr.char = 6
new = false
while curr.char>2 and not new
new = check.the.character()
if not new then
curr.char = curr.char - 1
endif

endwhile
if not new then
message("Last 4 are filled, start a new range.")
choice.again()
endif
display("post")
}

What I typically do in a case like this is to ask the user where the file is located. I then copy it to the server (client2server) and the open it and read it. Here is one routine you can modify for your needs - counting the rows.

Mark


|******************************************************************************
| Use dynamic sql to count rows on a table.
|******************************************************************************
function double count_table_rows()
{
string sql(120)
long sql_id
double rows


rows = 0
error.bypass = 1
sql = "select count(*):1 from " & table.name
sql_id = sql.parse(sql)
if sql_id<>0 then
sql.select.bind(sql_id, 1, rows)
sql.exec(sql_id)
while true
on case sql.fetch(sql_id)
case eendfile:
break
case 0:
break
default:
endcase
break
endwhile
sql.break(sql_id)
sql.close(sql_id)
endif
error.bypass = 0
return(rows)
}

Thanks a lot Prasanth. That was an excellent idea.

My code is like this:

String fie(9)
long value, nr_rows

fie = "t"&"tf"&"gld"&"106"
value = db.bind( fie )
db.nr.rows( value, nr_rows, 125 )

Great.

Make sure to check the return values of db.bind() and also use db.unbind() for each operation.

You might get some fatal errors like 512 if runtimes arent successfully or DD mismatches.
long db.bind( string table_name(9) [, ref string buffer(.) [, long comp_nr]] )
Description
This creates a pointer to a specified table. It returns a table ID that you use in other database calls to identify the table. The pointer is to a table with a particular company number and record buffer. You can create additional pointers to the same table by calling the function with a different company number and/or record buffer.
Note
When you create more than one pointer to a table, you must use a different record buffer for each one.

These are like the low level "C" pointers and you have to unbind/free them carefully.

Thank you very much. Excellent solutions from you and Prasanth.

Hats off !

This is coding not admin, moved thread to tools development.

Merged these threads as best I could.

Mark