1

I made a big research before asking this, I really did.

I have code written in Transact-SQL (SQL server accepts every piece of code, and it works fine), and I was about to transfer it to my JavaFX application, which uses H2 database. Tables were created fine. Stored Procedures were not. Is there a database, in which I can send Transact SQL query (via Connection-Statement- statment.execute( query:"MyQuery")), as I did in SQL server, and have an embedded copy of that database, which I created via SQL server? So triggers, functions and SP's, which work in server were accepted for good? Thank you.

Michael.Medvedskiy
  • 247
  • 5
  • 8
  • Since Transact-SQL is Microsoft's and Sybase's proprietary extension to SQL, unless you find an embedded db from Microsoft or Sybase (or someone who would have licensed it and created an embedded db), you're going to be out of luck. Perhaps it wasn't such a good choice to go with T-SQL in the first place? – Kayaman Jan 06 '18 at 19:38
  • So, if i have the .msd "MyTsqlDB" file, is there a way I can embed it in my Java project, so I could query it? (I was trying to do it for a couple of days, but no luck also) – Michael.Medvedskiy Jan 06 '18 at 19:41
  • No, it's not embeddable in the same way as H2 or SQLite. – Kayaman Jan 06 '18 at 19:43
  • Well, as tilting as it may sound, I think, I am willing to re-write my code to H2-acceptance, It was told on their site, that it supports triggers, Functions and SPs. But there literally are no good tutorials were caught in my sight. Could you please link it? (Or if it just uses another language, name it) – Michael.Medvedskiy Jan 06 '18 at 19:48
  • Well keep looking, I'm not going to start Googling on your behalf. – Kayaman Jan 06 '18 at 19:51

1 Answers1

1

H2 stored procedures are more like java functions that you can execute inside of your query.

Here's an example of H2 stored procedures in action.

A better example of how to use H2 procedures. May be duplicative of this Q.

Another idea, if H2 is required, and depending on the complexity of your stored procedures, you could save the procedure content (the SQL text inside the procedure) as a string and then just execute that string in H2, you could name the variable String storedProcedure to feel better about it...

For more SQLish stored procedures e.g: CREATE PROCEDURE blah... you could try HSQLDB, another embedded java database. It was originally created by the same person as H2 I believe.

Here is the HSQLDB guide for supported database objects. It includes triggers, functions, stored procedures etc, with normal SQL create statements.

This section covers stored procedures specifically.

egerardus
  • 11,316
  • 12
  • 80
  • 123