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
17%
200 - 500 GB
30%
500 - 800 GB
3%
800 - 1200 GB
7%
1200 - 1500 GB
10%
1500 - 2000 GB
13%
> 2000 GB
20%
Total votes: 30

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 4th June 2003, 23:07
mfaisal mfaisal is offline
Junior Member
 
Join Date: Apr 2003
Location: Houston
Posts: 8
mfaisal is on a distinguished road
Baan: Baan4, Baan5 - DB: Oracle, SQL Server -
SQL Server with Level 1 indexing

I want to insert records directly in SQL Server database that has level 1 indexing. I am not able to do so because the INSERT statement is not filling in the Hash# column.


Any help to solve this issue will be appreciated.


Thanks,

Muhammad Faisal
Reply With Quote
  #2  
Old 5th June 2003, 00:47
anupkumar anupkumar is offline
Junior Member
 
Join Date: Jan 2002
Posts: 26
anupkumar is on a distinguished road
Baan: Baan IV , Baan V - DB: all - OS: all
Not possible from external applications

Faisal,

If you are trying to insert records in a database which is in Level 1 mode then it is not possible. This is because the logic of creation of the hash columns is proprietary to the baan db driver. I suppose it will be difficult to recreate this logic.

u can try other methods..eg bdbpost , exchange depending on the source of the records to be inserted.

bye
Anup
Reply With Quote
  #3  
Old 5th June 2003, 01:19
mfaisal mfaisal is offline
Junior Member
 
Join Date: Apr 2003
Location: Houston
Posts: 8
mfaisal is on a distinguished road
Baan: Baan4, Baan5 - DB: Oracle, SQL Server -
How can I make it to work?

Anup,

Thanks for your reply.

I am using ASP to insert records in SQL Server for a Baan table. How will this be possible?

Please help if in anyway possible....


Regards,

Muhammad Faisal
Reply With Quote
  #4  
Old 5th June 2003, 02:20
anupkumar anupkumar is offline
Junior Member
 
Join Date: Jan 2002
Posts: 26
anupkumar is on a distinguished road
Baan: Baan IV , Baan V - DB: all - OS: all
May not be possible

I doubt if this will be possible thru ASP. I cant think of any other way. which version of baan are you using ? In Baan V level 2 is supported for sql. So you can handle this issue by changing the level of the table.


Bye
Anup
Reply With Quote
  #5  
Old 5th June 2003, 09:41
NvanBeest's Avatar
NvanBeest NvanBeest is offline
Guru
 
Join Date: May 2003
Location: South Africa
Posts: 520
NvanBeest is on a distinguished road
Baan: BaanIVc4 - DB: Oracle, TBase, SQL Server - OS: AIX, Linux, Window$
It IS possible

Hi Muhammad

It is possible to create the hash columns, but you will have to do it manually. How? Have a look at the hash columns of other records, and determine how they are built up. It will be a contatenation of the index fields. Then, in your ASP, build these hash values just before the insert.

Just be aware of the fact that Baan might be doing some calculations with the data before inserting a record. If so, the easiest would be to have your ASP generate an ASCII file, and, with the use of Exchange and an AFS, generate an import scheme. That's the safest route.

Regards,
Nico
Reply With Quote
  #6  
Old 5th June 2003, 12:35
Dikkie Dik's Avatar
Dikkie Dik Dikkie Dik is offline
Guru
 
Join Date: Sep 2002
Location: Netherlands
Posts: 585
Dikkie Dik is on a distinguished road
Baan: Triton 3.0 and higher - DB: All - OS: All
Thumbs down Don't create hash columns yourself

Indeed in for more than 90% of the cases it works when calculating a hash column, but in some other cases you can get into problems. The Baan hash algoritmn is far more complex.

So my advise: Don't create hash columns yourself, but indeed importing via Exchange or bdbpost is an option.

Kind regards,
Dick
__________________
BTW: this post has been made on my personal view. My employer might not share my point of view.
Reply With Quote
  #7  
Old 5th June 2003, 16:28
anupkumar anupkumar is offline
Junior Member
 
Join Date: Jan 2002
Posts: 26
anupkumar is on a distinguished road
Baan: Baan IV , Baan V - DB: all - OS: all
Not as simple as it looks

Nico,

the hash column is not as simple (concatenation) as it looks. If you take a dump of the table from SQL DB to a flat file and open it in an editor which shows you control characters you will see that there are lots of control characters in the hash column in between the concatenation. This is the difficult part.


Anup
Reply With Quote
  #8  
Old 9th June 2003, 10:38
morpheus's Avatar
morpheus morpheus is offline
Guru
 
Join Date: Jun 2002
Location: Planet of the apes
Posts: 369
morpheus is on a distinguished road
Baan: BaaN IV c4 - DB: BaaN Base - OS: HP-UX 11
Question Tell me

Recently, I also faced the same problem.
We wanted to insert the records directly in the table existing on MS-SQL. The table was created through BaaN.

Point # 1 - Hash column was creating problem for us. We decided to populate this column, with incremental values (1,2,3...), i.e., new hash value for every new record. It worked fine.
Point # 2 - We were able to insert the duplicate values in the primary key!!

Comments...
Reply With Quote
Sponsored Links
  #9  
Old 9th June 2003, 18:30
morpheus's Avatar
morpheus morpheus is offline
Guru
 
Join Date: Jun 2002
Location: Planet of the apes
Posts: 369
morpheus is on a distinguished road
Baan: BaaN IV c4 - DB: BaaN Base - OS: HP-UX 11
Exclamation Comments

After the last post, I worked again on the table. The observation was, although records can be inserted from the database level, but they can NOT be accessed from the BaaN application!! Probably BaaN db driver is not able to decode the hash column!!
Reply With Quote
  #10  
Old 9th June 2003, 18:33
anupkumar anupkumar is offline
Junior Member
 
Join Date: Jan 2002
Posts: 26
anupkumar is on a distinguished road
Baan: Baan IV , Baan V - DB: all - OS: all
same obeservations

I too had tried this. but when you access this record thru GTD or session baan hangs..It is not able to read this record.

Bye
Anup
Reply With Quote
  #11  
Old 15th July 2003, 11:09
vishbaan's Avatar
vishbaan vishbaan is offline
Senior Member
 
Join Date: Aug 2001
Location: Dubai
Posts: 209
vishbaan is on a distinguished road
Baan: B40c4, LN - DB: SQL2K - OS: W2K
make the key field of string type

hi

hash fields are the ones which are the index fields in baan used to locate the record.

we have decoded the logic if the key has only string data type.
it is as follows and it works live here.

eg:
baan ._index1 = str_field1, str_field2

then from VB you can populate the t_hash1 as follows:
hash1 = binary(str_field1 & str_field2)

But if the index is madeup of a string field and a long field then how baan generates the hash field values.......?

vish
Reply With Quote
  #12  
Old 11th September 2006, 05:29
Will@Tait's Avatar
Will@Tait Will@Tait is offline
Junior Member
 
Join Date: Feb 2002
Location: NZ
Posts: 6
Will@Tait is on a distinguished road
Baan: 5c sp8 - DB: Informix 7.31 - OS: Solaris 9
Smile Here are the routines we use for Informix 7.31 and Baan5 (worked on Baan4 too)

Disclaimer: Provided AS-IS without warranty of any kind. Use at your own risk!
ps. While these work great for us for reading (your milage may vary), I would NOT recommend using these for writing!

example of use:
(assuming index1 was made up of order and line)

hash1 = hash_encode_string_left(orno,9) & hash_encode_int16(pono)

You will need to use parameterised queries to pass thru the encoded data.

Code:
Option Explicit
Option Compare Binary


Public Function hash_encode_string_left(byval s$, byval l%) As String
    Dim t$: t = Trim$(s)
    If Len(t) < l Then
        hash_encode_string_left = t & Space$(l - Len(t))
    Else
        hash_encode_string_left = Left$(t, l)
    End If
End Function

Public Function hash_encode_string_right(byval s$, byval l%) As String
    Dim t$: t = Trim$(s)
    If Len(t) < l Then
        hash_encode_string_right = Space$(l - Len(t)) & t
    Else
        hash_encode_string_right = Right$(t, l)
    End If
End Function

Public Function hash_encode_date(byval d As Date) As String
    Dim l&

    If d = 0 Or d = CDate("1/1/1") Then
        l = 0
    Else
        l = _
            Year(d) * 10000 + _
            Month(d) * 100 + _
            Day(d)
    End If

    Dim t$
    t = Format$(Abs(l), "00000000")
    hash_encode_date = _
        Chr$(1 + CInt(Mid$(t, 1, 2))) & _
        Chr$(1 + CInt(Mid$(t, 3, 2))) & _
        Chr$(1 + CInt(Mid$(t, 5, 2))) & _
        Chr$(1 + CInt(Mid$(t, 7, 2)))

End Function

Public Function hash_encode_datetime(byval d As Date) As String

    Dim cymd&   'century, year, month, day
    Dim hms&    'hour, minute, second

    If d = 0 Or d = CDate("1/1/1") Or d = CDate("1/1/1970") Then
        cymd = 0 'not tested
        hms = 0
    Else
        cymd = _
            Year(d) * 10000 + _
            Month(d) * 100 + _
            Day(d)
        hms = _
            Hour(d) * 10000 + _
            Minute(d) * 100 + _
            Second(d)
    End If

    Dim t$
    t = Format$(cymd, "00000000") + Format$(hms, "000000")
    hash_encode_datetime = _
        Chr$(1 + CInt(Mid$(t, 1, 2))) & _
        Chr$(1 + CInt(Mid$(t, 3, 2))) & _
        Chr$(1 + CInt(Mid$(t, 5, 2))) & _
        Chr$(1 + CInt(Mid$(t, 7, 2))) & _
        Chr$(1 + CInt(Mid$(t, 9, 2))) & _
        Chr$(1 + CInt(Mid$(t, 11, 2))) & _
        Chr$(1 + CInt(Mid$(t, 13, 2)))

End Function

Public Function hash_encode_int32(byval l&) As String
    Dim t$
    t = Format$(Abs(l), "0000000000")
    If l >= 0 Then  'positive number
        hash_encode_int32 = _
            Chr$(129 + CInt(Mid$(t, 1, 2))) & _
            Chr$(1 + CInt(Mid$(t, 3, 2))) & _
            Chr$(1 + CInt(Mid$(t, 5, 2))) & _
            Chr$(1 + CInt(Mid$(t, 7, 2))) & _
            Chr$(1 + CInt(Mid$(t, 9, 2)))
    Else            'negative number
        hash_encode_int32 = _
            Chr$(100 - CInt(Mid$(t, 1, 2))) & _
            Chr$(100 - CInt(Mid$(t, 3, 2))) & _
            Chr$(100 - CInt(Mid$(t, 5, 2))) & _
            Chr$(100 - CInt(Mid$(t, 7, 2))) & _
            Chr$(100 - CInt(Mid$(t, 9, 2)))
    End If
End Function

Public Function hash_encode_int16(byval i%) As String
    Dim t$
    t = Format$(Abs(i), "00000")
    If i >= 0 Then  'positive number
        hash_encode_int16 = _
            Chr$(129 + CInt(Mid$(t, 1, 2))) & _
            Chr$(1 + CInt(Mid$(t, 3, 2))) & _
            Chr$(1 + CInt(Mid$(t, 5, 1)))
    Else            'negative number
        hash_encode_int16 = _
            Chr$(100 - CInt(Mid$(t, 1, 2))) & _
            Chr$(100 - CInt(Mid$(t, 3, 2))) & _
            Chr$(10 - CInt(Mid$(t, 5, 1)))
    End If
End Function

Public Function hash_encode_byte(byval i%) As String
    hash_encode_byte = Chr$(i + 1)
End Function

Public Function hash_encode_enum(byval i%) As String
    hash_encode_enum = Chr$(i + 1)
End Function

Last edited by Will@Tait : 12th September 2006 at 01:33.
Reply With Quote
  #13  
Old 6th November 2006, 11:39
Paul P's Avatar
Paul P Paul P is offline
Guru
 
Join Date: Jul 2002
Location: Jakarta, Indonesia
Posts: 797
Paul P is on a distinguished road
Baan: BaanIV, BaanERP - DB: SQL Server, Informix, Oracle - OS: Windows, HP-UX
Wow, Will! How on earth did you find out about this? My programmer colleagues just gave test runs on these for a client that also uses Informix and it seems to work!
For others, I think BaanERP has different hash calculation mechanism for different database server. On SQL Server, the hash calculation seems to be a lot simpler. There, we've used the simple method I mentioned in hash fields thread even for writing records, and we haven't seem to bump into major problems.
Thanks very much for the info, Will
Paul
__________________
Pambudi

Visit my technology consulting blog

Last edited by Paul P : 6th November 2006 at 11:52.
Reply With Quote
  #14  
Old 19th November 2006, 21:29
Junior Junior is offline
Junior Member
 
Join Date: Oct 2006
Posts: 3
Junior is on a distinguished road
Baan: 5c - DB: informix 7.31 - OS: sun solaris 9
Thanks

I "simply" decoded various hash columns down to their byte values and compared them to the source fields. The negative values took a little longer to understand but it was a doddle once I figured out what Baan was doing.
Hmm, I still have the conversion for double values to do...

At least with Informix, Baan is taking care to avoid chr(0) in any of the computed hash values.

As an aside, Baan likes to store zero dates strangely in the actual data fields: non utc zero = 01-01-0001, utc zero = 01-01-1970 0:0:0 GMT. These play havoc with ODBC queries and the like. We've written stored procedures to convert the above into NULL values for our use, and also conversion's to/from GMT to local time depending on the user's timezone.
Reply With Quote
  #15  
Old 20th November 2006, 06:14
bigjack's Avatar
bigjack bigjack is offline
Senior Member
 
Join Date: Feb 2004
Location: India
Posts: 212
bigjack is on a distinguished road
Baan: Baan IVc4 , SSA ERP LN - DB: informix - OS: HP UX
Hi,

After importing records from external applications , in order to *see* the records in baan you will have to do a reorganize tables. If its possible in your scenario you can schedule a job which will carry out the same.

Bye
__________________
Quote:
"I dont believe in miracles...I rely on them"
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
SQL Server 7.0 SP4 + BaanERP compatiblity Jabran Tools Administration & Installation 1 4th October 2004 12:49
Decision Manager & SQL Server 7.0 BisBoy Open World, Portal & Decision Manager 0 10th July 2003 14:11
Should Baan provide a level 2 database driver for SQL server 2000 on BaanIVc4? patvdv Polls and Surveys 6 28th March 2003 14:22
determine level mode for SQL Server spartacus Operating Systems & Databases 10 30th January 2003 18:26
SQL Server Driver Level II - already released ? Ramiro Operating Systems & Databases 7 29th April 2002 18:16


All times are GMT +2. The time now is 19:01.


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