Blog posts by Gonzalo Gonzalez2010-04-09T18:15:00.0000000Zhttps://world.optimizely.com/blogs/Gonzalo-Gonzalez/Optimizely WorldUpdating configuration to run CMS 6 on Oracle database<p>If you created a site without a database (via EPiServer Deployment Center) or even have an existing CMS site which uses SQL Server as its database engine, you may want to run it against an Oracle database instead. The files available for download <a href="/link/e936ccdab2e04f70a19cc77b02294c00.zip">here</a> will take the hard slog out of manually changing the CMS configuration files (amoungst other things) so your site works with Oracle.</p>
<p>Copy the contents of the zip file to a new folder called "Oracle" under the <Program Files>\EPiServer\CMS\6.0.530.0\Install\Modules folder. Note that on 64 bit operating systems the EPiServer folder can be found under the <Program Files (x86)> folder:</p>
<p> </p>
<p><a href="/link/2a70028e0504407face690ea4b0718c0.jpg"><img style="BORDER-RIGHT-WIDTH: 0px; DISPLAY: inline; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px" title="clip_image002" border="0" alt="clip_image002" src="/link/3afc30674209485bb6c493457952d212.jpg" width="267" height="244" /></a></p>
<p> The next time your run EPiServer Deployment Center you should notice a new item called "Install Oracle Configuration" under the EPiServer CMS -> Version 6.0.530.0 node. Select the item and then click on 'Run'.</p>
<p><a href="/link/c1dfe3d15652495aa4f359f3b13d3678.jpg"><img style="BORDER-RIGHT-WIDTH: 0px; DISPLAY: inline; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px" title="clip_image001" border="0" alt="clip_image001" src="/link/61e074e704e9483fbc260b22ee598c22.jpg" width="395" height="289" /></a></p>
<p>Now a list of CMS 6 sites installed on the computer should be shown. Select the site to be changed and click 'Install'</p>
<p><a href="/link/aaa7ad458b4e4b8581f94795f8858203.png"><img style="BORDER-RIGHT-WIDTH: 0px; DISPLAY: inline; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px" title="image" border="0" alt="image" src="/link/d5df8c86509c4fad9c2629d4cbb5db0d.png" width="244" height="147" /></a> </p>
<p>The PowerShell script will update the configuration for you and even move the Oracle Workflow assemblies to the bin folder of the selected site.</p>
<p>The only things left to do is install the CMS Oracle database and then update the sites' <a href="http://world.episerver.com/Documentation/Items/Installation-Instructions/EPiServer-CMS/Version-6/EPiServer-CMS-60/Oracle-Database---Configuration/">connectionStrings.config</a> to point to it and use the correct user / password.</p>
<p>Done!!!</p>2010-04-09T18:15:00.0000000ZBlog postUpgrading an EPiServer CMS 5 R2 SP2 Site to CMS6 when running Oracle<p>If you’re running your EPiServer CMS 5 R2 SP2 site on Oracle and want to get all the advantages of CMS 6, you can easily upgrade the site using the <a href="/link/5bddea5dae16447e94a7b327f70884e9.zip">these</a> files:</p>
<p>1. The zip packet includes 3 files:</p>
<p> - Upgrade Site.metadata</p>
<p> - Upgrade Site (Oracle).ps1</p>
<p> - Upgrade_Oracle_5sp2To6.sql</p>
<p> - EPiServerUpgrade.CMS5.dll</p>
<p>2. Copy the 'Upgrade Site.metadata' and 'Upgrade Site (Oracle).ps1' files to the <Program Files>\ EPiServer\CMS\6.0.530.0\Upgrade\System Scripts folder as shown below:</p>
<p><a href="/link/d6f7b3f902cb4743806b4c31ed605a84.png"><img style="BORDER-RIGHT-WIDTH: 0px; DISPLAY: inline; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px" title="image" border="0" alt="image" src="/link/cbdedd1cb7274a31b1b815753f4af38a.png" width="210" height="244" /></a> </p>
<p>3. Copy the EPiServerUpgrade.CMS5.dll file to the <Program Files>\ EPiServer\CMS\6.0.530.0\Install\Tools folder. Note that this should replace the existing one.</p>
<p>On a 64 bit operation system the EPiServer folder hive is located in the <Program Files (x86)> folder.</p>
<p>4. Copy the sql file to a convenient folder as it will be needed in step 6 below.</p>
<p>5. The EPiServerUpgrade.CMS5.dll file needs to be registered in the machine's Global Assembly Cache. To do this run the following command from a command prompt (Administrator privileges will be required for this on Windows Vista, Windows 7 and Windows Server 2008 / 2008 R2). This also implies that the gacutil utility is also installed on the current computer:</p>
<p> - gacutil -i "<Program Files>\ EPiServer\CMS\6.0.530.0\Install\Tools\EPiServerUpgrade.CMS5.dll"</p>
<p>Replace the <Program Files> part with your actual program files (or program files x86) folder path.</p>
<p>6. First you should upgrade the database with the latest schema. It is very important to ensure you <strong>back-up</strong> your CMS database before you perform the following steps.</p>
<p> - Connect to your <OracleEpiserverUser> through sqlplus.exe.</p>
<p> - Run the script Upgrade_Oracle_5sp2To6.sql from the location you copied it to.</p>
<p> - Make sure you don’t get any error running this script.</p>
<p>7. Start the EPiServer Deployment Center and you should find a new option called “<strong>Upgrade site with Oracle database</strong>”:</p>
<p><a href="/link/81840fdbbebe4d1cad64c2ba26845ab8.png"><img style="BORDER-RIGHT-WIDTH: 0px; DISPLAY: inline; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px" title="image" border="0" alt="image" src="/link/d50e9244a9674d149831560e4cfe949a.png" width="396" height="283" /></a> </p>
<p>Select this item and click on the 'Run' button. Now a list of possible CMS 5 R2 SP2 sites installed on the machine should be shown:</p>
<p><a href="/link/c6998c80d0ed4e47ba1bdf32454020b5.png"><img style="BORDER-RIGHT-WIDTH: 0px; DISPLAY: inline; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px" title="image" border="0" alt="image" src="/link/45004a149a944780a7a2848ae4f1fcff.png" width="234" height="244" /></a> </p>
<p>Select the site to be upgraded and click on 'Next'. </p>
<p>8. Once the deployment script has finished you should be able to browse your upgraded site. You’ll find a few look and feel differences in Edit and Admin mode plus the new shiny OnlineCenter as shown below:</p>
<p><a href="/link/4943386d833d44a78368913b1aaf55b0.png"><img style="BORDER-RIGHT-WIDTH: 0px; DISPLAY: inline; BORDER-TOP-WIDTH: 0px; BORDER-BOTTOM-WIDTH: 0px; BORDER-LEFT-WIDTH: 0px" title="image" border="0" alt="image" src="/link/e51f2376195b49f6a2969ae93e2a9c15.png" width="376" height="201" /></a> </p>
<p><strong>Note: </strong>If you need to upgrade from an older version of CMS than <strong>CMS 5 R2 SP2</strong> please contact us via support: <a href="mailto:support@ep.se">support@ep.se</a></p>
<p>Enjoy the new version!!!!</p>2010-03-25T10:50:00.0000000ZBlog postEPiServer CMS6 on Oracle moving on to ODP.NET<blockquote>
<div id="codeSnippetWrapper">On 15th June 2009, Microsoft annouced their decision to <a href="http://blogs.msdn.com/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx">deprecate their System.Data.OracleClient</a> assembly. As an EPiServer developer working with Oracle, you’ll probably know that EPiServer CMS 5 uses this assembly. We decided to change direction in EPiServer CMS 6 and use the ODP.NET ADO.NET provider from the Oracle Corporation.</div>
<p>In recent years, ODP.NET has added lots of <a href="http://www.oracle.com/technology/tech/windows/odpnet/newfeatures.html">new features</a>, such as performance tuning, user-defined types, advanced queuing, RAC connection pooling, and supporting multiple ODP.NET client versions simultaneously on the same machine. </p>
<p>Time to move on! </p>
<p>Below you can find some “ugly” peaces of code done with the previous data provider, due to differences between .NET CLR Types and Oracle data types:</p>
<p>E.g. Creation of a Clob parameter:</p>
<div><pre style="BORDER-BOTTOM-STYLE: none; TEXT-ALIGN: left; PADDING-BOTTOM: 0px; LINE-HEIGHT: 12pt; BORDER-RIGHT-STYLE: none; BACKGROUND-COLOR: #f4f4f4; MARGIN: 0em; PADDING-LEFT: 0px; WIDTH: 214.05%; PADDING-RIGHT: 0px; FONT-FAMILY: 'Courier New', courier, monospace; DIRECTION: ltr; BORDER-TOP-STYLE: none; HEIGHT: 432px; COLOR: black; FONT-SIZE: 8pt; BORDER-LEFT-STYLE: none; OVERFLOW: visible; PADDING-TOP: 0px" id="codeSnippet"><span style="COLOR: #0000ff">try</span><br />{<br /> <span style="COLOR: #008000">// WARNING! This is the ugliest piece of code I have ever written... </span><br /> <span style="COLOR: #008000">// not for the faint-of-heart.</span><br /> <span style="COLOR: #008000">// Believe it or not, but this is the way to handle large data</span><br /> <span style="COLOR: #008000">// fields in Oracle :-(</span><br /> cmd.CommandText = <span style="COLOR: #006080">"declare xx clob; <br /> begin dbms_lob.createtemporary(xx, false, 0);<br /> :tempblob := xx; end;"</span>;<br /> cmd.Parameters.Add(<span style="COLOR: #0000ff">new</span> OracleParameter(<span style="COLOR: #006080">"tempblob"</span>, OracleType.Clob)).Direction = <br /> ParameterDirection.Output;<br /> cmd.ExecuteNonQuery();<br /> OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;<br /><br /> <br /> System.Text.UnicodeEncoding Unicode = <span style="COLOR: #0000ff">new</span> System.Text.UnicodeEncoding();<br /> <span style="COLOR: #0000ff">byte</span>[] tempbuff = Unicode.GetBytes(paramValue);<br /> tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);<br /> tempLob.Write(tempbuff, 0, tempbuff.Length);<br /> tempLob.EndBatch();<br /> param = <br /> <span style="COLOR: #0000ff">new</span> OracleParameter(ProviderSpecificParameterName(parameterName), OracleType.Clob);<br /> ((OracleParameter)param).Value = tempLob;<br /> <span style="COLOR: #0000ff">if</span> (localTransaction)<br /> {<br /> cmd.Transaction.Commit();<br /> }<br />}<br /><br /></pre></div>
<div> </div>
<div> E.g Creation of a Blob Parameter:</div>
<div> </div>
<div><pre style="BORDER-BOTTOM-STYLE: none; TEXT-ALIGN: left; PADDING-BOTTOM: 0px; LINE-HEIGHT: 12pt; BORDER-RIGHT-STYLE: none; BACKGROUND-COLOR: #f4f4f4; MARGIN: 0em; PADDING-LEFT: 0px; WIDTH: 184.47%; PADDING-RIGHT: 0px; FONT-FAMILY: 'Courier New', courier, monospace; DIRECTION: ltr; BORDER-TOP-STYLE: none; HEIGHT: 400px; COLOR: black; FONT-SIZE: 8pt; BORDER-LEFT-STYLE: none; OVERFLOW: visible; PADDING-TOP: 0px" id="codeSnippet"><br /><br /><span style="COLOR: #0000ff">if</span> (serializedData.Length < 32000)<br />{<br /> CreateParameter(cmd, <span style="COLOR: #006080">"p_ItemData"</span>, OracleType.Blob, serializedData);<br />}<br /><span style="COLOR: #0000ff">else</span><br />{<br /> <span style="COLOR: #008000">// WARNING! This is the ugliest piece of code I have ever copied.</span><br /> OracleCommand tmpCmd = CreateCommand(<span style="COLOR: #006080">"declare xx blob; begin <br /> dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"</span>);<br /> tmpCmd.CommandType = CommandType.Text;<br /> tmpCmd.Parameters.Add(<span style="COLOR: #0000ff">new</span> OracleParameter(<br /> <span style="COLOR: #006080">"tempblob"</span>, OracleType.Blob)).Direction = ParameterDirection.Output;<br /> tmpCmd.ExecuteNonQuery();<br /><br /> OracleLob tempLob = (OracleLob)tmpCmd.Parameters[0].Value;<br /> tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);<br /> tempLob.Write(serializedData, 0, serializedData.Length);<br /> tempLob.EndBatch();<br /><br /> tmpCmd.Parameters.Clear();<br /> tmpCmd.Dispose();<br /><br /> OracleParameter param = <br /> <span style="COLOR: #0000ff">new</span> OracleParameter(<span style="COLOR: #006080">"p_ItemData"</span>, OracleType.Blob);<br /> ((OracleParameter)param).Value = tempLob;<br /> cmd.Parameters.Add(param);<br />}<br /><br /></pre> </div>
<div> </div>
<div> </div>
<div> </div>
<div>What does it look like now?</div>
<div> </div>
<div>for Blob:</div>
<div><pre style="BORDER-BOTTOM-STYLE: none; TEXT-ALIGN: left; PADDING-BOTTOM: 0px; LINE-HEIGHT: 12pt; BORDER-RIGHT-STYLE: none; BACKGROUND-COLOR: #f4f4f4; MARGIN: 0em; PADDING-LEFT: 0px; WIDTH: 100%; PADDING-RIGHT: 0px; FONT-FAMILY: 'Courier New', courier, monospace; DIRECTION: ltr; BORDER-TOP-STYLE: none; COLOR: black; FONT-SIZE: 8pt; BORDER-LEFT-STYLE: none; OVERFLOW: visible; PADDING-TOP: 0px" id="codeSnippet">CreateParameter(cmd, <span style="COLOR: #006080">"p_ItemData"</span>, OracleDbType.Blob, serializedData);</pre></div>
<div> </div>
<div>Internally CreateParameter create an Oracle Parameter associated to the command(cmd):</div>
<div> </div>
<div>
<div id="codeSnippetWrapper"><pre style="BORDER-BOTTOM-STYLE: none; TEXT-ALIGN: left; PADDING-BOTTOM: 0px; LINE-HEIGHT: 12pt; BORDER-RIGHT-STYLE: none; BACKGROUND-COLOR: #f4f4f4; MARGIN: 0em; PADDING-LEFT: 0px; WIDTH: 100%; PADDING-RIGHT: 0px; FONT-FAMILY: 'Courier New', Courier, Monospace; DIRECTION: ltr; BORDER-TOP-STYLE: none; COLOR: black; FONT-SIZE: 8pt; BORDER-LEFT-STYLE: none; OVERFLOW: visible; PADDING-TOP: 0px" id="codeSnippet"><span style="COLOR: #0000ff">private</span> <span style="COLOR: #0000ff">static</span> <span style="COLOR: #0000ff">void</span> CreateParameter(OracleCommand command,<br /> String parameterName,<br /> OracleDbType parameterType,<br /> Object parameterValue,<br /> ParameterDirection parameterDirection)<br /> {<br /> OracleParameter param = command.CreateParameter();<br /> param.ParameterName = parameterName;<br /> param.OracleDbType = parameterType;<br /> param.Value = parameterValue;<br /> param.Direction = parameterDirection;<br /><br /> command.Parameters.Add(param);<br /> }</pre><br /> </div></div>
<div>for Clob:</div>
<div id="codeSnippetWrapper"><pre style="BORDER-BOTTOM-STYLE: none; TEXT-ALIGN: left; PADDING-BOTTOM: 0px; LINE-HEIGHT: 12pt; BORDER-RIGHT-STYLE: none; BACKGROUND-COLOR: #f4f4f4; MARGIN: 0em; PADDING-LEFT: 0px; WIDTH: 100%; PADDING-RIGHT: 0px; FONT-FAMILY: 'Courier New', courier, monospace; DIRECTION: ltr; BORDER-TOP-STYLE: none; COLOR: black; FONT-SIZE: 8pt; BORDER-LEFT-STYLE: none; OVERFLOW: visible; PADDING-TOP: 0px" id="codeSnippet">param = <span style="COLOR: #0000ff">new</span> OracleParameter(ProviderSpecificParameterName(parameterName),<br />OracleDbType.Clob);<br /></pre><br /></div>
<div id="codeSnippetWrapper">Much better!!!</div>
<div> </div>
<div>There are some misconceptions to clarify:<strong> </strong></div>
<div><strong>Misconception</strong>: "I have to download a particular version of ODP.NET depending on the version of my database." <br /><strong>Fact</strong>: Any version of ODP.NET works with any version of Oracle Database.</div>
<div><strong></strong></div>
<div><strong>Misconception</strong>: "EPiServer is compiled against <a href="http://www.oracle.com/technology/software/tech/windows/odpnet/index.html">ODAC 11.1.0.6.21</a>( Oracle.DataAccess 2.111.6.0.) I'm in big trouble!" <br /><strong>Fact: </strong>Multiple versions of ODP.NET(Oracle Clients) can live on the same box and the application can target whatever specific one you need. You can of course also use assembly redirects.</div>
<div><strong>Misconception</strong>: "This means I have to pay for an Oracle provider now!" <br /><strong>Fact</strong>: ODP.NET is free!</div>
<div>You’ll find more information in the <a href="http://www.oracle.com/technology/tech/dotnet/msoc/index.html">ODP.NET for Microsoft OracleClient Developers webpage</a>.</div>
<div>Happy coding:)</div></blockquote>
<div> </div>
<p> </p>2010-03-15T11:49:00.0000000ZBlog post