Do not use Windows Backup!

At the time we speak this application is FUBAR, or in other words "fucked up beyond all repair".

Why?

• I never saw a completely successful backup, neither on my Vista Box nor on my Windows 7 box.
• You only waste hours or days running backups that are never successful, scanning your disk with chkdsk of sfc, searching the net, searching the registry, screwing up your systems registry, or formatting your disk.
• The errors are just cryptic hexadecimal numbers like 0x80070057, 0x80070570 without any reference to the cause of the error.

When I first started Windows Backup on Windows 7 I kept getting error 0x800700002 which quite obviously means, that a registry key named ProfileImagePath below HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList was pointing to a directory %systemroot%\system32\config\systemprofile that did not exist on my machine. In this case I could convince Windows Backup to go on after I manually created this directory for Windows Backup.
That was it? No. After that I got some obvious 0x80070570 followed of course by a 0x8007000D and on my Vista Box all I naturally get is 0x80070079. In the meantime I ran hours of chkdsk /r or sfc /scannow. In the end I assumed that maybe my backup hard drive had some kind of problem and reformatted it.

What I did not know at this point was that this meant the end for my "Windows Backup" on my machine. When I now start Windows Backup all I get is 0x80070057:



It took Microsoft half a year of investigation to find out that this error in fact is related to some problems they have with non-US locale settings.

The closest I ever got was a single unreproducible backup that ended "successfully with errors". But when I checked the log I found this error message:

Error while backing up "C:\Windows\System32\config\systemprofile\myFolder". The system cannot find the file specified (0x80070002))

The surprising part is, that this folder never existed and never will exist on my system. This raises the question, why a *backup* program starts seeing ghost files, which of course it later fails to find??

It also raises the question, what specifications, development and quality assurance process they have at Microsoft, that allows them to ship *twice* a solution that throws hexadecimal errors messages in a users face? From a software engineering perspective this is bare amateur level. If Microsoft wants to know, why the people love Apple, then here you got the answer. They would not ship crap like this once.

The reason could be simple. Because it feels like the core of Windows Backup is some component Microsoft bought and then tried to cram somehow into Windows. I would not wonder if there is just some sort of (former) third party backup.exe running under the hood.

As we speak of backup, means the security of your data, using Windows Backup you must ask yourself the following questions:

Do you really want to trust your data to a backup program

• that is incapable of giving a useful error message?
• that needs folders to be manually created to work at all?
• that tries to backup files that do not exist?
  
• that is dependent on US locale settings?
• that even after the Vista desaster, Microsoft itself was unable to fix in the 3 years time until Windows 7?

So the conclusion is simple:

If you love your data,
do not use Windows Backup!

How to pass arrays from .Net C# to Oracle

Guess you have the following query

select * from table where table.id in (:MyIDList)

and you want to pass a number of IDs in the binding variable :MyIDList from C# to your Oracle SQL. If you now check the types of binding variables available for this job, you find out that this Oracle only supports scalar types as binding variables.

If you now dig further you find a couple of bright solution that are all based on splitting up some string (varchar2) into several elements. But due to the fact, that the length of varchar2 binding variables is limited to 4/32K SQL/PLSQL this is not a scalable solution to the problem.

Even if you think you are smart, and you try a string replace of :MyIDList with the elements like 'a','b','c' let me assure you that the limit for written elements in an in statement is 1000. Also your performance will degrade significantly, as the query cache won't recognize the SQL as being executed before and therefore Oracle has to recompile it with every execution.

Is it impossible to pass an variable length array to SQL?

Let me put it straight!

Yes and No.

In pure SQL it is impossible to pass a variable length array to SQL.

But in PL/SQL it is not.

"Yes great", you think, "but I need it in SQL!"

The trick is a PL/SQL wrapper to SQL!

Simply use the following ingredients:

Define 2 global types (and don't try to be smart here. We need IDType for couple of reasons):

CREATE OR REPLACE

type IDTYPE as object (

id varchar2(20)

);

CREATE OR REPLACE

type IDTABLETYPE as table of IDType;

In PL/SQL now create a package

CREATE OR REPLACE PACKAGE MYPACKAGE

as

type stringTableType is table of varchar2(20) index by binary_integer;

procedure GetMyTableByIDs

(

p_MyIDList IN stringTableType,

p_outRefCursor out RefCursorType

);

end;

CREATE OR REPLACE PACKAGE BODY MYPACKAGE

as

TYPE RefCursorType IS REF CURSOR;

procedure GetMyTableByIDs

(

p_MyIDList IN stringTableType,

p_outRefCursor out RefCursorType

)

as

iMyIDList IDTableType;

begin

iMyIDList := IDTableType();

iMyIDList.Extend(p_MyIDList.count);

for i in p_MyIDList.First .. p_MyIDList.Last

loop

iMyIDList(i) := IDType(p_MyIDList(i));

end loop;

open p_outRefCursor

for

select * from table where table.id in (select id from table(iMyIDList));

end GetMyTableByIDs;

end;

What is going on here?

First thing you notice is that we have 2 very similar array (table) types.

Globally we defined the

type IDTABLETYPE as table of IDType -- IDType is varchar2(20)

This is in Oracle terms a "Nested Table". This type is available in SQL and PL/SQL. IDType brings the property "ID" of type varchar2(20).

In PL/SQL we defined the very similar type:

type stringTableType is table of varchar2(20) index by binary_integer;

This is an "index by table" or "associative array" in oracle terms. Associative arrays are better understood as "HashTable" and are available in PL/SQL only. For a more detailed explanation of the differences please have a look at "Collection Types in PL/SQL".

But why do you copy the arrays one by one?

Because you now see that Oracle has obviously 2 different development units for SQL and PL/SQL. And they do not seem to talk very much together.

The result of 3 days in short:

• There is no way to pass a nested table as parameter to a stored procedure in C#
• There is no way to use a associative array in SQL

• There is no way to assign/initialize a nested table to/with an associative array

Great, but how do we use it in C#?

OracleConnection conn = new OracleConnection("MyConnectionString");

OracleCommand cmd = conn.CreateCommand();

cmd.CommandText = "MyPackage.GetMyTableByIDs";

cmd.CommandType = CommandType.StoredProcedure;

cmd.BindByName = true;

cmd.Parameters.Add(new OracleParameter("p_outRefCursor", OracleDbType.RefCursor)).Direction = ParameterDirection.Output;

cmd.Parameters.Add(new OracleParameter("p_MyIDList", OracleDbType.Varchar2)

{

CollectionType = OracleCollectionType.PLSQLAssociativeArray,

Value = my_list_with_ids.ToArray()

}

);

da = new OracleDataAdapter(cmd);

da.Fill(myDataSet);

It's not working!

I can't find neither

cmd.BindByName = true;

nor

OracleCollectionType.PLSQLAssociativeArray

The constraint of the solution is that you have to use Oracle .NET driver (Oracle.DataAccess) instead of the the Microsoft (System.Data.OracleClient) driver. But with .Net 4 the Microsoft's Oracle driver is marked deprecated anyway. So get used to it.

This is it. The only way to pass an array to a SQL.

"Holy shivers!" you think. This is a lot of glue for a simple task like this!

Basically yes, BUT

You can do 3 things now:

1.) I use a macro that converts my SQL into PL/SQL by automatically replacing the binding variable :MyIDList with the PL/SQL Parameter p_myIDList.

2.) You can tune your performance significantly by rewriting your SQL

3.) You can clean up your code a lot by using default values

Improve overall performance

Our former SQL

select * from table where table.id in (select id from table(iMyIDList))

becomes unbearable slow with a large number of ID's and lines in table. What you can do now is to rewrite our SQL to

select * from table where table.id

join (select id from table(iMyIDList)) IdFilter on table.id = IdFilter.id

And if you want to develop in SQL and simply convert it with our macro you can add the following function to your package:

FUNCTION GetDefaultTable

(

param varchar2

)

RETURN IDTableType

is

begin

return IDTableType(IDType(param));

end;

and rewrite your regular SQL to:

select * from table where table.id

join (select id from table(MyPackage.GetDefaultTable(:MyIDList)) IdFilter

on table.id = IdFilter.id

Use PL/SQL defaults:

A feature of our PL/SQL is that you can define default values for all parameters.

In a simple case this is:

procedure GetSomeThing

(

p_param1 in varchar2 default 'SomeDefaultValue',

);

but what do we do with our associative array?

The fancy part about default parameters is that the value can be a call to function.....

So

procedure GetSomeThing

(

p_MyIDList IN stringTableType default GetDefaultTable('DefaultValueForElement'),

p_param1 in varchar2 default 'SomeDefaultValue',

p_outRefCursor out RefCursorType

);

works nice and easy.

in combination with the line in C#

cmd.BindByName = true;

what you can do now is only pass parameters that differ from their defaults what can be used to write a much nicer code. Instead of passing all the parameters defined a for each procedure defined (results in a clunky piece of code), you just just set the parameter for any stored procedure if the associated value is non-default.