Hi all,

I'm inserting into a FM6 table via ODBC, and the table has a
non-modifyable serial number (auto-entered).

any idea how i can get the value of that column for the record i just
inserted?

Thanks
Andy

Just put the serial-number field on your layout. Or isn't that what you 
need?

Ursus

"Andy" <ajohnstone@capcitypress.com> schreef in bericht 
news:1119456109.758033.19970@g14g2000cwa.googlegroups.com...
> Hi all,
>
> I'm inserting into a FM6 table via ODBC, and the table has a
> non-modifyable serial number (auto-entered).
>
> any idea how i can get the value of that column for the record i just
> inserted?
>
> Thanks
> Andy
> 

ursus,

I'm accessing soley through ODBC... so no layouts involved.

What i did is basically the equivolent of the @@IDENTITY variable in
SQL Server.

I *could* do the insert, and then Select Max( ID ) FROM table... but
i'm not sure that guarantees me the ID of the row I just inserted (as
opposed to a row inserted *after* my insert, but *before* my select).

Hope that clears what I'm looking for up a bit.

Andy

I am absolutely a nitwit regarding ODBC. But what I gather is that you want 
to see a value inside FMP before through a ADBC connection. I don't think 
that would be possible.

have you tried the FMP helpfile? it has some info on how to use OBDC. This 
is the list of instructions and elements that FMP can handle. It sdoesn't 
mention the @@IDENTITY. But as I don't know anything about OBDC I could be 
talking rubbish.

SELECT
INSERT (ook in een subopvraag)
UPDATE
DELETE
CREATE TABLE
DROP TABLE
ALTER TABLE
CREATE INDEX
DROP INDEX
FROM-instruction
ORDER BY-instruction
GROUP BY-instruction
HAVING-instruction
WHERE-instruction
UNION-operator
FOR UPDATE-instruction
: SUM, AVG, COUNT, MAX, MIN
DISTINCT SQL-operator
Inner Join-instructions
Left Outer Join-instructions
Subopvragen
Scalaire instructions
Current of Cursor-instructions
ROW ID

"Andy" <ajohnstone@capcitypress.com> schreef in bericht 
news:1119531550.117832.207040@g43g2000cwa.googlegroups.com...
> ursus,
>
> I'm accessing soley through ODBC... so no layouts involved.
>
> What i did is basically the equivolent of the @@IDENTITY variable in
> SQL Server.
>
> I *could* do the insert, and then Select Max( ID ) FROM table... but
> i'm not sure that guarantees me the ID of the row I just inserted (as
> opposed to a row inserted *after* my insert, but *before* my select).
>
> Hope that clears what I'm looking for up a bit.
>
> Andy
> 

ursus,

Yes, I've looked through the help, and haven't found what I need, which
is why I'm asking here if and how it can be done.

The best I could do is a select immediately after the insert.. but I'm
not sure that would ensure I have the row i inserted, and not someone
else.

Thanks
Andy

If you use a log-in system for your fmp file,  (accounts & privileges) you 
could do the following.

Create a script that
Imports your original ODBC record-serial-number together with all the data 
you need for the record
then puts the original ODBC record-serial-number into a global field.

With a user based system, globals are user-specific. So gMyGlobal can hold a 
value of UrsusSerial on my console (computer in the network) and at the same 
time hold a completely different value AndysSerial on your console. This way 
you will allways know what serial came out of the ODBC connection and that 
you are the one that put it there in the first place. Be aware that normally 
globals are stored when you shutdown a file. But with multi-user systems 
only the last one gets stored. But then again you should almost never rely 
on globals to hold data that does change to be retrieved in a new session.

Ursus

"Andy" <ajohnstone@capcitypress.com> schreef in bericht 
news:1119535811.102568.265530@f14g2000cwb.googlegroups.com...
> ursus,
>
> Yes, I've looked through the help, and haven't found what I need, which
> is why I'm asking here if and how it can be done.
>
> The best I could do is a select immediately after the insert.. but I'm
> not sure that would ensure I have the row i inserted, and not someone
> else.
>
> Thanks
> Andy
> 

Why don't you create a unique ID in your SQL database and send that to
FileMaker along with the other information. Then when you wanted to get
FM's unique ID, you could do

SELECT filemakerID FROM Table WHERE sqlID = 'known ID'

When you're synchronizing between two databases, it's a commonly used
technique to have a different unique identifier for each one that you
replicate on both sides. This has added benefits, such as the ability
to tell if a record has been synchronized (look to see if both ID
fields are populated). 

Kevin

Kevin & ursus,

Sorry, I think I created more confusion then I cleared :-)

I don't have any other DBs right now.. FM6 is the sole database
provider, I'm just writing an application to be a front end to the
database..

I threw out the @@IDENTITY from SQL server just to ask if there's
anything similar in FM ODBC, since thats basically the functionality
I'm looking for.

Andy