<i><font color="#663300"><i><font color="#663300"><i><font color="#663300">I'm currently working on a project that will use MS SQL Server 7.0 as the back-end. I'm planning to use IDENTITY columns for primary keys for my tables. Is this the "best" course of action to take with regards to CB6.1 treatment of primary keys? How about in relation to child records? How would this affect determining the parent's primary key?</font></i>
I haven't done this within the Codebook framework, but I've worked with it enough to know that it's certainly possible. Rather than pre-assign keys by calling NextPK(), you need to query the variable @@IDENTITY immediately after inserting a new record. Using SQL Passthrough, the code is pretty simple:
<code>SQLEXEC(lnHandle, "SELECT @@IDENTITY AS iID")
liPK = SQLResult.iID</code>
In the case of parent-child relationships, you would probably have to defer setting the FK field values until the parent record is inserted.
</font></i>
How would this work if I'm using remote views? Will I need to use SPT to insert a new record into my views? I tried inserting a new record into one of my views and tried to retrieve @@IDENTITY but it returned a NULL.
Please bear with me as I'm very new at this SQL Server stuff.
</font></i>
You have to use the connection of the remote view.
<code>
lnHandle = CursorGetProp('ConnectHandle', 'nameofremoteview')
SQLEXEC(lnHandle, "SELECT @@IDENTITY AS iID")
liPK = SQLResult.iID</code>
Also, make sure you are only inserting one record at a time, if you do this in a batch, you will only get the last indentity value used.
Bob
©2001 Bob Archer |
