Author: LizetP

Remember next time you write a web service or DAL class that connects to an Oracle database. If using the version 1.1 of the Framework and the Microsoft Oracle Provider, this provider has a known bug when the service name is longer than 16 characters:

The exception that you will get when trying to open the connection is:
ORA-00162: external dbid length xx is greater than maximum (16)

more on the topic can be found here

The workarounds are:
1) putting the whole tnsnames.ora entry in the DataSource entry of your connection string or
2) use Oracle’s data provider (ODP.NET)
or
3) set the TNSADMIN environment variable to point to a different directory
where you keep a modified tnsnames.ora file.

more on the troubles with the Oracle Native Provider for .NET (ODP.NET) whenever i have a chance to write again…

Did you ever have to use regular expressions on your client side code (JavaScript) to validate user input?
The .NET Framework has really cool RegularExpressionValidators that will do the job for you, however, these controls are not multilingual and this is due to the fact that ECMAScript, javascript, lacks of a specification for Unicode support.
Your common “\w” or “a-zA-Z” regular expression, while in the code behind does include Latin Extended characters such as ñ or ü, it only includes ASCII characters while used with JavaScript.

A workaround? modify your regular expressions in your javascript validators to explicitly include the characters you want.

A good post about this can be found on this blog

TTYL…

Got this article extract in an email from a friend, I lost the original link though…

If you want a job at a company like Microsoft, Yahoo!, Apple, or Amazon.com, they’re going to have high standards. It doesn’t matter if you “know how to program”. They’re going to test you on algorithmic complexity analysis, advanced data structures, algorithm design, searching and sorting, internationalization techniques, network protocols, OS-level memory management, parsing and semantic analysis, recursion and mathematical induction, graph theory, combinatorics, programming language theory, machine architecture, discrete math and logic, graphics and window systems, fonts and typesetting, color spaces and representations, databases and query languages, filesystems and storage, embedded systems, device drivers, mobile and wireless protocols, and internet standards and technologies.

If you’re lucky, that is.

If you’re unlucky, they’ll ask you to derive the outline of their Ph.D. thesis on fault-tolerant massively parallel machine-learning systems. Or to solve a grand-unification style computation problem involving telephone switches, grid networks, and third-degree differential equations. Or, God forbid, they’ll ask you about the darkest corners of C++ syntax.

And you want to know why they’ll ask you about that stuff? Because they’re using it every day. They’ve tried hiring people who don’t know this stuff. Believe me, they try all the time. They want to hire more programmers, and they’re out there on the constant lookout for new meat. But when they lower their standards, they get burned. The 747 crashes, the patient dies, the juggler drops a bowling pin on someone’s head, the tiger rips someone’s throat out. In the software world: the service goes down for days, losing them millions; the project gets delivered late or even not at all, losing them contracts and customers; they lose the business battle to competitors who hired better engineers.

Ok, I’m bored tonight waiting for a release and thought I would again remind myself what to do when SQL Server 2000 refuses to write data longer than 256 characters in an excel spreadsheet. This seems to be a Jet 3.5 and 4.0 bug and the work around is published here…

http://support.microsoft.com/kb/281517

So, next time get your hands dirty and edit the registry or your data will be cut off at the 255 character when exporting from SQL Server into .xls format or when saving from .xls format into .csv.

I’ve used this code over and over, so I better save it for future ocassions.
Problem: A table (MyTable) with a field (name) I need to eliminate records with duplicated name values.

declare @name_unique varchar(50)
declare @tablename table (name_final varchar(50), value text) —every other field in MyTable

DECLARE cursor_uniquename CURSOR
FOR select distinct name from MyTable

OPEN cursor_uniquename

FETCH NEXT FROM cursor_uniquename
INTO @name_unique

WHILE @@FETCH_STATUS = 0
BEGIN

insert into @tablename select top 1 name,value from MyTable where name=@name_unique

FETCH NEXT FROM cursor_uniquename
INTO @name_unique
END

CLOSE cursor_uniquename
DEALLOCATE cursor_uniquename

truncate MyTable

insert into MyTable select * from @tablename

I wonder if there is a more efficient way, hrmmm…

Remember next time you use OracleClient namespace. This dll depends on the Client Tools from Oracle, so that has to be installed in the server and the folder has to have the proper security settings.
If you get the error:

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

and your tnsnames.ora file is fine, your connection strings look good, the client tools you have are for sure of a higher version than 8.1.7 then check the permissions of the folder where the Oracle Client Tools are installed.

Problem
When usign System.Data.OracleClient with Oracle9i client, i got the following message while connecting to the Oracle database from and ASP.NET application.

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater.

Cause
Security permissions were not properly set when the Oracle Client was installed on Windows with NTFS. The result of this is that content of the ORACLE_HOME directory is not visible to Authenticated Users on the machine; this again causes an error while the System.Data.OracleClient is communicating with the Oracle Connectivity software from an ASP.NET using Authenticated User privileges.

Solution
To fix the problem you have to give the Authenticated Users group privilege to the Oracle Home directory.

• Log on to Windwos as a user with Administrator privileges.
• Start Window Explorer and navigate to the ORACLE_HOME folder.
• Choose properties on the ORACLE_HOME folder.
• Click the “Security” tab of the “Properties” window.
• Click on “Authenticated Users” item in the “Name” list.
• Uncheck the “Read and Execute” box in the “Permissions” list under the “Allow” column.
• Re-check the “Read and Execute” box under the “Allow” column
• Click the “Advanced” button and in the “Permission Entries” verify that “Authenticated Users” are listed with permission = “Read & Execute”, and Apply To = “This folder, subfolders and files”. If not, edit that line and make sure that “Apply To” drop-down box is set to “This folder, subfolders and files”. This should already be set properly but it is important that you verify it.
• Click the “Ok” button until you close out all of the security properties windows. The cursor may present the hour glass for a few seconds as it applies the permissions you just changed to all subfolders and files.
• Reboot, to assure that the changes have taken effect.

Try your application again.

Tsk Tsk Tsk. I put my hands recently on the DAAB 1.1. i had used it before with SQL Server and indeed was a great experience.
I got quite a disappointment when i tried this block against Oracle 9i.
I used System.Data.oracle client as provider to form my OracleCommand and to my surprise I wasn’t able to bind System. Int32 data types to OracleType.Number and the parameters marked with output direction always returned the error of buffer to small to allocate the response.
My hunch is the DBCommandWrapper class of the DAAB 1.1 block has something missing…
I tried using a different provider and downloaded ODP.NET from Oracle site. This will incurr on having an extra dll to deploy with my project, but that didn’t work either.

The scenario to reproduce the errors is as follows:

In the method that calls the SPROC I have the following:

Dim IObject As ICommonFactory

Dim cmd As New OracleClient.OracleCommand

Dim myQuote As New DataSet

Try

‘Write to Trace if Enabled

ServiceHelper.RoutineBegin(Debug)

cmd.CommandText = “web.sproc_request”

cmd.CommandType = CommandType.StoredProcedure

With cmd

.Parameters.Add(“NO”, OracleType.VarChar).Value = “333-333333-333”

.Parameters.Add(“iTEMPLATE”, OracleType.VarChar).Value = “XX_XX_COMBINED_ENGLISH”

Dim parameter As New OracleParameter

parameter.ParameterName = “oXML_STRING”

parameter.OracleType = OracleType.VarChar

parameter.Size = 10000

parameter.Direction = ParameterDirection.Output

.Parameters.Add(parameter)

End With

IObject = ObjectCreator.CreateObject(“MyApp”, “Factory”)

Quote_InitialRequest = IObject.GetList(cmd)

‘Write to Trace if Enabled

ServiceHelper.RoutineEnd(Debug)

Catch ex As Exception

Throw

End Try

The GetList Method of the factory looks like this:

Imports Microsoft.Practices.EnterpriseLibrary.Data

…

Public Function GetList(ByVal cmd As
System.Data.OracleClient.OracleCommand) As System.Data.DataSet Implements ICommonFactory.GetList

Dim db As Database =
DatabaseFactory.CreateDatabase(System.Configuration.ConfigurationSetting
s.AppSettings(“MyApp”), “Common”)

db.ExecuteNonQuery(CommonCmdWrapper.PrepareCommand(db, cmd))

Dim myData As New DataSet

GetList = CType(cmd.Parameters(2).Value, System.Data.DataSet)

End Function

The exception is thrown in the lne db.ExecuteNonQuery and the exception message is as follows:

Message “ORA-06502: PL/SQL: numeric or value
error: character string buffer too small

ORA-06512: at “MyApp.WEB”, line 990

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 1

” String

The stack trace is as follows:

StackTrace ” at
System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle
errorHandle, Int32 rc)

at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& rowidDescriptor, ArrayList&
refCursorParameterOrdinals)

at System.Data.OracleClient.OracleCommand.Execute(OciHandle
statementHandle, CommandBehavior behavior, Boolean needRowid, OciHandle&
rowidDescriptor)

at
System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean
needRowid, OciHandle& rowidDescriptor)

at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()

at
Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DB
CommandWrapper command)

at
Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DBCo
mmandWrapper command)

at
CIT.EAI.CS.ECS.OracleObjectFactory.PQSFactory.GetList(OracleCommand cmd) in C:\CIT.EAI.CS.ECS.OracleObjectFactory\PQSDEV\PQSFactory.vb:line 27

at QuoteSystem.QuoteSystem.Quote_InitialRequest(String ContractNo, String TemplateName, String BuyOutType, Boolean Debug) in c:\inetpub\wwwroot\QuoteSystem\QuoteSystem.asmx.vb:line 90″
String

No matter how much I increase the size of the varchars I always have the same error.

I executed the Oracle command in SQL Navigator and it only returns the
ORA-06502: PL/SQL: numeric or value error: character string buffer too small when the output parameter has a size smaller than 2000.

The CommonCmdWrapper is part of the
Microsoft.Practices.EnterpriseLibrary.Data and it seems to be the layer causing the problem.

When using only the System.Data.OracleClient namespace, the call goes through without problems.

I tried contacting the Pattern & Practices team unsuccessfully…

For those of you who attended the second session at the U of S last June 3rd (INETA-Microsoft Ready To Rock event), here are the Live Meeting recordings of the demos.

Demo 1: Data Access

Demo 2: Security, Profiles and Site Navigation

Demo 3: Query Notifications using SQL Server 2005 and ADO.NET

Hope this helps.

Last June 3rd I flew Saskatoon for the day and met with the .NET Users Group. It was good to see colleagues and ex-coworkers. I was presenting four demos regarding VS 2005 and SQL server 2005 with Service Broker. The presentation didn’t go very well as I had a hard drive error on one of my VPC images. Apart from that I noticed a great interest in the new technology, specially SQL Server 2005 Service Broker and web services support.
I also got very interesting questions from the audience. One of them was regarding the ASP.NEt Configuration tool or WSAT, Web Site Administration Tool.
The original question was:

We are planning to extend the Membership API to use it on our own intranet with existing databases; however, one of the advantages of using this API is also having the Web Site Administration Tool available to manage Memberships and Roles.

After a short investigation, especially reading this article:

http://msdn.microsoft.com/msdnmag/issues/05/04/Security/

and this one:

http://www.devx.com/codemag/Article/30087/0/page/3

we realized this WSAT tool is only available with VS 2005. We would like to know though, why isn’t this application available outside VS 2005. What dependencies are shared with VS 2005, so this application cannot be deployed as a stand alone solution?

You can call the tool outside VS 2005, by typing http://localhost/MyWebSite/Webadmin.axd on your web browser.

Juval Lowy’s replies:

In any event, I solved the problem you describe using my Credentials Manager tool described in this article:

http://www.code-magazine.com/Article.aspx?quickid=0511031

Unless you do some hacking, the admin pages (besides not providing all the required features) can only work with Cassini.

…

The WSAT does not use IIS both for security reasons and because many developers are not allowed to have IIS installed. The ASP.NET team recommends you use Credential Manager for real life admin.

You can use Credentials Manager freely (make sure you get the latest version from www.idesign.net) and even modify it as long as you maintain our copy rights and give credit to IDesign in your docs.

And Jeff Prosise’s replies:

WSAT was originally intended to work locally or remotely, but the ASP.NET team grew concerned about security, so in the final release, it only works against local Web servers. You can modify the source code to work against local servers, but in general your best bet is to use the membership and roles APIs to write your own targeted remote admin tool.

The source code is in the ASP.NETWebAdminFiles directory (e.g., C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ASP.NETWebAdminFiles). You can install it in a virtual directory and run it without Visual Studio.

You can modify it to work against remote servers if you wish.

I hope this helps the Community and also stays here on this blog for my own future reference.
Thank you guys and thanks to Saskatoon .NET User Group for their understanding and support during the hard drive failure.

Saskatoon there I go on Saturday!
I’d like to invite all the .Net developers in the Saskatoon area to a short presentation I’ll be giving this Saturday on the U of S, College of Enginering. Details of the event can be found here.
I’ll be doing 5 demos in total regarding Data Access, Membership, Profiles APIs in ASP.NET 2.0 and XML Support and Web Services in SQL Server 2005.
I upgraded my laptop last night to 1.5GB or RAM, so hopefully the VPC will run faster this time.
I would like to thank Bob Beauchemin
for his help preparing the SQL Server 2005 demos, he is a well known speaker and wrote the original Data Platform Series for Microsoft Launch. I appreciate the time he took to answer my questions regarding the CacheSync demo and the tips he gave me for optimizing the VPC.

I still have to prepare myself more tonight and take the time for the final presentation. I hope the audience enjoys the content as much as I did when I attended the Data Platform and Developer Track training.

Cheers!