How to use MSDE(SQL Server) as GBPVR Database

[KingArgyle]

You should still be able to use aliases with SQL Server. Every program I've written for SQL Server has always used alias names.

[sub]

trying to shoehorn a numeric into an int variable

An int varible is pretty much how we hold this data type in C#. For these identity fields I dont need to accommodate null, so I dont need to use an object to represent its value.

You could change programOID to be an System.Int32

This is effectively the equivalent of what I'm doing. Other than one being a primitive and the other being an object, both can represent negative 2,147,483,648 through positive 2,147,483,647. I dont believe this is the cause of the problem.

When you manually run the insert, then do a select @@identy, what value is returned?

[Darryl]

To be honest, I use this everywhere, and the idea of changing doesnt sound like fun. I originally picked up this syntax from SQL Server 6.5, so I'm surprised its no longer used. It also works in fine Oracle. It was particularly useful for when the subquery contained the same table as the outer query - using the alias would make it clear exactly which one you were talking about.

Looking deeper at the SQL syntax indicates that this can be used for SELECT statements as long as the 'AS' keyword appears between the table name and the alias.

eg: Select * from PROGRAMME AS P where p.oid = 100


However, it doesnt work in the simple DELETE statement.

eg: Delete from PROGRAMME as P where p.oid = 100


You CAN use aliases where referencing another select during the delete.


FROM <table_source>

Specifies an additional FROM clause. This Transact-SQL extension to DELETE allows you to specify data from <table_sources> and delete corresponding rows from the table in the first FROM clause.

This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.

table_name [[AS] table_alias ]
Is the name of the table to provide criteria values for the delete operation.

eg: DELETE from authors FROM (SELECT TOP 10 * FROM authors) AS t1
WHERE authors.au_id = t1.au_id

In Summary, the aliases can be used in SELECT statements with the 'AS' keyword, but not in simple delete statements.

Darryl

[sub]

My understanding is the "AS" is optional. Its even shown that way in the Microsoft, including your cut & paste above, with [AS] being represented in square brackets meaning it is optional.

[Darryl]

An int varible is pretty much how we hold this data type in C#. For these identity fields I dont need to accommodate null, so I dont need to use an object to represent its value.

This is effectively the equivalent of what I'm doing. Other than one being a primitive and the other being an object, both can represent negative 2,147,483,648 through positive 2,147,483,647. I dont believe this is the cause of the problem.

When you manually run the insert, then do a select @@identy, what value is returned?

A numeric data type is larger than the integer data type.

Integer
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

Numeric
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.


While the value returned is within the integer range ie 17620, its in a numeric data type. The code says to squash the numeric data type (9 bytes) into an integer (4 bytes)


In my case, the @@IDENTITY query returns 17620.

Looks like it needs to be bigger than Int32...


double dblprogramOID = System.Decimal.ToDouble(returnObject);
int programOID = dblprogramOID


Darryl

[Darryl]

My understanding is the "AS" is optional. Its even shown that way in the Microsoft, including your cut & paste above, with [AS] being represented in square brackets meaning it is optional.

Just to clarify things..


Query Analyser will not accept these.

DELETE from PROGRAMME as p where oid > 0
> Incorrect syntax near the keyword 'as'.

delete from PROGRAMME p where oid > 0
> Incorrect syntax near 'p'.

It will accept these..

delete from PROGRAMME where oid > 0

Select * from PROGRAMME as P where oid > 0

Select * from PROGRAMME P where oid > 0


It appears that the DELETE command cannot have table aliases unless there is a sub query to select rows to be deleted.


Darryl

[sub]

A numeric data type is larger than the integer data type.

Integer
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

Numeric
Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.

Yes, it can potentially be bigger than an integer, but from what I knew about the way I use data I felt safe that I would never get anywhere near an identity greater than 2^31 - 1. I asked you want SQL Server is generating for its identity on the off-chance it uses some seed values that causes it to start counting at some incredibly high number (which I wouldnt expect).

While the value returned is within the integer range ie 17620, its in a numeric data type. The code says to squash the numeric data type (9 bytes) into an integer (4 bytes)

In my case, the @@IDENTITY query returns 17620.

Looks like it needs to be bigger than Int32...

No, that is not correct. There is no problem with using the method I mentioned in my earlier to retrieve this numeric 17620 into a 32 bit integer. It would have been a problem if it had been larger than 2^31 - 1.

[sub]

Query Analyser will not accept these.

DELETE from PROGRAMME as p where oid > 0
> Incorrect syntax near the keyword 'as'.

delete from PROGRAMME p where oid > 0
> Incorrect syntax near 'p'.

It will accept these..

delete from PROGRAMME where oid > 0

Select * from PROGRAMME as P where oid > 0

Select * from PROGRAMME P where oid > 0

It appears that the DELETE command cannot have table aliases unless there is a sub query to select rows to be deleted.

If you can provide the equivalent SQL for any of these you find in your testing, I'm happy to change them in my next release.

[Darryl]

I've just read back over the thread and I've defined 2 separate problems that are not related and I've mixed them up together and confused everyone including me.

My apologies for that.


The

"delete from PROGRAMME p ..." Incorrect syntax near 'p'.

error can be resolved by removing the 'p' altogether. It has nothing to do with the @@IDENTITY code.


The 'invalid cast' error also has nothing to do with the @@IDENTITY code. That looks to be something else do do with storing channels.


Darryl

[sub]

No, I cant do that. You cant gaurantee no other records have been inserted between the original insert and the select (without adding transaction control around every occurance of this). Its pretty unlikely to occur, but is possible. Also, @@identy was really intended to be used exactly the way I'm using, and avoids this potenial problem.