How to put a delay in an Oracle sproc, for testing

To simulate your Oracle stored procedure taking 15 minutes to execute, put this in your sproc:

dbms_lock.sleep(15 * 60);

But first you’ll need to give your user EXECUTE permissions on the dbms_lock module:

C:\>sqlplus sys@clayoracle3 as sysdba
...
SQL> GRANT EXECUTE ON dbms_lock TO my_user;

Grant succeeded.

If you’re trying this out outside a sproc, put an exec on the beginning, thus:

exec dbms_lock.sleep(15 * 60);

Note: dbms_lock.sleep() is known to be unreliable past about a 10 minute nap.

How to put a delay in a SQL Server sproc, for testing

To simulate your SQL Server stored procedure taking 15 minutes to execute, put this in your sproc:

WAITFOR DELAY '00:15'

How to spit out a result set returned by an Oracle sproc

When a sproc returns a result set, in Oracle it takes a little more work to view the contents of that result set. But it can be done.

Simple example

As an example, say you want to call the spir_get_fooids_by_bazidkey sproc which returns a result set of foo ids, and you want to see what foo ids it returned. Here’s how that would look:

Oracle stuff

To disable ampersand-variables:
SET DEFINE OFF

To enable debug output:
SET SERVEROUTPUT ON

To list data tablespaces:
SELECT DISTINCT TABLESPACE_NAME FROM ALL_TABLES;

To list index tablespaces:
SELECT DISTINCT TABLESPACE_NAME FROM ALL_INDEXES;


Oracle: 30 char identifiers

Because I can never remember, here it is:

The size of an identifier cannot exceed 30 characters.

–from Oracle’sPL/SQL User’s Guide and Reference: 10g Release 1 (10.1)

DB-level unit tests for SQL Server

I had created some test data on a labmanager VM.  It had taken some time — I had recorded a few calls, an email, and a chat, then tweaked the recording metadata to try out a few important aspects of the stored procedure I was testing.

It’s at these times that I get the nagging feeling — the test was so time-intensive to create and run, why not take the step of automating it so we can run it in the future and catch regressions ‘most instantly?

I wanted an SQL script that I could run that would INSERT the test data as it currently existed on my test machine.  And I didn’t want to create those INSERT statements by hand.  Boy, did I not want to write them all by hand.

Solution

Microsoft SQL Server 2005 comes with something called Database Publishing Wizard (look under Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2\ ).  With the following command:

sqlpubwiz script -d MyDatabase -dataonly c:\temp\MyDatabase.sql

…I was able to generate INSERT statements for all the data in my little test database to an .sql script file.  I then edited the file to remove things I didn’t need (and to avoid key collisions by substituting NEWID() calls for hardcoded uniqueidentifiers as necessary).

Finally, with all the data in place, I could add the following tests. (If anything goes wrong, an error message appears in the Messages window.)

DECLARE @tempTable TABLE(BlahId      uniqueidentifier,
                         BlahType        int,
                         BlahDateUTC DateTime,
                         BlahLength  int,
                         PhoneNumber      nvarchar(60),
                         RightsMask       int);

DECLARE	@return_value int;
INSERT INTO @tempTable
EXEC	@return_value = [dbo].[blah_get_blah_chunk]
		@i_sessionId = @sessionId,
		@i_chunkSize = 50;

IF @return_value <> 0 RAISERROR('Return value should be zero but was %d', 15, 1, @return_value);

DECLARE @expectedRecords int;
DECLARE @actualRecords int;
SET @expectedRecords = 5;
SET @actualRecords = (SELECT COUNT(*) FROM @tempTable);
IF @actualRecords <> @expectedRecords RAISERROR('Expected %d records in result set but found %d', 15, 1, @expectedRecords, @actualRecords);

DECLARE @actualDisplayName nvarchar(60);
DECLARE @expectedDisplayName nvarchar(60);
SET @expectedDisplayName = N'blah_user -';
SELECT @actualDisplayName = (SELECT LocalPartyName FROM @tempTable WHERE RecordingId = N'a0ca1a4f-0933-4565-b5da-d8fc3222a42a');
IF @actualDisplayName <> @expectedDisplayName RAISERROR('Expected display name to be "%s" but it was "%s"', 15, 1, @expectedDisplayName, @actualDisplayName);

GO

In the future we could have a script test runner that keeps track of the output and flags errors as part of the build… but one step at a time, right? This already takes test scenarios that took more than a day to set up and makes them runnable in a minute or two.

Database Publishing Wizard can also generate script to create the entire database schema, but it’s the data generation that will support my unit test needs.

Thanks, Microsoft!

Acknowledgement

Thanks to Pinalkumar Dave for his helpful write-up titled Generate Script with Data from Database – Database Publishing Wizard

Funny log message

I was just browsing through a 130,000-line log file trawling for clues to an error.  About a hundred screens down, this log message made me smile:

N MSI (s) (98:F8) [16:53:58:645]: Hello, I'm your 32bit Impersonated custom action server.

Glad to meet you, sir!

To manipulate SQL Server metadata from a remote client…

(If you’d rather skip the narrative and go straight to the solution, please see the Solution heading…)

We use virtual machines for a lot of our testing — you just clone a VMWare LabManager image, deploy it, and off you go.  You can do whatever you want to the configuration, because when you’re done you just undeploy and delete the workspace.  It’s really nice.

The “clone a LabManager image” step works because someone regularly takes one of the nightlyish builds and installs it to a VM, creating an image suitable for others to clone.

For less generally applicable needs, such as builds of the team branch I’m working in, an image is not always available, but we can take the main branch and apply transformations to it to update it to the team branch.

And now we’ve arrived at the topic of this post.  One of the tranformations I commonly find myself needing to apply is a database schema update.

I can copy the SQL Server update scripts to the VM and run MS SQL Server Management Studio on the VM.  This is ok, but it requires that I Remote Desktop in to the VM, establish access to the VM’s file shares, and copy files from two different directories… and SQL Server Management Studio is sluggish on the VM.

I would prefer to run SQL Server Management Studio on my local PC, connecting to the VM… but the user account doesn’t have permissions to alter columns or create or drop tables.  Do I have to use Remote Desktop after all?

No, it’s a simple setup issue.

Solution

Edit the properties of your user in MS SQL Server Management Studio under Security->Logins (you might need to get on the VM to do this) and add the sysadmin server role to your user.  Now you can create and drop tables, alter columns, etc. from a remote SQL Server Management Studio connection.

Using Unicode characters in SQL Server

In troubleshooting an issue this week involving the storage of Japanese characters to the database, I found out some things.

When I ran a query on the database using MS SQL Server (2005) Management Studio, the row that should have looked like this

instead looked like question marks:

??????

Is it just my glasses?

My reaction in a situation like this is: “Is it really foggy out, or is it just my glasses?”  Either the data was being stored incorrectly in the database or I was simply having trouble viewing it.

Answers

Here is a dump of the things I learned:

• The question marks indicate that the data was really not stored correctly (data loss).
• If instead, your query results appear as square boxes (▯▯▯▯▯▯), your PC just does not have the language pack installed to view the characters.  On Windows XP, go to Control Panel -> Regional and Language Options and check the Install files for East Asian languages checkbox.
• One possible cause of your data turning to question marks is if you insert a literal unicode string but do not use the N prefix (e.g., N‘myunicodestring’).  Setting it as just ‘myunicodestring’ can cause non-Latin characters to go to question marks.
• Another cause is if your column is not of a unicode type.  For instance, on SQL Server, you would want to use one of the n- character column types such as nvarchar(x).

I, foreigner

I am troubleshooting an issue in the Japanese localization of one of our products.  Some string is apparently not getting sent to the database correctly, or for whatever reason the proper records are not being returned.

There’s a system set up in Japanese that I’m testing on.

I’m not used to feeling so disoriented using a program.  It’s not like a screen in Spanish, where I can understand some of it and guess my way around… the Japanese characters mean nothing to me:

Uhh, hmm… interesting to have the perspective of a non-native speaker.  What can help me?  How can I do anything? (I even know this application reasonably well in English, but the foreignness of the language is overwhelming, immobilizing.)

As I visually scanned the screen for anything that would help me find my way, I found myself focusing on the icons (which I had barely noticed in the English version) and on the arabic numbers.  “Ah yes,” I remembered –  the magnifying glass signifies a search, and “検索 1″ probably means “New Search 1″.

I created a new search and added an attribute to it, and once again the icons were most helpful (otherwise I would have had little hope of knowing what kind of search attribute I was adding):

So what’s the lesson in this?  One is that it’s really helpful to know the language!  But I think my takeaway is an increased identification with and compassion for those using a system who for whatever reason may not understand the language, or not understand it well.  I want the systems I design to not forget that person — and give them a little help when I can.