FORMS_BUILDER_CLASSPATH not set after Installation of Oracle Forms Builder 11.1.1.2.0

September 22, 2010

To add Forms 11 support to my application Forms Toolkit, I needed to install Oracle WebLogic and Oracle Developer Tools (see download information).

While the installer reported “installation complete” after a couple of minutes, it really spent about 5 hours (on my machine) installing and configuring the software. It’s best to watch the installation log file using a tool like BareTail, because the installer won’t give you much information whether it is still active and what it is doing.

After completion, I tried to start Oracle Forms Builder, but got this error message:

FRM-91129: fatal error in runtime process: no value specified for required environment variable FORMS_BUILDER_CLASSPATH

FRM-91129: Abbruchfehler: Kein Wert für erforderliche Umgebungsvariable FORMS_BUILDER_CLASSPATH angegeben

Forms 11 does not seem to be administered by earlier versions of the Oracle Home Selector, so I had to look for some clues where Forms would get its startup information from.

Open the Registry Editor (regedit.exe) and navigate to HKLM\SOFTWARE\ORACLE\ and find a number of subkeys starting with “KEY_OH”. Navigate to the key holding values for ORACLE_HOME and ORACLE_HOME_KEY that point to your installation directory.

In Explorer, navigate to the forms\templates\registry directory of your installation (C:\oracle\Middleware\as_1\forms\templates\registry in my case), and open the file formsbuilder.reg in your favorite editor and save under a different name (e.g. my_formsbuilder.reg).

Delete all lines between “REGEDIT4″ and the first line containing the %ORACLE_HOME_REG_KEY% macro. The file now looks like this:

REGEDIT4

[HKEY_LOCAL_MACHINE\%ORACLE_HOME_REG_KEY%]
"FORMS_SCCONVERT"="false"
[HKEY_LOCAL_MACHINE\%ORACLE_HOME_REG_KEY%]
"FORMS_SCDRIVER"="d2scrw32.dll" 

[HKEY_LOCAL_MACHINE\%ORACLE_HOME_REG_KEY%]
"FORMS_SCVIEWER"="NOTEPAD.EXE" 

[HKEY_LOCAL_MACHINE\%ORACLE_HOME_REG_KEY%]
"FORMS_ORACLE_INSTANCE"="%ORACLE_INSTANCE%"
[HKEY_LOCAL_MACHINE\%ORACLE_HOME_REG_KEY%]
"FORMS_BUILDER_CLASSPATH"="%ORACLE_HOME%\\a\\number\\of\\jars"

Replace all occurrences of %ORACLE_HOME_REG_KEY% with the value of the registry key ORACLE_HOME_KEY, e.g.

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OH377519060]

Save the file, and double-click from Explorer. Acknowledge the warning that the .reg file will be merged into the registry, and the successful merge.

You can now start Oracle Forms Builder.

Note: this worked on my machine, and may not necessarily solve your problem. However, the procedure only adds 5 known keys to the registry, so it can be revoked easily.


Calculating the Length of the Longest Common Subsequence in TSQL

September 16, 2010

When trying to calculate the similarity of strings, the Levenshtein Distance comes up as one way to solve this problem, as it counts the number of additions, deletions and substitutions of characters to transform one string into the other.

The inverse solution is to count the number of characters that are the same in both strings in the same order, known as the Longest Common Subsequence problem (LCS).

The code presented here uses the same technique as the Levenshtein algorithm in storing a two-dimensional array of integers as an NVARCHAR(MAX), i.e. a string of Unicode characters encoding the integers in a matrix.

First, we need a function Max2() to retrieve the maximum of two integers:

CREATE FUNCTION [dbo].[Max2](@a int, @b int)
RETURNS INT AS
BEGIN
  IF @a > @b
    RETURN @a
  RETURN @b
END

and then the T-SQL version of the algorithm:

CREATE FUNCTION [dbo].[LCS]( @s NVARCHAR(MAX), @t NVARCHAR(MAX) )
RETURNS INT AS
BEGIN
  DECLARE @d NVARCHAR(MAX), @LD INT, @m INT, @n INT, @i INT, @j INT, 
    @s_i NCHAR(1), @t_j NCHAR(1)

  SET @n = LEN(@s)
  IF @n = 0 RETURN 0

  SET @m = LEN(@t)
  IF @m = 0 RETURN 0

  SET @d = REPLICATE(NCHAR(0),(@n+1)*(@m+1))

  SET @i = 1
  WHILE @i <= @n BEGIN
    SET @s_i = SUBSTRING(@s,@i,1)

    SET @j = 1
    WHILE @j <= @m BEGIN
      SET @t_j = SUBSTRING(@t,@j,1)

      IF @s_i = @t_j

        SET @d = STUFF(@d,@j*(@n+1)+@i+1,1,
          NCHAR(UNICODE(
            SUBSTRING(@d, (@j-1)*(@n+1)+@i-1+1, 1)
            )+1))

      ELSE

        SET @d = STUFF(@d,@j*(@n+1)+@i+1,1,NCHAR(dbo.Max2(
          UNICODE(SUBSTRING(@d,@j*(@n+1)+@i-1+1,1)),
          UNICODE(SUBSTRING(@d,(@j-1)*(@n+1)+@i+1,1)))))

      SET @j = @j+1
    END
    SET @i = @i+1
  END      

  SET @LD = UNICODE(SUBSTRING(@d,@n*(@m+1)+@m+1,1))
  RETURN @LD
END

dbscript 1.03 released

September 15, 2010

dbscript, the integrated database versioning, documentation and code generation web application, has finally been migrated to .Net 3.5 (SP 1).

This makes it easier to install, as the separate installation of ASP.Net Ajax libraries is not necessary anymore.

The draw-back is that it is no longer possible to run the web application on Windows 2000 (Server), if that is a concern to anybody.

Generated Documentation

MS SQL Server Oracle PostgreSql edmx
dbscript HTML view view view
dbscript Single HTML view view view view
view
HTML (single file) view view view view
view
MediaWiki view view view view
view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view view view view
view

dbscript is available for download here.


Solving 2 ASP.Net Run-Time Errors

September 15, 2010

Sys.Extended is undefined

Firefox Web Developer Toolbar shows the error

Sys.Extended is undefined

If you click Information, View JavaScript, browse through the code and you may find the section for a JavaScript file called

http://localhost/myapp/somefile.aspx?_TSM_HiddenField_=ctl00_ScriptManager1_HiddenField
&_TSM_CombinedScripts_=3b%3bAjaxControlToolkit%2c+Version%3d3.5.40412.0
%2c+Culture%3dneutral%2c+PublicKeyToken%3d28f01b0e84b6d53e%3ade-DE
%3a065e08c0-e2d1-42ff-9483-e5c14441b311%3ade1feab2%3af2c8e708%3a720a52bf%3af9cec9bc%3a4a2c8239

(your codes may differ), containing an error message from ASP.Net that a directory cannot be found.

Solution: add the missing directory, even if it is empty.

Could not load type ‘System.Web.UI.ScriptReferenceBase’

The browser displays the error message:

Error Message: Could not load type ‘System.Web.UI.ScriptReferenceBase’ from assembly ‘System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′.

Der Typ “System.Web.UI.ScriptReferenceBase” in der Assembly “System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ konnte nicht geladen werden.

Solution: Install .Net Framework 3.5 Service Pack 1


Third Anniversary

September 13, 2010

12 month summary:

Of course, there were a couple of releases of dbscript, my database versioning, documentation, and code generation application. Other applications such as SMOscript and oraddlscript were also updated for bug fixes or enhanced functionality.

I wrote wpxslgui, a tool to convert WordPress XML Exports into HTML and Word HTML. YuJisho is a CJK character web dictionary based on freely available dictionary data with a simple user interface and fast responses (below 1 second). checktsql is a small tool to check all T-SQL objects in a MS SQL Server database for validity and reports error caused by referencing missing objects.

Projects I worked on using Entity Framework and NHibernate also generated a couple of entries.

The raw numbers: 162,000 views, 285 posts, 4000 downloads.


Searching TSQL Stored Procedures (and other Modules)

September 10, 2010

Every now and then I come across the question, “How do I search my stored procedures for a certain table or column name in MS SQL Server?”. You might have, too…

SQL Server up to version 2000 provided the view syscomments, which is still implemented in versions 2005 and higher as sys.syscomments. (All system catalog views have been moved to the sys schema in 2005)

The major drawback with syscomments is that its text column containing the stored procedure code is defined as NVARCHAR(4000), the longest possible string value in SQL Server 2000 (apart from NTEXT). So if the procedure (or function) code is longer than 4000 characters, the code is sliced into several 4000 character records. And as it happens, the string you are searching for may end up right at the boundary of two records, complicating the search algorithm.

SQL Server 2005 introduced the sys.sql_modules view which has a Definition NVARCHAR(MAX) column holding code, thus resolving this issue of split code. (check the remarks if you want to search DEFAULT and CHECK expressions, too)

So now that you happily accepted that search sql_modules is better than searching syscomments, here is the code to perform the search in TSQL:

DECLARE @Search NVARCHAR(MAX)

SET @Search = 'MyOldColumnName'

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, -- o.type,
       o.type_desc   -- , sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition LIKE N'%' + @Search + N'%'
ORDER BY o.type, OBJECT_NAME(sm.object_id);

As you see, the search is executed using the LIKE operation. But what if you want to search for wildcards as literals, such as _ and %, or the regex characters [, ], -  (because, for example, your database object names may contain underscores) ?

Simply escape the characters using the [] notation:

SET @Search = REPLACE(@Search, '[', '[[]')
SET @Search = REPLACE(@Search, '%', '[%]')
SET @Search = REPLACE(@Search, '_', '[_]')

and run the query after the REPLACE operations.

But what if you want to search for whole words only? Use PATINDEX using a regex which excludes alphabetical characters and underscores before and after the search string:

SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, 
    o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE PATINDEX(N'%[^a-zA-Z_]' + @Search + N'[^a-zA-Z_]%', sm.definition) > 0
ORDER BY o.type, OBJECT_NAME(sm.object_id);

Preparing web.config for Deployment using PowerShell

September 7, 2010

After you build your Web Project in Visual Studio, it would be nice to automatically process the developer’s web.config to remove any sensitive information, such as login credentials, and other information that sits in the config file for debugging or testing purposes.

As described in the previous entry, the build batch generates a compiled web application, and renames the web.config to web.publish.config, to avoid accidentally overwriting an existing web.config during an upgrade.

This PowerShell script opens the web.publish.config, replaces the connection string with a place holder, and removes two config sections (the XML manipulations shown are for illustration only):

$config = "c:\path-to\published\app\web.publish.config"

[xml]$cfg = Get-Content $config

"$config loaded"

$node = $cfg.SelectSingleNode("//connectionStrings/add[@name='default']")
$node.connectionString = "Data Source=LOCALHOST;Initial Catalog=THECATALOG;Persist Security Info=True;User ID=USERNAME;password=PASSWORD"

$node = $cfg.SelectSingleNode("//appSettings")
if ($node) { $node.RemoveAll() }

$node = $cfg.SelectSingleNode("//system.web/pages/controls")
if ($node) { $node.RemoveAll() }

$sr = New-Object System.IO.StreamWriter $config
$cfg.Save($sr)
$sr.Close()

"$config saved"

The script loads the web.config, performs some XML operations (notice the inline addressing of the connectionString attribute), and saves the XML document to its original name.


Publishing Web Application Projects from the Command Line (VS2008)

September 7, 2010

If you are a regular reader of this blog, you will know that one of the recurring topics is automatic builds of Visual Studio projects from command line or batch (see here, here, here).

I have written about the functionality of Web Deployment Projects for Visual Studio 2005, and after upgrading also downloaded Web Deployment Projects for Visual Studio 2008. Not much has changed in the meantime, but there are some noteworthy differences between Web Site Projects and Web Application Projects.

The main issue when compiling a Web App Project using aspnet_compiler (instead of msbuild) is that some files (such as the .csproj and .csproj.user files) and directories are copied to the output, as they do not exist in Web Site projects.

Thus the build+publish process should remove the files not required for execution (we are dealing with compiled applications, after all).

This is the updated batch file for building web application projects:

@echo off
if "%1"=="" goto errnoparam
if "%2"=="" goto errnoparam

if not exist %2\nul goto errnotfound

setlocal
call "C:\Program Files\Microsoft Visual Studio 9.0\VC\vcvarsall.bat" x86
echo on 

set base=c:\path-to\publish\%1

if not exist %base%\nul md %base%

rd /s /q %base%

echo compiling to %base% 

aspnet_compiler -v /%1 -f -d -c -p %2 %base%

aspnet_merge %base% -w %1.pages -debug 

echo web compiled to %base%

pushd %base%
del *.csproj
del *.csproj.user
rd /s /q Code
rd /s /q obj
rd /s /q Properties
rd /s /q "Web References"
ren web.config web.publish.config
popd

endlocal

goto end

:errnoparam

echo usage: %0 webname sourcepath
goto end

:errnotfound

echo web %1 not found
goto end

:end

The batch file takes two parameters: the assembly name and the path to the web app project (assuming that web app projects do not reside in the inetpub\wwwroot directory, as was the case with web site projects).

It calls aspnet_compiler to compile pages, controls, and code-behind, and aspnet_merge to merge all markup files (.aspx, .ascs, etc) into an assembly names *.pages. Afterwards it cleans up unnecessary files and directories, and renames web.config (to avoid accidentally overriding web.config in production environments).

Edit 14.9.2010: It seems the runtime requires directories originally containing .ascx files to be present in the published web, even though the directory is empty.


TSQL String Functions

September 7, 2010

While searching for appropriate string comparison functions in TSQL, I came across these pages implementing a couple of

string functions: Capitalize, Center, Count Substring in String, EndsWith, ExpandTabs, IsAlnum, IsAlpha,
IsDigit, IsLower, IsTitle, IsSpace, LJust, LStrip, RFind, RJust, RStrip, Strip, SwapCase, Title, Zfill

and string tuple functions: Split, SplitLines, Within, EndsWith, StartsWith, Contains, Join, Parts, Partition, RPartition

See here for more TSQL articles by the authors.


Calculating Levenshtein Distance in TSQL

September 7, 2010

I tried to find a method to compare strings according to their similarity, and first came across the Levenshtein distance which defines the distance (or degree of similarity) between two strings as the minimum number of additions, deletions, and substitutions of single characters needed to transform one string into the other.

I found this implementation of the Levenshtein algorithm in T-SQL, but noted a couple of errors:

First, the function returns a VARCHAR result, where you would expect an INT.

Next, due to the restriction of parameters and variables to VARCHAR(50) and VARCHAR(100), only strings with a limited number of characters could be compared. (The code may have been written before the introduction of VARCHAR(MAX)).

Furthermore, the distance matrix is stored in an array of CHAR, which only allows for a maximum difference of 255 characters to be handled correctly.

The Levenshtein algorithm requires a function to find the minimum of 3 integers:

create function [dbo].[min3](@a int, @b int, @c int)
returns int as
begin
  declare @min int
  set @min = @a
  if @b < @min set @min = @b
  if @c < @min set @min = @c
  return @min
end

And this is the code:

CREATE FUNCTION [dbo].[LEVENSHTEIN]( @s NVARCHAR(MAX), @t NVARCHAR(MAX) )
/*
Levenshtein Distance Algorithm: TSQL Implementation
by Joseph Gama

http://www.merriampark.com/ldtsql.htm

Returns the Levenshtein Distance between strings s1 and s2.
Original developer: Michael Gilleland http://www.merriampark.com/ld.htm
Translated to TSQL by Joseph Gama

Fixed by Herbert Oppolzer / devio
as described in http://devio.wordpress.com/2010/09/07/calculating-levenshtein-distance-in-tsql
*/
RETURNS INT AS
BEGIN
  DECLARE @d NVARCHAR(MAX), @LD INT, @m INT, @n INT, @i INT, @j INT,
    @s_i NCHAR(1), @t_j NCHAR(1),@cost INT

  --Step 1
  SET @n = LEN(@s)
  SET @m = LEN(@t)
  SET @d = REPLICATE(NCHAR(0),(@n+1)*(@m+1))
  IF @n = 0
  BEGIN
    SET @LD = @m
   GOTO done
  END
  IF @m = 0
  BEGIN
    SET @LD = @n
    GOTO done
  END

  --Step 2
  SET @i = 0
  WHILE @i <= @n BEGIN
    SET @d = STUFF(@d,@i+1,1,NCHAR(@i))        --d(i, 0) = i
    SET @i = @i+1
  END

  SET @i = 0
  WHILE @i <= @m BEGIN
    SET @d = STUFF(@d,@i*(@n+1)+1,1,NCHAR(@i))    --d(0, j) = j
    SET @i = @i+1
  END

  --Step 3
  SET @i = 1
  WHILE @i <= @n BEGIN
    SET @s_i = SUBSTRING(@s,@i,1)

    --Step 4
    SET @j = 1
    WHILE @j <= @m BEGIN
      SET @t_j = SUBSTRING(@t,@j,1)
      --Step 5
      IF @s_i = @t_j
        SET @cost = 0
      ELSE
        SET @cost = 1
      --Step 6
      SET @d = STUFF(@d,@j*(@n+1)+@i+1,1,
        NCHAR(dbo.MIN3(
          UNICODE(SUBSTRING(@d,@j*(@n+1)+@i-1+1,1))+1,
          UNICODE(SUBSTRING(@d,(@j-1)*(@n+1)+@i+1,1))+1,
          UNICODE(SUBSTRING(@d,(@j-1)*(@n+1)+@i-1+1,1))+@cost)
        ))
      SET @j = @j+1
    END
    SET @i = @i+1
  END      

  --Step 7
  SET @LD = UNICODE(SUBSTRING(@d,@n*(@m+1)+@m+1,1))

done:
  RETURN @LD
END

Follow

Get every new post delivered to your Inbox.

Join 65 other followers