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

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

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

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

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

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

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

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...

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!!

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

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

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.

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

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

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.

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

A reorg isn't necessary if the records are written with properly calculated hash column values.

Actually Baan loves to crash or act randomly if it comes across such records!!! (from painful experience obtained when Baan somehow corrupted the hash values for one or two records).

That's why we DON'T write directly into the Baan DB - we always use Baan to do reads/writes/deletes (Baan AFS and Baan OLE is a godsend here!), and then parameterised ODBC/OLEDB queries using the hash calculation logic to give us high-speed reads for add-on apps outside of the Baan environment.