Can someone clarify why only a single-statement can be executed in a command?

I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.

I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time

For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition

Answer this question

Can someone clarify why only a single-statement can be executed in a command?

Zadoras
I haven't tried, but the documentation (Mobile Edition Books Online) seems pretty clear:

The data provider for SQL Server Mobile has the following limitations:
- No support for batch queries. Queries must be a single SQL statement. For example, the following statement is valid:
  Copy Code
  
  SELECT * FROM Customers
  This statement is not valid:
  Copy Code
  
  SELECT * FROM Customers; SELECT * FROM Customers2
I'd be using parameter substitution, not string catenation, so I'm not worred about SQL injection attacks. I'm just talking about logically enforcing my data model in as easy and bullet proof way as possible. Today I can write a stored procedure which implements a high level operation like "purchase" (which debits one account, credits another, and adds a detail record to another table); its a very basic, primitive, operation and its silly to enforce the integrity of this operation with lines and lines of OLEDB code that begins a transaction, and issues each statement, and hopefully issues the rollback if anything fails. With a single batch submitted to the backend dbms, this is all handled trivially, and robustly. I'm trying to figure out if SQL Everywhere can aproach this. But by looking at books online, it seems it cannot.... no support for stored procedures or multi-statement batches issued by an application.
murratore
Have you tried it at all

From my understanding alot of database servers limited the execution of multiple sql statements because you would end up with people doing stuff like "select * from tablename where column='" + value + "'" and then you would have people that would inject sql into value and they sould turn it into value ='"; DELETE * FROM TABLENAME ".

I think in most cases where multiple statements are executed - perhaps you can use joins I'm really not sure. For the most part I try to use stored procedures where necessary - But that's just my personal preference. Hope this hielps.
Jim Keifenheim

Hi MarcD & Shaun,

I would like to clarify the positioning of SQL Server Everywhere Edition (SSEv) against other Microsoft SQL Server products such as SQL Server Standard/Enterprise Edition (SQL EE), SQL Server Express Edition (SQL XE).

SSEv is for small apps where you need basic data store and querying capabilities and not stored procs, sql batching ... etc. If you need these rich/advanced capabilities we would recommend you to use SQL XE.

SSEv is not a redundant product when compared to SQL XE. Each caters to different customer needs.

I hope I have not disappointed you by this post but thats the detail I can give for now :(

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation
Binny
Right but its not the fact that you will use it - its the fact that many people wouldn't.

IMHO if you need to use batched sql statemnents that are reliant on a state of the prior sql statement you should use stored procedures. (unless of course it isn't supported)
AlexBB
Hi Laxmi,

We have recently come up against this problem in an application we are developing. Our problem is that we are developing an application with online/offline capability. The online version uses SQL Server and the offline uses SQL Server CE 3.5 Beta 2. CE's limitations mean that it is not easy for us to develop a shared set of business objects. We can get around not using stored procedures, although not ideal, but the inability to execute batch statements means that we cannot separate the data layer without also having to change the structure of the business objects. The specific problems are

1. We load our root object by populating a single dataset from a sql batch query returning multiple result sets. The dataset is then used to populate the child/grandchild objects. We cannot do this in CE.

2. We use a lastUpdated field to support optimistic concurrency. This field is returned after every update as an output parameter. We cannot get this field atomically using CE meaning we may lose data if the app synchronizes while the user is performing some action.

I'm sure we can get around these problems but the situation is not ideal and will result in a lot of duplicated code. Are batch statements (and stored procedures) planned for a future version of CE, or is the limitation more a functional choice to create a separation between the various versions of SQL Server With the pending release of synchronization services for ADO.NET I can see this problem occuring a lot more.

Regards,

Paul.
Can someone clarify why only a single-statement can be executed in a command?

Answer this question