The Lounge - CodeProject

William Clardy9-Oct-15 1:48

9-Oct-15 1:48

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.

Re: SQL != SQL...

Marc Clifton8-Oct-15 2:37

Marc Clifton

8-Oct-15 2:37

That's why I use a SQL builder.

SQL - it's not Structured, it's not just Query, and it's not a Language.

How the f*** did it get that acronym?

Marc

Imperative to Functional Programming Succinctly

Contributors Wanted for Higher Order Programming Project!

Re: SQL != SQL...

Jörgen Andersson8-Oct-15 3:16

Jörgen Andersson

8-Oct-15 3:16

It is indeed just Query, the rest is DDL[^] and DML[^]
The other two I'll pin down as opinions. Smile | :)

Wrong is evil and must be defeated. - Jeff Ello

Re: SQL != SQL...

Sander Rossel8-Oct-15 5:33

Sander Rossel

8-Oct-15 5:33

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

Re: SQL != SQL...

Eddy Vluggen8-Oct-15 2:58

Eddy Vluggen

8-Oct-15 2:58

SQL92 ftw

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: SQL != SQL...

kmoorevs8-Oct-15 3:02

kmoorevs

8-Oct-15 3:02

I had fun with SQL SUBSTRING the other day...WTE is it 1 based? Laugh | :laugh:

"Go forth into the source" - Neal Morse

Re: SQL != SQL...

PIEBALDconsult8-Oct-15 4:50

PIEBALDconsult

8-Oct-15 4:50

Because CHARINDEX is? Big Grin | :-D

modified 8-Oct-15 19:19pm.

Re: SQL != SQL...

PIEBALDconsult8-Oct-15 4:49

PIEBALDconsult

8-Oct-15 4:49

Sander Rossel wrote:
When does the hurting stop?

Generally, the hurt lessens with each paycheck. Wink | ;)

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 | :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.

Re: SQL != SQL...

Sander Rossel8-Oct-15 5:32

Sander Rossel

8-Oct-15 5:32

PIEBALDconsult wrote:
Very few applications actually need this, but it's good exercise.

And fun!

Unless, of course, you need to get things done quick... Unsure | :~

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

Re: SQL != SQL...

Jörgen Andersson8-Oct-15 6:22

Jörgen Andersson

8-Oct-15 6:22

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. Dead | X|

Wrong is evil and must be defeated. - Jeff Ello

Re: SQL != SQL...

kmoorevs8-Oct-15 7:12

kmoorevs

8-Oct-15 7:12

PIEBALDconsult wrote:
wrapping table and column names

He/She who uses spaces in table/field names should be publicly flogged! Dead | 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 | :WTF:

Whenever possible, I avoid unnecessary wrapping...but that's just me. Smile | :)

"Go forth into the source" - Neal Morse

Re: SQL != SQL...

PIEBALDconsult8-Oct-15 13:00

PIEBALDconsult

8-Oct-15 13:00

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.

Re: SQL != SQL...

Roger Wright8-Oct-15 12:43

Roger Wright

8-Oct-15 12:43

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 | :sigh:

Will Rogers never met me.

Re: SQL != SQL...

Brittle16188-Oct-15 18:49

Brittle1618

8-Oct-15 18:49

Sander Rossel wrote:
When does the hurting stop?

When you start using NoSQL, No SQL at all Laugh | :laugh:

Re: SQL != SQL...

Eric Whitmore9-Oct-15 1:58

Eric Whitmore

9-Oct-15 1:58

When you stop writting SQL and let your ORM handle it! Poke tongue | ;-P

Eric

Re: SQL != SQL...

Gary Huck9-Oct-15 3:13

Gary Huck

9-Oct-15 3:13

Sounds like a good time for some dynamic sql. I know lots/most folks dis the idea of such, but it certainly has its place imho.

Re: SQL != SQL...

Sander Rossel9-Oct-15 10:44

Sander Rossel

9-Oct-15 10:44

Especially when you know how it works[^] Smile | :)

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

Re: SQL != SQL...

agolddog9-Oct-15 4:01

agolddog

9-Oct-15 4:01

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.

Re: SQL != SQL...

Sander Rossel9-Oct-15 10:43

Sander Rossel

9-Oct-15 10:43

Yeah, normally I'd do that, but this time I'm generating the query client side Smile | :)

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

MQOTD

V.7-Oct-15 21:59

7-Oct-15 21:59

Movie Quote Of The Day

I always felt that the old Bond films were only as good as the villain. As a child, I rather fancied a futuristic colorful megalomaniac.

Which movie?

V.

(MQOTD rules and previous solutions)

Re: MQOTD

OriginalGriff7-Oct-15 22:12

OriginalGriff

7-Oct-15 22:12

The Donald Trump Story?

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Re: MQOTD

RUs1237-Oct-15 22:18

RUs123

7-Oct-15 22:18

This isn't that kind of movie.

Re: MQOTD

den2k887-Oct-15 22:24

den2k88

7-Oct-15 22:24

Iron Man

GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- ++>+++ y+++* Weapons extension: ma- k++ F+2 X

If you think 'goto' is evil, try writing an Assembly program without JMP. -- TNCaver

"When you have eliminated the JavaScript, whatever remains must be an empty page." -- Mike Hankey

Re: MQOTD

Brittle16187-Oct-15 23:53

Brittle1618

7-Oct-15 23:53

V. wrote:
good as the villain

The ain't always that bad, the Villians.

Re: MQOTD

HobbyProggy8-Oct-15 0:00

HobbyProggy

8-Oct-15 0:00

Inception : James Bond watches James Bond with James Bond in a future past / past future scenario doing his jobs but doing it somehow diffrent and crashing the whole universe because the villian was bad.

Rules for the FOSW ![^]

if(this.signature != "")
{
    MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
    MessageBox.Show("404-Signature not found");
}

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Welcome to the Lounge