The reason for this is historical and is because Microsoft SQL Server is a derivative work of Sybase SQL Server and their TRANSACT-SQL language. Sybase was the first (I believe) SQL-based RDBMS that supported a complete and ONLY dynamic execution model for SQL statements. All other DBMS systems at the time were either navigational or required that the SQL statements be pre-compiled into the host application, usually via EXEC SQL macro's of the like.
This was because of the time and computation required to prepare the execution plan for the SQL and efficiency. While most SQL RDBMS more-or-less supported dynamic execution of SQL (as in pass the SQL statement at execution time and prepare the statement at that time, it was very very very inefficient). Sybase TRANSACT was designed from the get-go to be able to execute SQL Statement blocks (not a single statement but a block of statements, that would otherwise be part of a pre-compiled EXEC SQL block) dynamically by preparing the plan at runtime -- though it did also support the standard pre-compiler EXEC SQL preparation syntax -- but converted that into the dynamic equivalent code performed at runtime rather than the normal pre-prepare and plan binding). With the introduction of OS/2 EE IBM added the necessary plumbing to support SNA on their version of IBM OS/2 Extended Edition so they could also port Communications Manager and their mainframe DB2 product to OS/2. The "Extended Edition" enhancements were not covered by the JAD and were not included in the generic version of OS/2 marketted by Microsoft as "Microsoft OS/2". Not wanting to be left out, Microsoft entered into a technology agreement with Sybase to port the Sybase Database engine (which at that time only ran on Unixlike systems) to MS OS/2. Thus was born "Microsoft SQL Server". The core "OS/2" was covered by the JAD between Microsoft and IBM and was the same. IBM had "IBM OS/2 Extended Edition" which included integration with other IBM products and included DB/2. Microsoft had "Microsoft OS/2" and kept feature parity by adding "Microsoft SQL Server" to end up with an equivalent offering for shops that did not or want to integrate with IBM Big Iron. Under the IBM and Microsoft JAD work began on creating the next version of OS/2, OS/2 3.0, which would be based on a different memory model. Somewhere around beta 2 of OS/2 3.0 Microsoft and IBM had a major disagreement regarding "Windows" support, the WIMP interface that ran overtop of the old MS-DOS operating system that OS/2 was being built to replace. IBM believed that OS/2 should use a new Object-based gooey layer, and Microsoft wanted to use the "Windows" interface that they had been so successful at giving away in boxes of cornflakes and such. IBM thought that Windows, being merely a WIMP layer on top of DOS (which it was in all versions Microsoft ever released) belonged in the "penalty box" (the DOS emulation layer inside OS/2). This split and terminated the JAD between Microsoft and IBM. Microsoft took the OS/2 3.0 Beta 2 code and generated their OS/2 New Technology. The "New Technology" part was considered to be a bit to long, so Microsoft shortened it to NT, replaced the Presentation Manager with with Windows layer, added the "Windows Subsystem" and released it as Windows NT. IBM took the OS/2 3.0 Beta 2 code, changed the UI interface to their new Workplace Shell, and released an IBM version of OS/2 which eventually became OS/2 WARP. The IBM side kept all the proprietary IBM stuff and the DB2 database. The Microsoft side kept the common codebase and it became Windows NT. As part of the JAD separation Microsoft was required to replace all the proprietary IBM technology with their own, which Microsoft did by calling it "ActiveX" (Active this and Active that) re-writing those bits. Somewhere in all the splitting up and divorce proceedings between Microsoft and IBM there was also a split between Microsoft and Sybase. Up until "Windows NT" Microsoft had been getting codebase updates from Sybase which kept "Microsoft SQL Server" and "Sybase SQL Server" pretty much at feature parity. This also split at the time the new "Windows NT" product and the Sybase and Microsoft "SQL Server" products started to diverge, however both retained the common TRANSACT-SQL underpinnings. What, you may wonder, does this have to do with support for "local variables" in MS SQL Server? Well, as I mentioned at the beginning TRANSACT does not execute single SQL Statements, it dynamically executes EXEC SQL statement blocks (TRANSACT-SQL) which may consist of MULTIPLE SQL STATEMENTS which are dynamically prepared and executed as a single statement at the server. Compare this to SQLite3 which executes only SINGLE STATMENTS locally. Therefore in TRANSACT you may have a statement block thus: "declare @v char select @v=CustName from customers where custno=:custno update onhand set qty=qty+1 where custname=@v" where the variable @v is local to the block and two sql statements are executed together in the same plan on the server. Oracle has a similar capability to execute statement blocks that they call PL/SQL that execute on the server. DB2 requires that you declare an EXEC SQL block (what is called a stored procedure) to do the same thing on the server. In SQLite3 each statement executes within its own local context and there is no functionality to provide a "context wrapper" which would allow for context (server side) variables. This makes perfect sense because SQLite3 has no server in which to create such a context -- your appplication is the server and you can create local variables in your application programming language to your own hearts desire. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Luuk >Sent: Saturday, 19 January, 2019 06:49 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Variable Declaration > > >On 19-1-2019 14:23, Jesse Rittner wrote: >> What language do you want to use? Parameter binding is a feature of >the >> SQLite C API. So pretty much any language with a C FFI will >suffice. There's >> a list on Wikipedia, not sure how authoritative it is. >> https://en.wikipedia.org/wiki/SQLite#Programming_language_support >(Note: >> When it says they "provide bindings for SQLite", it's referring to >the FFI, >> not parameter binding.) >> > >This question is not about: 'parameter binding'! > >It's about 'variable decalaration'... > > >In MS-SQL you can do things like: > >C:\temp>PSQL.EXE -E -S SQL2017DEV > >1> declare @count int = 10; >2> select * from test where i<=@count >3> go > i > ----------- > 1 > 2 > 3 > 4 > 5 > 6 > 7 > 8 > 9 > 10 > >(10 rows affected) >1> > > > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
