SQL != SQL...
-
Yes, and most things where CRUD does not work are the direct road to hell. I have seen many failed 'dynamic' SQL thingies and every time the 'creators' finally noticed that they could not swim when they were in the middle of the ocean. I'm patching up another interesting creation right now. Each table in the database has more triggers than an average piece of sh.t . Not just 'normal' triggers, if there is such a thing. Those triggers contain real application logic and also try to do everything at once, triggering even more triggers. The whole avalanche is stopped by setting special columns in the data rows. Now, I need to change a value in a primary key of one row, which usually means deleting and then inserting the row with its new key. If I do that, the wrong triggers will start triggering and everything goes to hell (GOTO is very bad). Our geniuses did an update on the data row with the new key and then the (hopefully) right triggers will take over. The problem is that I really use an ORM and updating on a new primary key value will not cause an error, but also update nothing. There hopefully is a special place in hell reserved for those people.
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns.Anyone using triggers for anything else than logging, or with any recursion whatsoever, should get publically flogged. Just a personal opinion.
Wrong is evil and must be defeated. - Jeff Ello
-
I used to live there. Don't mess with Texas :-)
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns.Saw a t-shirt once: "Don't mess with Texas, it's not nice to pick on retards" A very brave fellow I might add.
Wrong is evil and must be defeated. - Jeff Ello
-
Sander Rossel wrote:
When does the hurting stop?
Generally, the hurt lessens with each paycheck. ;) I use many different database systems. I also have to deal with the various ways of wrapping table and column names in the various databases:
[],"",``, etc. And parameter prefices:@,:. But it's Caché with is lack of operator precedence that wins the prize as worst (yes, worse than Access and Excel). :mad: Anyway... now and again I work on a technique to deal with these issues. My current technique looks a bit like this:internal enum SQL
{
[System.ComponentModel.DescriptionAttribute("Get a User record by Name")]
[PIEBALD.Attribute.SqlServerStatementAttribute
(
@"
SELECT [blah] , [blah] , [blah] FROM [UserTable] WHERE [Name]=@Param0
"
,
1 // (The number of parameters)
)]
[PIEBALD.Attribute.OracleStatementAttribute
(
@"
SELECT ""blah"" , ""blah"" , ""blah"" FROM ""UserTable"" WHERE ""Name""=:Param0
"
,
1
)]
[PIEBALD.Attribute.MySqlStatementAttribute
(
@"
SELECT `blah` , `blah` , `blah` FROM `UserTable` WHERE `Name`=@Param0
"
,
1
)]
GetUserByName// Other members as required
}This has the added benefit that it keeps all the various versions of the SQL together rather than having separate files or classes for each type of database and never knowing whether or not you are keeping them maintained properly. Then in the application, I need refer only to the enumeration members, and my framework will select the correct version of the SQL for the particular ADO.net provider in use at the moment. (Yes, I might write yet another Data Access article.) Very few applications actually need this, but it's good exercise.
All three work with the ANSI/ISO standard which is
"". MySQL have to be set in ANSI mode though. Parameter prefices is a real pain though, especially the ansi standard?which is positional only. X|Wrong is evil and must be defeated. - Jeff Ello
-
Sander Rossel wrote:
When does the hurting stop?
Generally, the hurt lessens with each paycheck. ;) I use many different database systems. I also have to deal with the various ways of wrapping table and column names in the various databases:
[],"",``, etc. And parameter prefices:@,:. But it's Caché with is lack of operator precedence that wins the prize as worst (yes, worse than Access and Excel). :mad: Anyway... now and again I work on a technique to deal with these issues. My current technique looks a bit like this:internal enum SQL
{
[System.ComponentModel.DescriptionAttribute("Get a User record by Name")]
[PIEBALD.Attribute.SqlServerStatementAttribute
(
@"
SELECT [blah] , [blah] , [blah] FROM [UserTable] WHERE [Name]=@Param0
"
,
1 // (The number of parameters)
)]
[PIEBALD.Attribute.OracleStatementAttribute
(
@"
SELECT ""blah"" , ""blah"" , ""blah"" FROM ""UserTable"" WHERE ""Name""=:Param0
"
,
1
)]
[PIEBALD.Attribute.MySqlStatementAttribute
(
@"
SELECT `blah` , `blah` , `blah` FROM `UserTable` WHERE `Name`=@Param0
"
,
1
)]
GetUserByName// Other members as required
}This has the added benefit that it keeps all the various versions of the SQL together rather than having separate files or classes for each type of database and never knowing whether or not you are keeping them maintained properly. Then in the application, I need refer only to the enumeration members, and my framework will select the correct version of the SQL for the particular ADO.net provider in use at the moment. (Yes, I might write yet another Data Access article.) Very few applications actually need this, but it's good exercise.
PIEBALDconsult wrote:
wrapping table and column names
He/She who uses spaces in table/field names should be publicly flogged! X| Even in this day and age, one of the items on my TODO list is reverse engineering a report a client sent to create a data pull from their 'new' Access 97 based inventory system and it's chock full of spaces in table and field names. :wtf: Whenever possible, I avoid unnecessary wrapping...but that's just me. :)
"Go forth into the source" - Neal Morse
-
Anyone using triggers for anything else than logging, or with any recursion whatsoever, should get publically flogged. Just a personal opinion.
Wrong is evil and must be defeated. - Jeff Ello
Same here. I built a 'don't use this for anything else than this one purpose' update method and it finally seems to work - except that now something else is not updated correctly. Turned out to be the same thing our heroes pulled off on another table. And of course more extra hours for finding this. Now it's very late, I still have a one and a half hour trip home, will not get much to eat tonight unless I pick up some greasy junk along the way and would I love to strangle that idiot that invented the technique of updating primary keys and enforcing this nonsense with countless triggers.
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns. -
Same here. I built a 'don't use this for anything else than this one purpose' update method and it finally seems to work - except that now something else is not updated correctly. Turned out to be the same thing our heroes pulled off on another table. And of course more extra hours for finding this. Now it's very late, I still have a one and a half hour trip home, will not get much to eat tonight unless I pick up some greasy junk along the way and would I love to strangle that idiot that invented the technique of updating primary keys and enforcing this nonsense with countless triggers.
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns.If there's anything worse than nested triggers, it has to be people fiddling with primary keys. You have my sympathies, and I wish your company would pay you for a decent restaurant. My old job actually had that as a rule, if you were forced to work past eight o'clock the dinner was paid for. Within reason.
Wrong is evil and must be defeated. - Jeff Ello
-
If there's anything worse than nested triggers, it has to be people fiddling with primary keys. You have my sympathies, and I wish your company would pay you for a decent restaurant. My old job actually had that as a rule, if you were forced to work past eight o'clock the dinner was paid for. Within reason.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
pay you for a decent restaurant.
Are you joking? They will cry how long it took me to find and correct it. They actually tried to tell us that we do and always have produced excellent quality - as a company guideline. If they say so, then it must be true and it must somehow be our fault when something does not go according to plan.
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns. -
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Sander Rossel wrote:
When does the hurting stop?
When you stop expecting Microsoft to conform to any standard other than what feels good to them this week, the pain will subside. Not stop, but subside some... :sigh:
Will Rogers never met me.
-
PIEBALDconsult wrote:
A
GUIDtypeSyntactic sugar. Use
myguid RAW(16) default SYS_GUID()Or rather, don't use them at all. The only serious place where GUIDs have the edge over sequences is on distributed systems.Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
is on distributed systems
They're all distributed systems. :-D
-
PIEBALDconsult wrote:
wrapping table and column names
He/She who uses spaces in table/field names should be publicly flogged! X| Even in this day and age, one of the items on my TODO list is reverse engineering a report a client sent to create a data pull from their 'new' Access 97 based inventory system and it's chock full of spaces in table and field names. :wtf: Whenever possible, I avoid unnecessary wrapping...but that's just me. :)
"Go forth into the source" - Neal Morse
I'm doing a lot of ETL/import, I see it all -- SPACEs, ASTERISKs, SLASHes, DOTs, reserved words, etc. I prefer to strike back first and wrap everything.
-
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Sander Rossel wrote:
When does the hurting stop?
When you start using NoSQL, No SQL at all :laugh:
-
Sander Rossel wrote:
When does the hurting stop
When you stop doing presentation logics in the database. I also agree with Phil, why do you need to support more than one database?
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
why do you need to support more than one database?
Because one features premium pay and the other features ubiquitous jobs?
-
If the minor differences between databases already make you cry, then please stay away from anything that has to do with browsers.
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns.CDP1802 wrote:
If the minor differences between databases already make you cry, then please stay away from anything that has to do with browsers.
But it's the minor differences that cause the subtle bugs which take the most time and require the greatest pulling of hair to resolve.
-
So what you are saying is T-SQL <> PL/SQL? :)
Mongo: Mongo only pawn... in game of life.
For some of us, "Transact-SQL" <> [Transact-SQL]. In Sybase SQL Anywhere, you can get a current date-time value using "CURRENT_TIMESTAMP", "GETDATE( )" , "CURRENT TIMESTAMP" or "NOW( )". Good luck using either "CURRENT TIMESTAMP" or "NOW( )" in Microsoft SQL Server.
-
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
When you stop writting SQL and let your ORM handle it! ;P
Eric
-
CDP1802 wrote:
If the minor differences between databases already make you cry, then please stay away from anything that has to do with browsers.
But it's the minor differences that cause the subtle bugs which take the most time and require the greatest pulling of hair to resolve.
Ok, then it should be the probabilty of a subtle bug, weighted by its severity. :-O
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns. -
Ok, then it should be the probabilty of a subtle bug, weighted by its severity. :-O
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns.Don't forget to also weight according to the severity of the motivational floggings that are part of the debugging process. ;)
-
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
-
So I've been doing Oracle development, coming from SQL Server. Simple string concatenation, which is + everywhere, is || in Oracle. A little research and || seems to be the ANSI standard, which makes sense as 2 || 'A' is now unambiguous '2A' (and not a conversion error). But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server. Oracle doesn't support + and SQL Server doesn't support ||, however both support CONCAT. Seems too easy for something that's uneasy already, and indeed it is... SELECT CONCAT('A', 'B') FROM TABLE works in Oracle and SQL Server. SELECT CONCAT('A', 'B', 'C') FROM TABLE works only in SQL Server... Seems like the only thing that works in both databases is CONCAT('A', CONCAT('B', 'C')). And that seems like the only reasonable solution is to write two different queries, one for Oracle and one for SQL Server because it's just too friggin difficult to implement a standard FRIGGIN STRING CONCATENATION!!! X| When does the hurting stop? :((
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
Sander Rossel wrote:
But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server.
Why is that important? If you're running some kind of application to interface with the database, seems like the call to the database should just invoke a sproc (for example--maybe a query, whatever). You have the same named sproc on two instances, but they work differently. The code layer is effectively calling an interface (i.e., "whatever I'm connected to, execute the 'selectMyStuff' sproc"), and each database is the concrete implementation of that interface. This is a simple example, but what if the databases had completely different structures? You wouldn't expect to deploy the same SQL to both, you'd have to write custom procedures which happened to take the same parameters and return the same result set (i.e., implement the 'interface'), even though they perform that operation in significantly different ways.
-
Sander Rossel wrote:
But now I want to write a simple SELECT statement which would work in both Oracle and SQL Server.
Why is that important? If you're running some kind of application to interface with the database, seems like the call to the database should just invoke a sproc (for example--maybe a query, whatever). You have the same named sproc on two instances, but they work differently. The code layer is effectively calling an interface (i.e., "whatever I'm connected to, execute the 'selectMyStuff' sproc"), and each database is the concrete implementation of that interface. This is a simple example, but what if the databases had completely different structures? You wouldn't expect to deploy the same SQL to both, you'd have to write custom procedures which happened to take the same parameters and return the same result set (i.e., implement the 'interface'), even though they perform that operation in significantly different ways.
Yeah, normally I'd do that, but this time I'm generating the query client side :) Anyway, it's not all that important, I should abstract away such stuff and implement it for each database anyway. I was just amazed that something so simple can't be done uniformly by two of the biggest databases that both work with the same language that has an ANSI standard...
Visit my blog at Sander's bits - Writing the code you need. Or read my articles at my CodeProject profile.
Simplicity is prerequisite for reliability. — Edsger W. Dijkstra
Regards, Sander
