Oracle Data Access Components – Development Installation Tips

aka ODAC

start here for latest downloads

Oracle Provider for OLE DB

  • I initially found a free generic database object browser tool called Oracle Maestro
    • i actually wound up landing on a much better tool, linked below, but getting Maestro to work walked me through some troubleshooting which would come up for anything requiring OLEDB connectivity
    • Maestro happened to be 32bit only and as usual, the bitness of our runtime is a fun factor…
  • the need for an OLEDB connection string prompted my handy trick of creating a “test.udl” file and then double clicking it to get into a helpful OLD DB Config Wizard UI… once you’ve configured a connection, just notepad that UDL file to copy/paste the connection string, nice!
  • i didn’t have any Oracle providers loaded on my naked win10 instance so i hit the download site above and initially loaded the 64-bit ODAC 12.2c Release 1 (12.2.0.1.0) for Windows x64
  • back to test.udl… now the Oracle provider was listed but immediately upon hitting “next” i got “Provider is no longer available. Ensure that the provider is installed properly.“…
    • using SysInternals ProcMon i saw it seemed to be failing to find oci.dll … started smelling like a binaries path issue… eventually i flip flopped the order of $\oraclehome64\ & $\oraclehome64\bin in my system path and that went away… perhaps also simply because of a reboot
  • i had a connection string now…
  • however, firing up Oracle Maestro, the Oracle Provider was not listed… that’s when i remembered the deal with 32bit and 64bit OLEDB stacks…
  • now loaded “32-bit ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio (12.2.0.1.0)”
  • and back to test.udl, here’s where i use another trick… launching c:\windows\SysWow64\cmd.exe and then start test.udl from there makes sure i’m launching the 32bit OLEDB config UI which happens to be via a C:\Windows\SysWOW64\rundll32.exe command line vs C:\Windows\System32\rundll32.exe
  • here is an example “TNS-less” DataSource for OLE DB based connections: (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ABC.org)(PORT = 1521)) ) (CONNECT_DATA = (SID = XYZ) ) )

.Net Core Projects and “ODP.NET Managed Driver” aka ManagedDataAccess

  • Just reference through Nuget however it is not .Net Core Runtime compatible yet (supposedly .Net Core 2.0 savvy version coming end of 2017)…
  • Must run on top of traditional .Net Framework until Oracle releases their .Net Core 2.0 compatible update
  • Under VS2017 the ASP.Net Core MVC project template is friendly to this mix (see next screenshot below)
    • which also gives us the necessary clue to spin up other less flexible Core project templates and manually edit the csproj to net461

nPoco requiring DbProviderFactory vs direct instantiation UNDER .Net Core

  • typical error message: System.ArgumentException: ‘Unable to find the requested .Net Framework Data Provider. It may not be installed.’
  • the gist is the current Core incompatible ODP.Net is expecting to configure our project’s app.config or web.config …
  • yet as we know, Core has shifted to appsettings.json, no .config file present… which leads me to next heading…
  • yet, in this case, there’s simply a 3rd parameter where we can pass the factory like so:
    new NPoco.Database(ConnectionString, NPoco.DatabaseType.OracleManaged, Oracle.ManagedDataAccess.Client.OracleClientFactory.Instance)

Tip – Generally fixing .Net Framework based Nuget lib’s configuration under .Net Core projects

  1. spin up a quick traditional framework console app
  2. nuget reference the culprit Nuget lib as usual (“Oracle.ManagedDataAccess” in this case)
  3. copy the pertinent sections in the resulting app.config to your C:\Windows\Microsoft.Net\Framework(64)\v4.0.30319\Config\machine.config
    • this stack-o clued me in to remember there is both a Framework and Framework64 folder…
    • i’ve noticed, in my environment anyway, an ASP.Net MVC Core site launches under a 32bit process requiring the settings to be present in \Framework
    • while a .Net Core Console app will launch a 64bit process requiring the \Framework64 settings

machine.config entries i wound up editing in as of current version

<configuration>
  <configSections>
    <section name="oracle.manageddataaccess.client"
      type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>

  ...

  <system.data>
    <DbProviderFactories>
      <remove invariant="Oracle.ManagedDataAccess.Client"/>
      <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"
          type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
    </DbProviderFactories>
  </system.data>

</configuration>

snapshot of the nuget install

snapshot of the DbProviderFactory error

3rd Party Tools

Oracle Forms 11g Development Installation Tips

Yeah I actually said Oracle Forms, that product from the 90’s… as one might guess, i happen to be on a legacy conversion project at work…
There’s some rough edges getting these old bits to install and run that i wanted to capture…

Background

  • my project is tied to the Oracle Forms v11g stack… the latest 11.x version at this time appears to be 11.1.2.2.0
  • we’re using the web servlet stuff which runs our forms as java applets… it’s actually a pretty slick rich client arrangement for the bygone era it heralds from
  • the primary breakthrough i made was getting it all installed once and then doing “xcopy” deploy of the main “Oracle Home” folder (c:\oracle\middleware) to my other team members’ machines… plus the appropriate registry branch (e.g. HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OH1949191890)

Java dependency

  • JDK required – JRE’s not enough
  • JDK 1.6.0u35 – through painful trial and error i’ve settled on this fairly dated release… i’ve seen various components in this whole stack run on more recent JDK’s but subsequent obscure runtime errors beat me into submission

both 32 & 64bit required

  • we are running on Win10 x64 naturally preferring to install 64bit executables…
  • thereby running the 64bit Forms Builder installation which requires 64bit JDK…
  • however the servlet web site generates 32bit java <code> tag references (i’m kinda thinking IE was 32bit only at the time) so we also need the 32bit JDK installed

Java Applet troubleshooting tips:

  • be aware that Control Panel > Java will most likely only show you the 64 bit control panel…
  • you’ll need to specifically launch the 32bit panel via C:\Program Files (x86)\Java\jdk1.6.0_35\jre\bin\javacpl.exe
  • from there you’ll want to enable the Advanced > Java Console > Show Console, to get some visibility on any exceptions firing
  • and very crucial tip here… Advanced > Default Java for browsers > Microsoft Internet Explorer is always greyed out but you can select that node and hit space bar to select it (nuts)
    • its also probably helpful to go back to the 64bit javacpl and uncheck it there

Browser dependency

  • IE (v11 works) is the only browser that will properly launch the 32bit java applet for us on Windows 10 x64 (not Chrome, not Firefox, not Edge)
  • IE11 can actually run in 64bit or 32bit mode depending on what each page’s elements require and due to the 32bit java tags mentioned above, it spawns into a 32bit IExplore.exe process

Main Installation Steps

  1. stop your virus checker’s active file scan – we’re on McAfee enterprise and everybody is pretty spooked that it interferes with these ancient installs and i had enough problems to go ahead and rule it out
  2. WebLogic Server v1.0.3.6 hard dependency
    • i loosely understand weblogic as the web server backend, “servlets”, which deliver the pages and java applets
    • as mentioned, we’re targeting the 11g/11.x stack which drives this 1.0.3.6 version requirement… seriously, trust me, save yourself the trouble, any other version is not going to work with the 11.x Oracle Forms stack which comes next
    • see download links at the bottom of this page
    • CRUCIAL – make sure to get the “Generic” jar – specifically wls1036_generic.jar
    • CRUCIAL – launch wls1036_generic.jar specifically via the golden JDK above under ELEVATED aka Admin command line like so
      • C:\Program Files\Java\jdk1.6.0_35\bin -jar wls1036_generic.jar
    • DO NOT just double click the .jar to launch, that will typically launch via JRE WHICH WILL ACTUALLY INSTALL WITHOUT ERROR AND THEN BITE YOU when it comes to the Oracle Forms piece… i know, nuts!
    • we went with the default c:\oracle “home” path… which wound up creating a single c:\oracle\middleware folder
  3. Oracle Forms 11.x (v11.1.2.0 is current latest)
    • the x64 zips worked for us…
    • download & extract those zips and fire up the Disk1\setup.exe ELEVATED
    • the prompts are pretty straightforward…
      • and one big important choice is to choose “Install Software – Do Not Configure”… we’ll do the configure step next
      • we stuck with the default “Oracle_FRHome1” path
      • skip security updates (that ship has long since sailed 😉
    • once the install finishes then fire up c:\oracle\middleware\Oracle_FRHome1\bin\config.bat ELEVATED
      • select “configure for development” vs deployment
      • provide the previous weblogic and oracle home paths…
      • we just went with FormInstance1 for the instance path
      • “for development only”, didn’t select the reports bits
      • auto port configuration = yes
  4. Lastly were specific environmental configs, YMMV
    • copy our default.env file to c:\oracle\middleware\user_projects\domains\FORMDomain\config\fmwconfig\servers\AdminServer\applications\formsapp_11.1.2\config
    • copy our formsweb.cfg => c:\oracle\middleware\user_projects\domains\FORMDomain\config\fmwconfig\servers\AdminServer\applications\formsapp_11.1.2\config
    • copy our tnsnames.ora and sqlnet.ora => c:\oracle\middleware\FORMInstance\config
    • copy jacob.jar => c:\oracle\middleware\Oracle_FRHome1\forms\java
    • copy jacob.dll => c:\oracle\middleware\Oracle_FRHome1\forms\webutil (NOT down to either \win32|\win64)
    • CRUCIAL – update registry HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OH1949191890\FORMS_PATH to include your forms “.fmb, .mmb, etc” file paths… we had separate folders for images, forms, libs & menus files to be included there
      • this “KEY_OH1949191890” path is probably different for each installation

PHP SQL Server blob hosting

from here
see this post for sql server driver downloads
 

downloadPDF.php

<?php
header('Content-type: application/pdf');
// leave this out to open directly in browser: header('Content-Disposition: attachment; filename="my.pdf"');
include("SQLConnect.php");
$sql = "select InvoiceDocument from SalesInvoicePDF where InvoiceID = '123'";
$stmt = sqlsrv_query($conn, $sql);
if ( sqlsrv_fetch($stmt) )
{
    //this pulls the first field via "0"
    $data = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
    // write binary sql stream directly to http response
    fpassthru($data);
}
?>

SQLConnect.php

<?php
// DB connection info
$host = "servername";
$user = "user";
$pwd = "pwd";
$db = "database";
// Connect to database.
try {
  /*
  $connPdo = new PDO( "sqlsrv:Server= $host ; Database = $db ", $user, $pwd);
  $connPdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    */

  $conn = sqlsrv_connect($host, array("Database"=>$db, "UID"=>$user, "PWD"=>$pwd));
}
catch(Exception $e){
   die(var_dump($e));
}
?>

Using “C# Interactive” aka CSI/CSX for ETL

motivation

interactive C# offers typical REPL benefits ala powershell without the mental context switch required to leave our beloved C# syntax =)
 

notable

  • great MSDN reference article
  • CSX syntax can be executed from either Visual Studio 2015 (as of update 1) > View > Other Windows > C# Interactive
  • -or- C:\Program Files (x86)\MSBuild\14.0\bin\csi.exe
    • then #load file.csx

Extract

this example is based on a low fidelity web page as the raw data source
… it’s really nice to have all the convenient one liner RESTy methods of System.Net.WebClient available now vs the lower level WebRequest/WebResponse pattern that came with .Net 1.0. E.g. client.DownloadString, DownloadFile, etc.

Transform

trivially demonstrative in this case, simply a string.Split call 🙂

Load

demonstrates leveraging SQL Server’s Table-Valued-Parameter functionality to bulk upload rows which are then conveniently manifested inside the receiving stored procedure as a standard sql rowset, ready for tpyical DML like joining to other tables, etc.
 

SQL definition

CREATE TYPE dbo.SpreaderData_UDT AS TABLE
(
    [SpreaderDataID] [INT] PRIMARY KEY,
    [SpreaderID] [INT],
    [Speed] [INT],
    [Density] [INT],
    [SpreadQty] [INT],
    [Setting] [VARCHAR](100)
)
GO

-- crucial - SQL Server yields a unintuitive error message when this has not been done
GRANT EXECUTE ON TYPE::dbo.SpreaderData_UDT TO PUBLIC
GO

CREATE PROCEDURE [dbo].SpreaderData_Table_u
@SpreaderData dbo.SpreaderData_UDT READONLY -- <= ***** crucial
AS BEGIN

UPDATE sd SET
  sd.Setting = sd2.Setting
FROM dbo.SpreaderData sd
JOIN @SpreaderData sd2 ON sd2.SpreaderDataID = sd.SpreaderDataID

END
GO

scrape.csx

SQL Server PDO PHP7

  1. get the DLL – grab latest x64.zip
  2. add to your php.ini extension list:
    [ExtensionList]
    extension=php_pdo_sqlsrv_7_nts.dll
    
  3. here’s sample call code:
    <?php
    
    try {
         $conn = new PDO( "sqlsrv:Server= ip_address; Database = mydb ", $user, $pwd);
         $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    }
    catch(Exception $e){
         die(var_dump($e));
    }
    
    $stmt = $conn->query($qry);
    $result = $stmt->fetchAll();
    $row = $result[0];
    $colval = $row["fieldname"];
    ?>
    

Shred GPX WPT XML with SQL Server

declare @xml xml = '<gpx>
<wpt lat="35.0977419" lon="-80.89258">
  <name>10112 Industrial Dr. Pineville, NC 28134, Pineville, NC, 28134</name>
  <desc>10112 Industrial Dr, Pineville, NC 28134, USA</desc>
</wpt>
<wpt lat="30.8276466" lon="-83.9967315">
  <name>323 Industrial Blvd Thomasville, GA 31792, Thomasville, GA, 31792</name>
  <desc>323 Industrial Blvd, Thomasville, GA 31792, USA</desc>
</wpt>
<wpt lat="26.4237949" lon="-81.415628">
  <name>283 E Jefferson St Immokalee, FL 34142, Immokalee, FL, 34142</name>
  <desc>283 Jefferson Ave E, Immokalee, FL 34142, USA</desc>
</wpt>
</gpx>'

SELECT 
  n.value('@lat', 'varchar(100)'),
  n.value('@lon', 'varchar(100)'),
  n.value('desc[1]', 'varchar(100)')
FROM @xml.nodes('/gpx/wpt') Rows(n)

namespace example

declare @xml xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">
<dimension ref="A1:H700"/>
<sheetViews>...</sheetViews>
<sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
<cols>...</cols>
<sheetData>
<row r="1" spans="1:8" x14ac:dyDescent="0.25">
<c r="A1">
<v>5637163536</v>
</c>
<c r="B1" t="s">
<v>1014</v>
</c>
</row>
<row r="2" spans="1:8" x14ac:dyDescent="0.25">
<c r="A2">
<v>5637163580</v>
</c>
<c r="B2" t="s">
<v>1288</v>
</c>
</row>
</sheetData>

;WITH XMLNAMESPACES('http://schemas.openxmlformats.org/spreadsheetml/2006/main' AS x)
SELECT 
  n.value('x:c[1]/x:v[1]', 'varchar(100)') as Id,
  n.value('x:c[2]/x:v[1]', 'varchar(100)') as Value
FROM @xml.nodes('x:worksheet/x:sheetData/x:row') Rows(n)

output

Id Value
5637163536 1014
5637163580 1288

SQL Server Aliasing

  • Done via “SQL Server Configuration Manager” > “SQL Native Client vXY.Z Configuration” > Aliases
  • tip: SSMS.exe is a 32bit app (because Visual Studio, upon which it is based, still has a well established justification for 32bit) and therefore it depends on the (32bit) Client Configuration node above to find your server alias
  • For mainstream sql server network client API based connections there is no need to put this alias anywhere else (i.e. not in DNS/hosts file nor AD computers)
  • tip: in AD trusted login context, it seems mandatory to use the name of the actual SQL Server host machine vs just the corresponding ip address; otherwise i would always get bonked with “Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.”

[SOLVED] SSRS (2012), Excel export, “Not a legal OleAut date” error

Cause

For my situation, it wound up being that executing TimeSerial(0,0,secs) in some vbscript code, when the “secs” param value was greater than 24 hours.

Analysis

Somehow out of all the different formats SSRS will export to, the Excel output was the only one running into this issue.

From the TimeSerial specs I don’t see anything inherently invalid about going that high… the docs indicate it simply rolls it into the day portion of the resulting value as I would expect. Given that the error refers to “Ole”, there must be some other intermediate data type conversion going on here… perhaps it passes through a time only type???

DST T-SQL

-- this code returns the start and end datetime’s for DST in the current year
-- DST begins at 2am on the SECOND SUNDAY of March and ends the FIRST SUNDAY of November

SELECT 
  DATEADD(HOUR, (MULTIPLIER + 7 /*skip the first week*/) * 24 /*24 hours a day*/ + 2 /*offset to 2am*/, StartWeek) AS StartDate, 
  DATEADD(HOUR, MULTIPLIER * 24 + 2, EndWeek) AS EndDate 
FROM ( 
  SELECT 0 AS MULTIPLIER, 1 AS DOW UNION ALL 
  SELECT 6, 2 UNION ALL 
  SELECT 5, 3 UNION ALL 
  SELECT 4, 4 UNION ALL 
  SELECT 3, 5 UNION ALL 
  SELECT 2, 6 UNION ALL 
  SELECT 1, 7 
) dow, 
(SELECT 
  '03/01/' + convert(varchar, DATEPART(YEAR, GETDATE())) AS StartWeek, 
  '11/01/' + convert(varchar, DATEPART(YEAR, GETDATE())) AS EndWeek 
) DST 
WHERE DATEPART(WEEKDAY, StartWeek) = DOW

Enable SSL Connections to SQL Server

“SQL Server Transport Encryption” is a good Google phrase for this technology.
Reference: http://msdn.microsoft.com/en-us/library/ms191192.aspx

Obtain an SSL Certificate

A self signed certificate is easy and works fine… here’s one way:

  • Get the IIS 6.0 Resource Kit Tools: http://www.microsoft.com/download/en/confirmation.aspx?id=17275
  • All you’ll need is the “SelfSSL.exe” tool so the custom install is minimal.
  • Find SelfSSL.exe in default install path: C:Program FilesIIS Resources
  • Good reference for SelfSSL usage: http://www.sslshopper.com/article-how-to-create-a-self-signed-certificate-in-iis-7.html, scroll down to “Generate a Self Signed Certificate with the Correct Common Name”
  • Command line example:
    1. SelfSSL /N:CN=MWR-TRO-V2 /V:1999999
    • The /V: part is the validity duration of your cert, in days. I believe 1999999 is the max, which corresponds to around 5475 years in the future (that ought’a last ya 😉
    • The /N:CN= part is the “Common Name” this cert will be tied to… in this case that needs to be the true machine name of your database server.
  • “Do you want to replace the SSL settings for site 1 (Y/N)?” => No
  • Now you have a cert registered in your “personal certificate store” – next we’ll extract it for installation on db server.
Fire up the MMC “Certificates Console” to manage your new cert

  • Good reference: http://support.microsoft.com/kb/276553)
  • Open the MMC console: Start > Run > mmc [enter] (or Windows-R) (MMC Fig.1)
  • Add the cert snapin: click File > Add/Remove Snap-in (MMC Fig.2)
  • select Certificates under Available snap-ins… and hit Add button… (MMC Fig.3)
  • select Computer Account… then Next… (MMC Fig.4)
  • select Local computer, and then Finish… (MMC Fig.5)
  • lastly, hit OK (MMC Fig.6)
  • You may wish to save this MMC configuration for future convenience (MMC Fig.7)
Screenshots…

Export the cert as a pfx file

…to be installed on your database server

  • Certs installed via the above process will be your “Personal > Certificates” folder (Export Fig.1)
  • Right mouse desired certifcate > All Tasks > Export > Next … (Export Fig.2)
  • “Export the private key?” => Yes … Next… (Export Fig.3)
  • Select PFX format, “Include all certs…”, “Export extended”, NOT “Delete…”, Next … (Export Fig.4)
  • Enter a password, hit Next… (Export Fig.5) – ** REMEMBER THAT PASSWORD **
  • Save the pfx file (Export Fig.6)
  • Finish… OK (Export Fig.7)
Screenshots…

Import cert on database server

  • Login to your DB server desktop
  • Launch MMC Cert Console same as above
  • Go to Personal > Certs
  • Right mouse All Tasks > “Import”… (Import Fig.1)
  • Next… (Import Fig.2)
  • Browse… (Import Fig.3)
  • Next … (Import Fig.4)
  • Enter Password, select “Mark this key as exportable”, Next… (Import Fig.5)
  • “Place all certificates in the following store” => Personal… Next… (Import Fig.6)
  • Finish (Import Fig.7)
    Screenshots…

    Activate SSL encryption on DB server

    • Fire up SQL Server’s Network Configuration Utility
      • Start  > All Programs > Microsoft SQL Server {version} > Configuration Tools > SQL Server Configuration Manager” 
    • then under “SQL Server Network Configuration”
    • Right click “Protocols for MSSQLServer”
    • select “Properties”
    • set  “Flags tab > Force Encryption” to Yes
    • and select the installed cert on the “Certificates” tab
    • voila!
    • STOP AND RESTART THE SQL SERVER SERVICE
    • login to the instance with an SSMS Query window
    • fire this command to verify all connections are encrypted:
      1. SELECT encrypt_option, * FROM sys.dm_exec_connections WHERE session_id = @@SPID
    • Tip: SP_WHO2 is handy for obtaining spids