locked
How can I get the file_id of the last file uploaded into database?Details inside RRS feed

  • Question

  • User-1538122812 posted

    Hello guys,

    I am building a website for managing contracts in a company.In my oracle database,I am having a table called "contracts" with the primary key "contract_id", a table called "uploads" with the primary key "file_id", and a table called "contracts_uploads" with a composite primary key of "contract_id" and "file_id".Every contract can have many uploaded files assigned to it.My question is : When I am uploading a file through the interface,I need to get the "contract_id" for the current contract which I can easily get,and I need the "file_id" and insert them into "contracts_uploads".The "file_id" should be the last "file_id" incremented by one.So how can I know that if multiple users are using the site to upload files in the same time.

    Hope you give me an idea about how to do that.

    Thank you very much.

    Wednesday, April 11, 2012 4:05 AM

Answers

  • User1859182686 posted

    I think I get what you want - I'm just approaching it differently.

    You say 

    my app will get the max (file_id) of uploads and increment it
    . Well instead of your app doing the work, let the database do it.

    Create a stored procedure as mentioned and insert the record into your table with an IDENTITY column, you can then set an output parameter to contain the IDENTITY value using SCOPE_IDENTITY if your application needs it.

    That way, the end result is the app knows the file_id and the record has been safely inserted into the database. It also saves an unnecessary  MAX query to your database to get the value to increment.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 12, 2012 1:09 AM
  • User1013750657 posted

    hi

    in oracle you should create a sequence. sequences have same behavior as kidshaw is descriving in SQL.

    if you are using a sequence , you can then use your_sequence.curr_value and your_sequence.next_value.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 14, 2012 9:58 AM

All replies

  • User1859182686 posted

    Can you not use an IDENTITY field instead? You can then wrap the insert in a Stored Procedure and return the file-id back to your app as an output parameter?

    I am a SQL Server guy rather than Oracle so not sure if the terminology crosses over.

    Wednesday, April 11, 2012 4:17 AM
  • User-1538122812 posted

    I am not sure if you got my question right.My problem is not how to get the file_id into the app,but here is the situation : imagine a user is trying to upload a file,then my app will get the max (file_id) of uploads and increment it by one to get the next file_id,but if there were another user using the website and tried to upload a file before the first user completed the upload,then they will be getting the same file_id,am I right?There will be the problem.So how to make sure every user gets a unique file_id?

    Thanks for your cooperation.

    Wednesday, April 11, 2012 4:50 AM
  • User1859182686 posted

    I think I get what you want - I'm just approaching it differently.

    You say 

    my app will get the max (file_id) of uploads and increment it
    . Well instead of your app doing the work, let the database do it.

    Create a stored procedure as mentioned and insert the record into your table with an IDENTITY column, you can then set an output parameter to contain the IDENTITY value using SCOPE_IDENTITY if your application needs it.

    That way, the end result is the app knows the file_id and the record has been safely inserted into the database. It also saves an unnecessary  MAX query to your database to get the value to increment.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 12, 2012 1:09 AM
  • User1013750657 posted

    hi

    in oracle you should create a sequence. sequences have same behavior as kidshaw is descriving in SQL.

    if you are using a sequence , you can then use your_sequence.curr_value and your_sequence.next_value.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 14, 2012 9:58 AM