SQL :: Variable declaration in queries

[OgRo]

hello there



In my work we are using variables as the parameters for our queries in a microsoft sql server database.

There we work using queries such as:

Code: Select all

DECLARE @YEAR as INT
SET @YEAR = ?
-- the "?" thing is because we are working with Java's prepared statements

SELECT TOP 10, * FROM TABLE_NAME WHERE YEAR = @YEAR
-- the query itself comes here

The problem arrived when we decided to move our development platform from windows to linux. But there is a problem with that: our client's database will still be Microsoft SQL Server and then our code must work in both servers.



Is there a standard way (ie, ANSI SQL) for variable declaration in queries? If not, do you know another (better) way for accomplishing what we need?

By the way: using hybernate or any other mapping is not desired because we don't need object mappings. We need to receive a table-like structure. I know hybernate has it own query language and I'd love to use it, but I have no idea on how to extrat the data without mapping it to an object (I did research on this matter and I found nothing).



And please, don't RTFM me. I already did it and I couldn't find anything usefull. Also, I'm posting this here because this is the best forum about anything related to computers that I know and because we are going to use gentoo linux in our server.

[dev_zero]

I'm not savvy in sql at all, but i believe microsoft never uses any form for standards, so the syntax will not be the same. However coudn't you move to mysql? I think the mysql version of the above would be something like:

Code: Select all

DECLARE YEAR INT
SET YEAR = ?
SELECT * FROM TABLE_NAME WHERE YEAR = YEAR LIMIT 10

But i guess this will be too much work to convert to a different sql server

[timeBandit]

Speaking from long experience, I can promise you the only way to maintain portable SQL over the long term is to test on every platform where you need to run. There's no reason not to develop on Linux end-to-end (including the database server), but if your clients run SQL Server on Windows it's unwise (foolish, really) not to keep a SQL Server machine in-house. No one wants the client to be the first to see portability bugs (unless gaining a reputation for mediocrity is in the business plan ).

You're using JDBC, right? Regarding your example, what does the SQL variable give you over a simple replaceable parameter? Such as:

Code: Select all

SELECT * FROM TABLE_NAME WHERE YEAR = ?

If you set the variable so that it can be referenced multiple times in the query, while only setting a single replaceable parameter...sorry, you'll have to sacrifice that to portability. Use a JDBC parameter in the prepared statement everywhere the SQL variable appeared and set the same value into each occurrence.

As for the row return limit, I don't know a portable way to achieve that in SQL. To do that in my experience we always count rows and close the result set at the limit.

[OgRo]

Thanks for your answers.


dev_zero: In fact, the way variables work in MySQL is just like in SQL Server, except for the DECLARE part (which is not allowed in the Query context in MySQL).

timeBandit: you are right, it's not advisable not to maintain the same server the client has in your production environment, but using some strong and well tested standard should be enough to ensure it's working fine.

We could also develop using MySQL but still have a SQL Server for testing. This way we could purchase per user license for only 1 user in SQL Server and we'd have a way for making sure our product is compatible with the client's infrastructure.

[timeBandit]

[S]ome strong and well tested standard should be enough to ensure it's working fine. We could also develop using MySQL but still have a SQL Server for testing. This way we could ... [be] sure our product is compatible with the client's infrastructure.

Exactly, that's all I'm advising. Besides the small differences between the database vendors' SQL dialects, the quality and capabilities of their JDBC drivers is a factor. Even if you and I conform to standards, we must be aware of where the vendors didn't , if we expect our products to work.