SQL & how different databases implement it

Error Type:
OraOLEDB (0x80004005)
?B8
/nar/nvCreateaccount.asp, line 105

This error was caused by assigning a null value to a stored procedure input variable.


OraOLEDB error '80004005'
$?

This error was caused by setting a procedure output parameter as Variant - should be VarChar, for example:
objCommand.parameters.append objCommand.createparameter( "p_device_id", adVarChar, adParamOutput, 24)

Have you received the following error:

Error Type:
OraOLEDB (0x80040E4B)
Accessor is not a parameter accessor.

This is from an ASP application running against an Oracle database. Here is how I fixed this problem in my application:

The problem was due to excessive digits in a number being returned by an Oracle SQL SELECT statement. To fix it, I changed the SQL statement so that it used the Oracle ROUND function to remove some of those digits, e.g.:

change:

SELECT fees / days
FROM timesheet

to:

SELECT ROUND(fees / days, 3)
FROM timesheet

Version 1.5 of SQL Developer is now in production. Download it for free from
http://www.oracle.com/technology/products/database/sql_developer/index.html

Oracle has recently made available an "early adopter" version of the upcoming version of SQL Developer v1.5

Click on the link to go to the Oracle page from which you can find the download.

I have noticed a lot of activity on my posts to do with ADODB SELECT statements in ASP VBScript code. So here is some more information, this time about executing stored procedures.

ORACLE STORED PROCEDURES

Let's run an Oracle stored procedure and extract the data from the OUT variable. This post does not teach you about Oracle PL/SQL programming, but the head of the stored procedure we are going to run is:
CREATE OR REPLACE PROCEDURE sp_create_device
(p_ingame_name IN VARCHAR2,
p_status IN VARCHAR2,
p_device_id OUT VARCHAR2
)
IS
.....bulk of stored procedure here....

The remainder of this post is about the ASP code you need to execute the above stored procedure. First just a bit of setup to create the ADODB objects (these can be put in an include file at the top of all your ASP scripts):
set objConn = server.CreateObject("ADODB.Connection")
objConn.Open (application("gc_db_conn"))
set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConn

In the above, replace application("gc_db_conn")which I have defined in my global.asa with the connection string for your database. More information setting up a connection string here. Now identify the stored procedure to execute:
objCommand.CommandText = "sp_create_device"
objCommand.CommandType = adCmdStoredProc

Next, for each of the stored procedure's variables as defined in the stored procedure itself (our procedure sp_create_device here has three variables, two of which are IN variables and one of which is an OUT variable), define the variables:
objCommand.parameters.append objCommand.createparameter("p_ingame_name",adVarChar,adParamInput, 20)
objCommand.parameters.append objCommand.createparameter("p_status", adVarChar,adParamInput, 25)
objCommand.parameters.append objCommand.createparameter("p_device_id", adVarChar,adParamOutput,24)

For the avoidance of doubt, I know the above will probably wrap on your screen, but there are THREE lines in the above bit of code, one for each parameter. Notice how each parameter is identified by name (the names are in the code taken from the stored procedure), the data type is defined, the direction (in or out) is defined and the length of the variable is specified.

Now let's assign a value to each of the input variables (there are only two IN variables, so only two of the parameters need to be given a value):
objCommand("p_ingame_name") = "ghast"
objCommand("p_status") = "forced offline"

Finally now execute the stored procedure and this allows us to return (extract) the result:
objCommand.Execute
v_device = objCommand("p_device_id")

The VBScript variable v_device now contains the value that the stored procedure put into its p_device_id variable.

Please note that no error processing / handling has been included in the above. But I know you know that!

Good luck. Feel free to drop me a line if you need any help.

Imagine you write a SELECT statement with a UNION or UNION ALL in it with each subselect having a GROUP BY clause, then add an ORDER BY clause to present the results set in a particular order. How do you construct the ORDER BY clause so that the data is presented in order of the group function or aggregate function?

Let me take this one step at a time.

Take a simple SELECT statement with a GROUP BY and an ORDER BY:

SELECT a_status, COUNT(*) AS cnt
FROM device
GROUP BY a_status
ORDER BY cnt

That is pretty straightforward. You could also write the ORDER BY as:
ORDER BY 2
or even:
ORDER BY COUNT(*)
and the statement will work as expected.

Now let's add a UNION ALL:

SELECT a_status, COUNT(*) AS cnt
FROM device
GROUP BY a_status
UNION ALL
SELECT b_status, COUNT(*) AS cnt_b
FROM device
GROUP BY b_status
ORDER BY cnt

This works fine. But if you change the ORDER BY clause in the previous statement so that it reads:
ORDER BY cnt_b
both Oracle and SQL Server will give error messages. In Oracle:
SQL Error: ORA-00904: "CNT_B": invalid identifier
In SQL Server:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'cnt_b'.
Msg 104, Level 16, State 1, Line 8
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Now change the ORDER BY clause to read:
ORDER BY COUNT(*)

In SQL Server, the statement will work just fine. In Oracle the statement will give an error message.
SQL Error: ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Is this an important difference in behaviour? Almost certainly not. It has never been advisable or correct to specify an aggregate function in an ORDER BY clause. The developer should have given the aggregate function the same name in both select lists of the UNION (i.e. both COUNT(*)'s should have been called cnt). Or they should have specified the name of the select list item taken from the first part of the UNION. Or alternatively the numeric method of specifying an ORDER BY could have been used.

Did you know that in order for a user of Microsoft SQL Server to INSERT data into a table, or to UPDATE or DELETE existing rows, not only does that user need the appropriate INSERT, UPDATE or DELETE privilege to be GRANTed to them, but also the SELECT privilege too.

This is not the case for other DBMSs. To INSERT data into an Oracle table, you only need the INSERT privilege. Take an example of an audit table which is just recording rows that contain information about the actions users are performing in the application. It seems entirely reasonable to restrict the database user associated with the application to just INSERTing data - that is all that it is required to do. Increasing the privileges of this user by also GRANTing SELECT privilege decreases application security.

This also highlights an important aspect of designing application security. The database user that is being used by the application to access the tables in the database should not be the owner of those tables, but instead be a specific user that is only granted the appropriate SELECT, INSERT, UPDATE and DELETE privileges. Do not grant all of these privileges on all of the tables in the database - be selective and you will increase application security by reducing the scope of what can go wrong if the application is abused or is in error.