Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 6th April 2004, 21:32
Renegade's Avatar
Renegade Renegade is offline
Senior Member
 
Join Date: Sep 2001
Location: Norway
Posts: 221
Renegade is on a distinguished road
Baan: BaaNERP5.0c - DB: Oracle - OS: Windows
Strange requirement

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.
Reply With Quote
  #2  
Old 6th April 2004, 21:57
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
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()
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #3  
Old 6th April 2004, 22:03
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,935
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Sample of Dynamic SQL

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

Mark
Code:
|******************************************************************************
| 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")
}
Reply With Quote
Sponsored Links
  #4  
Old 6th April 2004, 22:06
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,935
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Typically..

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

Code:
|******************************************************************************
| 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)
}
Reply With Quote
  #5  
Old 6th April 2004, 22:18
Renegade's Avatar
Renegade Renegade is offline
Senior Member
 
Join Date: Sep 2001
Location: Norway
Posts: 221
Renegade is on a distinguished road
Baan: BaaNERP5.0c - DB: Oracle - OS: Windows
Excellent idea.

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.
__________________
Knowledge is experience, everything else is just information. - A. Einstein
Reply With Quote
  #6  
Old 6th April 2004, 22:25
NPRao's Avatar
NPRao NPRao is offline
Guru
 
Join Date: Aug 2001
Location: Pacific NW, USA
Posts: 3,032
NPRao will become famous soon enough
Baan: iBaanERP-5.2a(Reger),SSA-ERP-LN-6.1,Infor LN-10.x - DB: Oracle-10g,11g,12c,MS-SQL - OS: HP-UX, Linux, Windows
Arrow

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.
Quote:
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.
__________________
The art of perfection does not lie in doing extraordinary things but, doing ordinary things extraordinarily well. [-N. Prashanth Rao]
How To Ask Questions The Smart Way,BaaNBoard,NPRao
Reply With Quote
  #7  
Old 6th April 2004, 22:29
Renegade's Avatar
Renegade Renegade is offline
Senior Member
 
Join Date: Sep 2001
Location: Norway
Posts: 221
Renegade is on a distinguished road
Baan: BaaNERP5.0c - DB: Oracle - OS: Windows
Thanks a lot.

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

Hats off !
Reply With Quote
  #8  
Old 7th April 2004, 13:15
victor_cleto's Avatar
victor_cleto victor_cleto is offline
Guru
 
Join Date: Aug 2001
Location: Portugal
Posts: 776
victor_cleto is on a distinguished road
Baan: none (B40c4 was last) - DB: Oracle - OS: Linux (RHEL)
This is coding not admin, moved thread to tools development.
Reply With Quote
  #9  
Old 7th April 2004, 17:06
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,935
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Merged the threads

Merged these threads as best I could.

Mark
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Smth. strange while job execution Old Vens Operating Systems & Databases 5 14th November 2003 06:53
Strange mesage when importing EDI DELINS via tcedi7205m000 MrMarco Manufacturing & Supply Chain 0 4th November 2003 10:35
Immediate Requirement BaaN Technofunctional pradeepspatil Jobs and Resumes 1 19th February 2003 14:46
Strange problem with AFS arunprasath AFS/DDC/OLE: Function servers 5 26th November 2002 19:22
Requirement for Functional/Implementation Team Leads resourcemanager Jobs and Resumes 1 8th March 2002 17:27


All times are GMT +2. The time now is 12:17.


©2001-2017 - Baanboard.com - Baanforums.com