I have a query relating to updating Btrieve files using scalable SQL and I would
be grateful for any advice you may have on
this matter.
Pervasive was purchased from Southdown (UK). Our sales ordernumber from the
first purchase of user licenses is 24948, and our customer number is 04013. We
also have a reference number: REV 5416.
I am writing to you with regard to Pervasive SQL. We have version 7.0 and
service pack 5.
I have created a DDF in order to be able to access data from a 'legacy' system
using SQL and VB.
One of the processes of this VB application is to perform SQL updates on the
btrieve files - there is a particular field type on the legacy file which is a
six digit number compressed into three characters. In the documentation we have
the field is described as being packed numeric. I've defined it as a char(003)
in the ddf.
With numbers other than zero there are no problems - I have some vb code that
converts the six digit number into three characters.
When the number is zero however, the sql query fails with a syntax error - I
think that '000000' compressed into three charcacters is the same as sending a
null value to the field - I would be grateful for any advice you might have re:
this.
The code I use to encode the field is as follows :
strNewvalue(lngLocali) = Trim(strNewvalue(lngLocali))
strNewvalue(lngLocali) =
FillWithLeadingZeros(Trim(strNewvalue(lngLocali)), (Val(lngFieldLen(lngLocali) * 2)))
strlen = Len(Trim(strNewvalue(lngLocali)))
lncount = 1
Do Until lncount > strlen
strString = Val("&H" & (Mid(strNewvalue(lngLocali), lncount, 2)))
strString = Chr(Val(strString))
strString2 = strString2 & strString
lncount = lncount + 2
Loop
strTemporaryField = "'" & strString2 & "'"
The first three lines take the 'realworld' number and format it to have leading zeros - '500' becomes '000500' or in our problem case '0' becomes '000000'
The code within the 'Do until' takes two characters at a time converts them to hex and then stores the 'char' equivalent in string2 in the case of zero I think
this is like setting the field to be null.
Thanks,
be grateful for any advice you may have on
this matter.
Pervasive was purchased from Southdown (UK). Our sales ordernumber from the
first purchase of user licenses is 24948, and our customer number is 04013. We
also have a reference number: REV 5416.
I am writing to you with regard to Pervasive SQL. We have version 7.0 and
service pack 5.
I have created a DDF in order to be able to access data from a 'legacy' system
using SQL and VB.
One of the processes of this VB application is to perform SQL updates on the
btrieve files - there is a particular field type on the legacy file which is a
six digit number compressed into three characters. In the documentation we have
the field is described as being packed numeric. I've defined it as a char(003)
in the ddf.
With numbers other than zero there are no problems - I have some vb code that
converts the six digit number into three characters.
When the number is zero however, the sql query fails with a syntax error - I
think that '000000' compressed into three charcacters is the same as sending a
null value to the field - I would be grateful for any advice you might have re:
this.
The code I use to encode the field is as follows :
strNewvalue(lngLocali) = Trim(strNewvalue(lngLocali))
strNewvalue(lngLocali) =
FillWithLeadingZeros(Trim(strNewvalue(lngLocali)), (Val(lngFieldLen(lngLocali) * 2)))
strlen = Len(Trim(strNewvalue(lngLocali)))
lncount = 1
Do Until lncount > strlen
strString = Val("&H" & (Mid(strNewvalue(lngLocali), lncount, 2)))
strString = Chr(Val(strString))
strString2 = strString2 & strString
lncount = lncount + 2
Loop
strTemporaryField = "'" & strString2 & "'"
The first three lines take the 'realworld' number and format it to have leading zeros - '500' becomes '000500' or in our problem case '0' becomes '000000'
The code within the 'Do until' takes two characters at a time converts them to hex and then stores the 'char' equivalent in string2 in the case of zero I think
this is like setting the field to be null.
Thanks,