Binary auto increment key

[michaelgreer]

I am having to set up a table that can be accessed from sql server. The schema I have been given to replicate has the primary key as an auto-increment bigint.  I figure I can use the "I" option in a keydef and wind up with an adequate key as bigint is signed and the GUID type data involved is not, so 4 bytes would get me what I need.  I can define a file such as 'keyed "test",[0:1:4:"I"),0,-1000' with no problem.  My issue is how one write to is such that the key autoincrements.  Michael

[GordDavey]

Michael,

The var which holds the auto increment must be blank, when you write the record. You can then read the record with a KEC() to get it back and the var should have the new value in it.

[michaelgreer]

Gord - Thanks for the response, but this is simply not working for me.  Can you send a code snippet that works?  Michael

[RobL]

Hi Michael,

I'm no auto-increment key expert, but here's an example that seems to work correctly.

begin
!
! define the file
erase "testfile",err=*next
keyed "testfile",[0:1:4:"I"],0,-1000
!
testfile=unt; open (testfile,rec=testfile$,iol=testfile)"testfile"
testfile:iolist id$:[chr(4)],cust_num$,cust_name$
!
! write some data records
for recnum=1 to 5
testfile.id$=dim(4,$00$)
testfile.cust_num$=str(recnum:"000000")
testfile.cust_name$="Customer "+testfile.cust_num$
write (testfile)
next
!
! display records in file
select *,rec=testfile$ from testfile begin ""
print hta(kec(testfile))," - ",testfile.cust_num$," - ",testfile.cust_name$
input *; if ctl=4 then escape
next record

In Gords' post, he indicated that the variable containing the auto-increment must be blank, but I could only get it to work by filling it with $00$'s.

Hope this helps (and is correct!!)

Regards,

Rob Leighton

[Peter.Higgins]

Hi Rob,
Thanks for the example.
My guess is the key should be pre-padded with the default padding. 
The $00$ is the default pad for an internal keyed file which is used in your example, while the default for external is space. 

[RobL]

Hi Peter,

That's an interesting idea, but I think it has to do with type of auto-increment field being used. I tried creating a blank-filled and zero-filled auto-increment and those 2 types allowed the field to be initialized to either "" (null), " " (blank) or $00$. In all 3 cases the fields are part of an internal key.

Regards,

Rob