Baanboard.com

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

User login

Frontpage Sponsor

Main

Google search


Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
0%
200 - 500 GB
25%
500 - 800 GB
0%
800 - 1200 GB
0%
1200 - 1500 GB
0%
1500 - 2000 GB
75%
> 2000 GB
0%
Total votes: 4

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 9th March 2005, 02:36
ncgjiju's Avatar
ncgjiju ncgjiju is offline
Member
 
Join Date: Jan 2005
Location: Singapore
Posts: 73
ncgjiju is on a distinguished road
Baan: BAAN VC, Triton & Baan IV - DB: informix,sql server 200 - OS: hp UNIX 11, Windows
how to insert record in baan table

Hi,

I am trying to insert record into customised baan tables using the microsoft excel sheet. There is no development license so I can't create library code that can attach the code. They do not have baan AFS (Automatic File Server) application availbale.The backend is SQL server. When I try to insert a record uisng the database method in vbscript , I unable as the hash key has to be filled in. The source code of hashkey in db.insert() is not available in the system as it is standard code. How to get around the problem and to insert the record in baan table. What is the standard library that contains the db.insert() function. What are the options available to slove this problem.

Thanks for help.
regards,

George
Reply With Quote
  #2  
Old 9th March 2005, 07:22
MariaC's Avatar
MariaC MariaC is offline
Guru
 
Join Date: Sep 2001
Location: South Africa
Posts: 386
MariaC is on a distinguished road
Baan: BaanIV, BaanV, BaanERPLN - DB: SQL, Oracle - OS: Unix, Windows
Try using the Exchange Module. This has the facility to import data into baan tables from ascii files.
__________________
Regards
Maria
Reply With Quote
  #3  
Old 10th March 2005, 02:12
ncgjiju's Avatar
ncgjiju ncgjiju is offline
Member
 
Join Date: Jan 2005
Location: Singapore
Posts: 73
ncgjiju is on a distinguished road
Baan: BAAN VC, Triton & Baan IV - DB: informix,sql server 200 - OS: hp UNIX 11, Windows
Hi

Hi Marai,

Thanks for your advice , but I need excel to import the data into baan tables. Since there is no development license, I am unable to make a library and create function that will create the same. Is it possible to get the name of standard library that contains the function db.insert(). Please advise.

regards,

George
Reply With Quote
Sponsored Links
  #4  
Old 10th March 2005, 05:05
Debdas Banerjee Debdas Banerjee is offline
Member
 
Join Date: Feb 2003
Location: Mumbai
Posts: 90
Debdas Banerjee is on a distinguished road
Baan: 4c4 - DB: sql-2000/ informix - OS: Advance server / unix
Hi

there is tow way to insert data in to BaaN table

1: db.insert()
2. Exchange Scheme

1.
select tdclc955.*
from tdclc955
where tdclc955._index1 = {:slno.f}
selectdo
select tdclc956.*
from tdclc956 for update
where tdclc956._index1 = {:slno.f }
selectdo
selectempty
tdclc956.slno=tdclc955.slno
tdclc956.flag = tcyesno.No
db.insert(ttdclc956,db.retry)
endselect
commit.transaction()
2. Exchange Scheme

BaaN Utility
Master data
Maintain exchange scheme and then go to special and maintain
AscII file and Maintain Batch

Import module
Creat Import Script
First creat then go to special and run Import data for non regular


Regards
__________________
Deb
Arise Awake & Stop not till the Goal is Reached...........Swami Vivekananda.
Reply With Quote
  #5  
Old 10th March 2005, 11:12
MariaC's Avatar
MariaC MariaC is offline
Guru
 
Join Date: Sep 2001
Location: South Africa
Posts: 386
MariaC is on a distinguished road
Baan: BaanIV, BaanV, BaanERPLN - DB: SQL, Oracle - OS: Unix, Windows
As Deb menitoned the exchange module is a standard uitility in baan which will read data from and ascii file (convert excel spreadsheet to CSV format) and then populate the corresponding tables in baan. It works well as does reference checking.
__________________
Regards
Maria
Reply With Quote
  #6  
Old 10th March 2005, 17:06
Francesco's Avatar
Francesco Francesco is offline
Guru
 
Join Date: Aug 2001
Location: Antwerp, BE
Posts: 727
Francesco is on a distinguished road
Baan: 5b - DB: Oracle - OS: Solaris
paddle?

Quote:
Originally Posted by ncgjiju
They do not have baan AFS (Automatic File Server) application availbale.
Without a development license you are up a creek with no canoo. But why would AFS (Application Function Server) not be available? Anyway, you are only interested in API, and why would they turn that off?

The beauty of that is that you can do all your coding in Excel's VBA and subsequently don't need developer's access to Baan. If this is not an option, then exchange is your _only_ alternative.
__________________
Cheers,

Francesco
..............................................................

Admiral Business Solutions | My World | Baan Board | IT Happens!

"If everyone is thinking alike, then somebody isn't thinking" -- George Patton
"It's easy to cry 'bug' when the truth is that you've got a complex system and sometimes it takes a while to get all the components to co-exist peacefully." -- Doug Vargas
Reply With Quote
  #7  
Old 11th March 2005, 02:21
ncgjiju's Avatar
ncgjiju ncgjiju is offline
Member
 
Join Date: Jan 2005
Location: Singapore
Posts: 73
ncgjiju is on a distinguished road
Baan: BAAN VC, Triton & Baan IV - DB: informix,sql server 200 - OS: hp UNIX 11, Windows
reply

Hi Franseco,

I have already done the coding in vb script, I am able to update , but unable insert a record as the probelm is of hashkeys. Can u please guide on this issue or show any sample code.


regards,

George
Reply With Quote
  #8  
Old 11th March 2005, 02:26
Francesco's Avatar
Francesco Francesco is offline
Guru
 
Join Date: Aug 2001
Location: Antwerp, BE
Posts: 727
Francesco is on a distinguished road
Baan: 5b - DB: Oracle - OS: Solaris
API or ODBC?

The beauty of using the API is that you don't have to worry about hash codes. You still use Baan's session functionality and all the engines behind it.

If you are updating through excel and experience problems with the hash field, I am guessing that you go directly to the database with ODBC rather than using Baan's API.

There is a forum here dedicated to AFS/API/OLE. You can find tons of sample code there plus the Baan documentation on the subject (remarkably complete for Baan standards).
__________________
Cheers,

Francesco
..............................................................

Admiral Business Solutions | My World | Baan Board | IT Happens!

"If everyone is thinking alike, then somebody isn't thinking" -- George Patton
"It's easy to cry 'bug' when the truth is that you've got a complex system and sometimes it takes a while to get all the components to co-exist peacefully." -- Doug Vargas
Reply With Quote
  #9  
Old 11th March 2005, 03:30
ncgjiju's Avatar
ncgjiju ncgjiju is offline
Member
 
Join Date: Jan 2005
Location: Singapore
Posts: 73
ncgjiju is on a distinguished road
Baan: BAAN VC, Triton & Baan IV - DB: informix,sql server 200 - OS: hp UNIX 11, Windows
Hi

Hi Franseco,

do you know the name form or what is web site name

regards.

George
Reply With Quote
  #10  
Old 11th March 2005, 06:25
Debdas Banerjee Debdas Banerjee is offline
Member
 
Join Date: Feb 2003
Location: Mumbai
Posts: 90
Debdas Banerjee is on a distinguished road
Baan: 4c4 - DB: sql-2000/ informix - OS: Advance server / unix
I want to share my experience on inserting data into BaaN Database.
1.You can insert data through Exchange Scheme
2.You can insert data through AFS
3.You can Insert data through any other utili using BaaN DB

I am giving my code below . This is visual basic code . First we developed barcode system through Visual Basic and update the data through Visual basic. But problem is this it will not generate the hash column. You have to reorganise the data then only data will be visible through GTM or use will able to use that data . If you open that data through SQL - query analyzer , you will the data before Reorganise the data .

I don't know the logic of Hash column . I tried lot for has column even I ask Baan Global they also failed to give.

Do Until rs.EOF
stmt1 = "insert into tticus930" & gCompany & _
" values (" & rs("t_pdno") & ", " & rs("t_ldno") & ", " & _
"cast('" & strdate & "' as smalldatetime), " & seqn & ", " & _
rs("qty") & ", 0, 4, '0'," & _
" 0, 0)"
dbconn.Execute stmt1
rs.MoveNext
Loop
rs.Close

For Hash I did this but now working

stmt = "select (max(cast(hash1 as integer)) + 1) nexthash from tticus930" & gCompany

Regards
__________________
Deb
Arise Awake & Stop not till the Goal is Reached...........Swami Vivekananda.
Reply With Quote
  #11  
Old 11th March 2005, 14:33
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 7,114
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Quote:
Originally Posted by ncgjiju
Hi Franseco,

do you know the name form or what is web site name

regards.

George

The forum is on baanboard. Here is a link to the AFS/DDC/OLE Forum. You might also want to search this complete board for "hash". Seems to me there were previous posts that discuss this topic.

Mark
Reply With Quote
  #12  
Old 21st March 2005, 05:37
ncgjiju's Avatar
ncgjiju ncgjiju is offline
Member
 
Join Date: Jan 2005
Location: Singapore
Posts: 73
ncgjiju is on a distinguished road
Baan: BAAN VC, Triton & Baan IV - DB: informix,sql server 200 - OS: hp UNIX 11, Windows
Hi debdas

I have tried the sql to get the hash key , but I get a empty value

stmt = "select (max(cast(hash1 as integer)) + 1) nexthash from tticus930" & gCompany

mysql = "select (max(cast(hash1 as integer)) + 1) next hash from tdupk070300"

I am getting the next hash as empty value. please help


regards

George
Reply With Quote
  #13  
Old 21st March 2005, 09:32
RobertB's Avatar
RobertB RobertB is offline
Senior Member
 
Join Date: Jul 2002
Location: S. Germany
Posts: 130
RobertB is on a distinguished road
Baan: Baan 5.0c, sp15 - DB: MS SQL Server 7, sp3 - OS: Win XP + 2000, RedHat 7.3
Hi,

It is certainly possible to fill the hash fields using SQL statements, and it's quite easy to do so. But, it's not to be recommended!.

Here's how it's done: take the (Baan V) table tcibd001 (General Item Data) as an example. The first two indices are:

(1) Item ; field item;
(2) Search Key 1, Item; fields seak, item

Each index corresponds to a hash field in the table (duh).

You need to check the lengths of the individual fields in each index: for the first index, the field-length is 47 characters (domain tcitem), and for the second, it's 16 + 47 = 63 characters (domains tcseak and tcitem).

Then the code for generating the first hash field for Item No. 12345678-9 is:
Code:
select convert(varbinary(47), '         12345678-9                            ') as hash1
, where the first nine spaces stand for a (in this case empty) Project Number, and the rest of the field is padded with spaces (important).

For the second hash-field, we might have the following:
Code:
select convert(varbinary(63), 'POWER DRIVE UNIT         12345678-9                            ') as hash2
You already know how to do your other fields.

To read a hash field, do this:
Code:
select convert(varchar(47), hash1), convert(varchar(63), hash2)
from baandbc..ttcibd001100
where t_item = space(9) + '12345678-9'
HTH,
Rob
Reply With Quote
  #14  
Old 13th August 2005, 00:15
NaaBbaaN NaaBbaaN is offline
Junior Member
 
Join Date: May 2003
Posts: 6
NaaBbaaN is on a distinguished road
Numeric, date hash

How Could we read and write number, date hash fields
Reply With Quote
  #15  
Old 13th August 2005, 05:51
lsatenstein's Avatar
lsatenstein lsatenstein is offline
Senior Member
 
Join Date: Oct 2001
Location: Canada (Montreal)
Posts: 147
lsatenstein is on a distinguished road
Baan: BaaN4 Baan5 - DB: Oracle. Informix, MS SQL DB2 - OS: Unix, NT Linux
adding record to baan table

There are several ways to do it, and it depends on whether you need to do it for a single record, or for multiple records. Second, if it can be done in batch or live.

When it is only one record is to be added, one could dilliently use ttaad4100 GTM)

When there are several, you could use the excel macro to write out a delimited file and use bdbpre/bdbpost (one of the two) to read the ascii file and to merge in the records.

If you do not have developper, then I presume you do not have easy sql.
With easy sql, you have a third way, and that is to write a report script (the regular script does not allow insert or delete, but you can have a report with an edit script attached. Use the sql from within the edit script to insert the records, again from a flat file as input.

Use your dabase system to do an sql and completely circumvent baan.

If Baan does not make a single developer licence available for free, you should switch to a better product. (Oracle, informix, and a few other sql's would offer what Baan offers)
__________________
Les. Satenstein
Senior Specialist in IT.
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
Insert record in table: not work mostrightfuture Tools Development 3 29th November 2004 10:43
Is Invensys really planning to sell Baan? baanassist General Discussion & Chat 10 3rd September 2002 18:29
Baanboard.com Community Bulletin 01/05/2002 patvdv About Baanboard.com 0 1st May 2002 15:04
Oracle table grants - Baan tables, non-Baan user francishsu Operating Systems & Databases 3 5th February 2002 21:56
Segregating tools company 000 vanwinkle Operating Systems & Databases 7 5th February 2002 14:22


All times are GMT +2. The time now is 10:25.


©2001-2018 - Baanboard.com - Baanforums.com