Santosh's profileSantosh KumarPhotosBlogListsMore  |  Tools Help |
ASP.NET, Designing one of the most Generalized Navigation System using XML and Menu Control in .NET Framework 2.0Microsoft has provided various ways to handle the navigations within the application. Menu Control is one amongst the best. Here in this article we are going to find a way that will handle N number of menus and its N number of navigation level with no change in the source code. The code given here is taking input from an XML file. So, let us see what is what. You need to put a Menu Control and rename its ID as MenuOne. Every other bit of aspx code is written for designing purpose only. If you are interested in this design then download the small '.gif' files given below. The server side code within AddMainMenu() and AddAllSubMenus() procedures are taking care of building the entire menu. And the two importantant things I would like to discuss of data structure of XML file are (1) m01 tag and (2) common nodes. The m01 tag is included for different types of menu plans. Say for example, you have 3 three category of users and their usage of menu is not same. So you can extend your menu plan to m02, m03, m04... ... ... Each of the nodes contains required information about a particular menu or submenu. The mNode must contain mTxt, mVal, mUrl, and mImg. Apart from these an mNode tag can contain another mNode to built sub levels. There is nothing much to discuss about. Find the code to experiment this article.  
---[ ASPX ]--------------------------------------------------------------------- <%@ Page Language="VB" AutoEventWireup="false" CodeFile="mymenutest.aspx.vb" Inherits="mymenutest" %> <html> <head runat="server"> <title>Menu Example</title> <style> td tr table{ font-family: Verdana; font-size: 8pt; } .mrgn_lr{ font-family: Verdana; font-size: 8pt; background-color: #555555; } .mrgn_t{ font-family: Verdana; font-size: 8pt; color: #ff3333; border-top: solid 3px #ff3333; } .mrgn_b{ font-family: Verdana; font-size: 8pt; color: #ffdddd; background-color: #ff3333; } .smis{ font-size: 8pt; background-image: url('app_image/xpback.gif'); background-attachment:left; background-repeat:repeat-y; } .dcmnt{ font-family: Verdana; font-size: 8pt; background-color: #ffffff; } .mnbr{ background-color:#FFF2F2; border-right: #ffcaca 1px; border-top: #ffcaca 1px solid; border-left: #ffcaca 1px; border-bottom: #ffcaca 1px solid; } </style> </head> <body topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor=#FFF7F7> <form id="form1" runat="server"> <table border="0" cellpadding="0" cellspacing="0" height="100%" width="100%"> <tr> <td width="10%" class="mrgn_lr" > </td> <td valign=top style="border-right: solid 1px #ffcaca; border-left: solid 1px #ffcaca" > <table border="0" cellpadding="0" cellspacing="0" width=100% height=100%> <tr> <td height="50px" class=mrgn_t align=right> <B>M E N U E X A M P L E </B> </td> </tr> <tr> <td align="center" > <table border="0" cellpadding="0" cellspacing="0" width=100%> <tr> <td align="left" class=mnbr> <asp:Menu ID="MenuOne" runat="server" Orientation="Horizontal" BackColor="#FFF7F7" ForeColor="black" BorderWidth="0" BorderColor="#FFCACA" BorderStyle="Solid" DynamicMenuStyle-BorderWidth="1" DynamicMenuStyle-BorderColor="black" DynamicMenuStyle-BorderStyle="Solid" DynamicMenuStyle-CssClass="smis" StaticEnableDefaultPopOutImage="False"> <StaticSelectedStyle BackColor="#FFf2f2" /> <StaticHoverStyle BackColor="#990000" Font-Bold="False" ForeColor="White" /> <StaticMenuItemStyle HorizontalPadding="10px" VerticalPadding="3px" /> <DynamicMenuStyle BackColor="#FFF7F7" BorderColor="#FFCACA" BorderStyle="Solid" BorderWidth="1px" /> <DynamicSelectedStyle /> <DynamicMenuItemStyle HorizontalPadding="5px" VerticalPadding="3px" /> <DynamicHoverStyle BackColor="#990000" Font-Bold="False" ForeColor="White" /> </asp:Menu> </td> </tr> </table> </td> </tr> <tr height=100% class=dcmnt> <td align="center" height=100%> </td> </tr> <tr height="21px" > <td class=mrgn_b align=right> Copyright not applicable </td> </tr> </table> </td> <td width="10%" class="mrgn_lr" > </td> </tr> </table> </form> </body> </html> ---[ VB ]----------------------------------------------------------------------- Imports System Imports System.IO Imports System.Xml Imports System.Data Partial Class mymenutest Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load AddMainMenu("01") End Sub Protected Sub AddMainMenu(ByVal vMenuPlan as String) Dim mTxt As String Dim mVal As String Dim mUrl As String Dim mImg As String Dim xDoc As New XmlDocument xDoc.Load(Server.MapPath("~/app_data/APPMenu.xml")) Dim xNod As XmlNodeList = xDoc.SelectNodes("app/m" & vMenuPlan & "/mNode") Dim mCtr As Integer, mCnt As Integer mCnt = xNod.Count - 1 MenuOne.Items.Clear() MenuOne.Orientation = Orientation.Horizontal For mCtr = 0 To mCnt mTxt = xNod(mCtr).Item("mTxt").InnerText mVal = xNod(mCtr).Item("mTxt").InnerText mUrl = xNod(mCtr).Item("mUrl").InnerText mImg = xNod(mCtr).Item("mImg").InnerText MenuOne.Items.Add(New MenuItem(mTxt, mVal, mImg, mUrl)) If xNod(mCtr).SelectNodes("mNode").Count > 0 Then AddAllSubMenus(xNod(mCtr).SelectNodes("mNode"), MenuOne.Items(mCtr)) End If Next End Sub Protected Sub AddAllSubMenus(ByVal xNod As XmlNodeList, ByRef mNod As MenuItem) Dim mTxt As String Dim mVal As String Dim mUrl As String Dim mImg As String Dim mCtr As Integer, mCnt As Integer mCnt = xNod.Count - 1 For mCtr = 0 To mCnt mTxt = xNod(mCtr).Item("mTxt").InnerText mVal = xNod(mCtr).Item("mTxt").InnerText mUrl = xNod(mCtr).Item("mUrl").InnerText mImg = xNod(mCtr).Item("mImg").InnerText mTxt = " " & mTxt If mImg = "" Then mImg = "~/app_image/xpvide.gif" mNod.ChildItems.Add(New MenuItem(mTxt, mVal, mImg, mUrl)) If xNod(mCtr).SelectNodes("mNode").Count > 0 Then AddAllSubMenus(xNod(mCtr).SelectNodes("mNode"), mNod.ChildItems(mCtr)) End If Next End Sub End Class ---[ XML ]---------------------------------------------------------------------- <?xml version="1.0" encoding="utf-8" ?> <app> <m01> <mNode> <mTxt>File</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg></mImg> <mNode> <mTxt>Search</mTxt> <mVal></mVal> <mUrl>http://www.live.com</mUrl> <mImg></mImg> </mNode> <mNode> <mTxt>Open</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg>~/app_image/xpdir.gif</mImg> </mNode> <mNode> <mTxt>Save</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg>~/app_image/xpfile.gif</mImg> </mNode> <mNode> <mTxt>Recent</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg></mImg> <mNode> <mTxt>File1.txt</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg></mImg> </mNode> <mNode> <mTxt>File1.doc</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg></mImg> </mNode> <mNode> <mTxt>File1.xls</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg></mImg> </mNode> </mNode> <mNode> <mTxt>Close</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg></mImg> </mNode> </mNode> <mNode> <mTxt>Edit</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg></mImg> </mNode> <mNode> <mTxt>View</mTxt> <mVal></mVal> <mUrl></mUrl> <mImg></mImg> </mNode> </m01> </app> »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Java, Step-by-Step Guidelines for Developing a JSP Web Application using Oracle 9.2.0.1.0 EE, J2SDK 1.4.2 and JBOSS 4.0.2 Application ServerHi friends! Get ready! We are going to develop a JSP web application using JBoss application server with Oracle database. Here in this, the article aims to help those people who are aware of java technologies but not at its core level and doing development by being dependent of existing frameworks. This article is written with an objective to provide required related information at one place so that the journey from the installation to browser based output can become clear to java techies. And is based on the following technical specifications, have a look. Operating System: Window 2000 Professional Browser: Internet Application 6 Service Pack 1 Software Development Kit: j2sdk-1.4.2 (http://java.sun.com/j2se/1.4.2/download.html) Application Server: jboss 4.0.2 (http://www.jboss.org/jbossas/downloads/) Database: Oracle 9.2.0.1.0 EE Step 1: Installation Download the JDK and JBOSS from the given links above. Create a folder e.g. ‘_java’ in a drive e.g. ‘e:’ and extract the both in this. So your folders are e:\_java\j2sdk-1.4.2 and e:\_java\jboss-4.0.2. Installation of Operating System, Oracle and Internet Explorer is not discussed here. Step 2: Environment Settings Yaa! There you are! Even though you do every thing correctly, improper environment setting will burn your brain by giving thousands of errors to confuse you. So what do you need to do? Firstly, Copy JDBC lib. Secondly, set the environment variables JAVA_HOME, CLASSPATH and PATH to let different level of compilers know where to seek for required BINs, JREs and LIBs Copy JDBC libraries Go to oracle_home directory Expand up to \ora92\jdbc\lib Select ojdbc14.jar (it is suggested to select all) Copy to e:\_java\j2sdk-1.4.2\lib Set Environments Go to my_computers -> right_click -> properties -> advanced Click environment_variables button Environment Variable window will come up Go to system_variables -> new Type CLASSPATH at variable_name box Type e:\_java\j2sdk-1.4.2\lib\ojdbc14.jar; at variable_value box Go to system_variables -> new Type JAVA_HOME at variable_name box Type e:\_java\j2sdk-1.4.2; at variable_value box Go to system_variables -> edit Select PATH variable Add e:\_java\j2sdk-1.4.2\bin;E:\_java\j2sdk-1.4.2\jre; at the beginning of existing value at variable_value box  Step 3: Create the web directory Go to e:\_java\jboss-4.0.2 folder and expand it up to e:\_java\jboss-4.0.2\server\default\deploy. Now create a war folder e.g. ‘myweb.war’. Remember this is a folder you will refer as your application URL i.e. http://localhost:xxxx/myweb later in this article. One more thing, copy the WEB-INF folder from jmx-console.war folder to myweb.war and delete now myweb.war\WEB-INF\org folder, we don’t need it  Step 4: Designing the Package Whatever you call it, whether framework, whether layer, whether assembly, whether class, whether dll; they are nothing but class files that will be required for the execution of event triggers, fired directly or indirectly. So lets create the structure. Thing are case-sensitive, take care while creating folder and files and writing codes as well. To make it more explanatory, let us make some hierarchal layers, which will be embedded within the package. You can make it folder wise parallel later on. All Right! Fine! So there will be four layers and three of them are (1) Statement layer (2) Data layer (3) Communication layer. To do this, go to folder e:\_java\jboss-4.0.2\server\default\deploy\myweb.war\WEB-INF\classes and create a folder ‘Smt’ for statement layer. Get inside Smt folder and create a folder ‘Data’ for data layer. Get inside Data folder and create a folder ‘Com’. Step 5: Writing Codes for Class files Com layer Goto to myweb.war\WEB-INF\classes\Smt\Data\Com folder Create a file Com.java and write the following code. //file: Com.java package Smt.Data.Com; import java.sql.*; import java.lang.*; public class Com { public static Statement GetStatement() throws ClassNotFoundException, SQLException{ Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ora9i","scott", "tiger"); conn.setAutoCommit(false); return conn.createStatement(); } } Data layer Goto to myweb.war\WEB-INF\classes\Smt\Data folder Create a file Data.java and write the following code. //file: Data.java package Smt.Data; import java.sql.*; import java.lang.*; import Smt.Data.Com.*; public class Data { private static Com objCom = new Com(); public static String GetTableData(int Cols, String vQry) throws ClassNotFoundException, SQLException{ Statement stmt = objCom.GetStatement(); String vRet = ""; int i = 0; ResultSet rset = stmt.executeQuery(vQry); while (rset.next()) { vRet = vRet + "<tr>"; for (i=1; i<=Cols; i++){ vRet = vRet + "<td style='font-size: 8pt;'>" + rset.getString(i) + "</td>"; } vRet = vRet + "</tr>"; } stmt.close(); return vRet; } } Smt layer Goto to myweb.war\WEB-INF\classes\Smt folder Create files SmtPage1.java & SmtPage2.java and write the following code. //file: SmtPage1.java package Smt; import java.sql.*; import java.lang.*; import Smt.Data.*; public class SmtPage1 { private static Data objData = new Data(); public static String GetEmpDetails() throws ClassNotFoundException, SQLException{ return objData.GetTableData(6, "select empno, hiredate, sal, dname, loc, mgr from emp, dept where emp.deptno=dept.deptno"); } } //file: SmtPage2.java package Smt; import java.sql.*; import java.lang.*; import Smt.Data.*; public class SmtPage2 { private static Data objData = new Data(); public static String GetEmpDetails() throws ClassNotFoundException, SQLException{ return objData.GetTableData(6, "select empno, hiredate, sal, ename, job, mgr from emp"); } } Step 6: Compiling Class files Till now we have not compiled anything. Lets do it. You don’t need to compile each of the layers individually. When you compile the top most packages, it will compile the related package automatically. Better to create a batch file to compile the things. Get to folder e:\_java\jboss-4.0.2\server\default\deploy\myweb.war\WEB-INF\classes\Smt and create cSmtPage1.bat and cSmtPage2.bat with the following codes respectively. Rem file: cSmtPage1.bat @echo off java -version echo. set classpath=E:\_java\jboss-4.0.2\server\default\deploy\myweb.war\WEB-INF\classes;%classpath% echo jv_home : %java_home% echo clspath : %classpath% echo winpath : %path% echo. echo Compiling... echo. javac SmtPage1.java echo Running... java Smt.SmtPage1 pause Rem file: cSmtPage2.bat @echo off java -version echo. set classpath=E:\_java\jboss-4.0.2\server\default\deploy\myweb.war\WEB-INF\classes;%classpath% echo jv_home : %java_home% echo clspath : %classpath% echo winpath : %path% echo. echo Compiling... echo. javac SmtPage2.java echo Running... java Smt.SmtPage2 pause Just note the CLASSPATH. Now execute these files one by one by double clicking each of them. It will create related class files inside each of the folder hierarchy. And as there is no main() in any of our java files, there exists no fault if you get the following exceptions Exception in thread "main" java.lang.NoSuchMethodError: main  Step 7: Creating JSP files Now the fourth layer i.e. Presentation layer. We need to create three JSP files to see the differences i.e. index.jsp, page1.jsp and pag2.jsp. index.jsp will be the default page that will be served by the application server. The two other JSP files will interact with the database. This article is using only select statements, you can expand you page functionalities by insert, update and delete operations because by the time you can do that yourself. All right! Lets see the codes <!--file=index.jsp--> <%@ page language="java" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>JDBC With THIN Driver in JSP</title> </head> <body leftmargin="0" topmargin="0" rightmargin=0 bottommargin=0 style="color: #333366; font-family: verdana;"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <tr> <td width="70px" style="background-color: #ececec;"> </td> <td> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <tr height="75px"> <td style="border-bottom: #B8101F 3px solid; font-size: 8pt; color: #B8101F;" align="center"> <table width="90%"><tr> <td style="font-size: 8pt; color: #B8101F;" align="left"> <b> <br> </b> </td> <td style="font-size: 8pt; color: #B8101F;" align="right"> <b><a href=page1.jsp>Page One</a> <br><a href=page2.jsp>Page Two</a></b> </td> </tr></table> </td> </tr> <tr> <td valign="middle" align="center" style="font-size: 14pt; color: #1F10B8;"> Hi...!!!<br> <br> And Congrates...!!!<br> Your first JSP Application is Ready <br> And is working fine<br> <br> </td> </tr> <tr height="25px"> <td style="border-top: #B8101F 3px solid;"> </td> </tr> </table> </td> <td width="70px" style="background-color: #ececec;"> </td> </tr> </table> </body> </html> <!--file=page1.jsp--> <%@ page language="java" %> <%@ page import="Smt.SmtPage1" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <% SmtPage1 myObj = new SmtPage1(); String s = myObj.GetEmpDetails(); %> <title>JDBC With THIN Driver in JSP</title> </head> <body leftmargin="0" topmargin="0" rightmargin=0 bottommargin=0 style="color: #333366; font-family: verdana;"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <tr> <td width="70px" style="background-color: #ececec;"> </td> <td> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <tr height="75px"> <td style="border-bottom: #B8101F 3px solid; font-size: 8pt; color: #B8101F;" align="center"> <table width="90%"><tr> <td style="font-size: 8pt; color: #B8101F;" align="left"> <b><a href=index.jsp>Home</a> <br> </b> </td> <td style="font-size: 8pt; color: #B8101F;" align="right"> <b><a href=page1.jsp>Page One</a> <br><a href=page2.jsp>Page Two</a></b> </td> </tr></table> </td> </tr> <tr> <td valign="top" align="center"> <br> <table style="border: #dcdcdc 1px solid;" width="90%"> <tr> <td align="center" width="100%"> <table style="border: #dcdcdc 1px solid;" cellpadding="5px" width="100%"> <tr> <td align="center"> <b style="font-size: 8pt;">Emp Details</b > </td> </tr> </table> </td> </tr> <tr> <td valign="top" align="center"> <table style="border: #dcdcdc 1px solid;" cellpadding="2px" width="100%"> <%= s %> </table> </td> </tr> </table> </td> </tr> <tr height="25px"> <td style="border-top: #B8101F 3px solid;"> </td> </tr> </table> </td> <td width="70px" style="background-color: #ececec;"> </td> </tr> </table> </body> </html> <!--file=page2.jsp--> <%@ page language="java" %> <%@ page import="Smt.SmtPage2" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <% SmtPage2 myObj = new SmtPage2(); String s = myObj.GetEmpDetails(); %> <title>JDBC With THIN Driver in JSP</title> </head> <body leftmargin="0" topmargin="0" rightmargin=0 bottommargin=0 style="color: #333366; font-family: verdana;"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <tr> <td width="70px" style="background-color: #ececec;"> </td> <td> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <tr height="75px"> <td style="border-bottom: #B8101F 3px solid; font-size: 8pt; color: #B8101F;" align="center"> <table width="90%"><tr> <td style="font-size: 8pt; color: #B8101F;" align="left"> <b><a href=index.jsp>Home</a> <br> </b> </td> <td style="font-size: 8pt; color: #B8101F;" align="right"> <b><a href=page1.jsp>Page One</a> <br><a href=page2.jsp>Page Two</a></b> </td> </tr></table> </td> </tr> <tr> <td valign="top" align="center"> <br> <table style="border: #dcdcdc 1px solid;" width="90%"> <tr> <td align="center" width="100%"> <table style="border: #dcdcdc 1px solid;" cellpadding="5px" width="100%"> <tr> <td align="center"> <b style="font-size: 8pt;">Emp Details</b> </td> </tr> </table> </td> </tr> <tr> <td valign="top" align="center"> <table style="border: #dcdcdc 1px solid;" cellpadding="2px" width="100%"> <%= s %> </table> </td> </tr> </table> </td> </tr> <tr height="25px"> <td style="border-top: #B8101F 3px solid;"> </td> </tr> </table> </td> <td width="70px" style="background-color: #ececec;"> </td> </tr> </table> </body> </html> Step 8: Running JBoss Application Server All the coding is over. Next, you need to run the application severs. Before this lets change the default port 8080 to 90. This is because the same port is required by many other applications running on your computer. All right! Go to e:\_java\jboss-4.0.2\server\default\deploy\jbossweb-tomcat55.sar. Open ‘server.xml’ and change ‘Connector port="8080" address="${jboss.bind.address}’ to ‘Connector port="90" address="${jboss.bind.address}’. Now let us run the JBoss Application Server. Just Go to e:\_java\jboss-4.0.2\bin folder and double click the run.bat file and wait till you get the following message. [Server] JBoss (MX MicroKernel) [4.0.2 (build: CVSTag=JBoss_4_0_2 date=200505022023)] Started in 47s:763ms  Step 9: Working with the application Now the application is ready and all set to give the result. Open the browser i.e. internet explorer and type the URL http://localhost:90/myweb in the address bar. That’s all it seems. Rest is to click the links coming on the page.    B e s t O f L u c k … ! ! ! O - oh! Unmm!!! One small thing, I am a .NET guy, don't mind, leading Microsoft and Embedded applied software projects in my current company. And so also I did write this article, as I want to know your technology & its fine beauties. Java is really beautiful and have a lot of charm within itself but I am already in love, with Mcrosoft and Embedded Technologies. If you find anything usefule, don’t forget to add comments, and if you find it useless so also. ...ba-bye!!! »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Javascript, Navigating a list of URLs continuously and sequentially one by one<html> <body> <script language="JavaScript"> w=window.open('about:blank'); function Myfun(ctr) { var cnt = 10; var arr = new Array(cnt); arr[0] = 'http://www.url_01.com'; arr[1] = 'http://www.url_02.com'; arr[2] = 'http://www.url_03.com'; arr[3] = 'http://www.url_04.com'; arr[4] = 'http://www.url_05.com'; arr[5] = 'http://www.url_06.com'; arr[6] = 'http://www.url_07.com'; arr[7] = 'http://www.url_08.com'; arr[8] = 'http://www.url_09.com'; arr[9] = 'http://www.url_10.com'; w.navigate("about:blank"); w.document.write('Just a min Navigating...<br>' + arr[ctr]); w.navigate(arr[ctr]); ctr = ctr + 1; if (ctr == cnt) ctr = 0; setTimeout('Myfun(' + ctr + ')', 7000); } Myfun(0); </script> </body> </html> »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com How to convert an Oracle BLOB column image into Long RAW using Visual BasicPrivate Sub cmdGo_Click() On Error GoTo ErrHnd Dim adCon As New ADODB.Connection Dim adStream As New ADODB.Stream Dim adRstOne As New ADODB.Recordset Dim adRstTwo As New ADODB.Recordset Dim vComp As Double Dim vDown As Double If adRstOne.State = 1 Then adRstOne.Close If adCon.State = 1 Then adCon.Close adCon.Provider = "OraOledb.oracle" adCon.Open tSrv, tUsr, tPwd adCon.CursorLocation = adUseClient adRstOne.Open "select EmpID from EmpDetail", adCon Do While Not adRstOne.EOF If adRstTwo.State = 1 Then adRstTwo.Close adRstTwo.Open "select EmpBLOB, EmpLRAW from EmpGallery" & " where EmpID=" & _ adRstOne!EmpID, adCon, adConadOpenDynamic, adLockOptimistic If Not adRstTwo.EOF Then If Not IsNull(adRstTwo!EmpBLOB) Then adRstTwo!EmpLRAW = adRstTwo!EmpBLOB adRstTwo.Update End If vDown = vDown + 1 End If vComp = vComp + 1 lSts = vDown & " updated and " & vComp & " scanned . . ." lSts.Refresh adRstOne.MoveNext Loop lSts = vDown & " updated and " & vComp & " scanned . . . Done" GoTo ExitSub ErrHnd: lSts = Err.Description ExitSub: If adRstOne.State = 1 Then adRstOne.Close If adRstTwo.State = 1 Then adRstTwo.Close If adCon.State = 1 Then adCon.Close If adStream.State = 1 Then adStream.Close End Sub How to save all Oracle BLOB images to a folder using Visual BasicPrivate Sub cmdGo_Click() On Error GoTo ErrHnd Dim adCon As New ADODB.Connection Dim adStream As New ADODB.Stream Dim adRst As New ADODB.Recordset Dim adRst As New ADODB.Recordset Dim vComp As Double Dim vDown As Double If adRst.State = 1 Then adRst.Close If adCon.State = 1 Then adCon.Close adCon.Provider = "OraOledb.oracle" adCon.Open tSrv, tUsr, tPwd adCon.CursorLocation = adUseClient adStream.Type = adTypeBinary adRst.Open "select empid, empphoto from empgallery", adCon Do While Not adRst.EOF If adStream.State = 1 Then adStream.Close adStream.Open If Not IsNull(adRst!empphoto) Then vDown = vDown + 1 adStream.Write adRst!empphoto adStream.SaveToFile App.Path & "\" & adRst!empid & ".jpg", adSaveCreateOverWrite End If vComp = vComp + 1 lSts = vDown & " downloaded and " & vComp & " scanned . . ." lSts.Refresh adRst.MoveNext Loop lSts = vDown & " downloaded and " & vComp & " scanned . . . Done" GoTo ExitSub ErrHnd: lSts = Err.Description ExitSub: If adRst.State = 1 Then adRst.Close If adRst.State = 1 Then adRst.Close If adCon.State = 1 Then adCon.Close If adStream.State = 1 Then adStream.Close End Sub ASP.NET, How to display an image into an Image Control from an Oracle Long Row FieldPrivate Sub oracle2image(ByVal EmpId As String) 'This procedure requires {Imports System.Data.Odbc} Dim vDr As OdbcDataReader Dim vCon As OdbcConnection Dim vCmd As OdbcCommand Dim PicField As Integer = 0 Dim dPath As String = Server.MapPath("\jpg") & "\empid.jpg" Dim sPath As String = Server.MapPath("\jpg") & "\frame.jpg" Dim vSql As String = "select empphoto from empgallery where empid = '" & EmpId & "'" Try vCon = dbConnect() 'This function returns an ODBC Connection object vCmd = vCon.CreateCommand vCmd.CommandText = vSql vCmd.CommandType = CommandType.Text vDr = vCmd.ExecuteReader If vDr.HasRows Then vDr.Read() Dim vByte(vDr.GetBytes(PicField, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte vDr.GetBytes(PicField, 0, vByte, 0, vByte.Length) Dim fs As New System.IO.FileStream(dPath, IO.FileMode.Create, IO.FileAccess.Write) fs.Write(vByte, 0, vByte.Length) fs.Close() Else IO.File.Copy(sPath, dPath, True) End If imgPhoto.ImageUrl = "jpg/empid.jpg" Catch ex As Exception Throw ex Finally If Not vDr Is Nothing Then vDr.Close() If Not vCmd Is Nothing Then vDr.Dispose() If Not vCon Is Nothing Then vCon.Close() End Try End Sub »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com Oracle, Concatenating two/multiple columns may not come/be uniqueUnique key integrity ensures prevention from duplicacy. But some time it requires composite columns to form it unique. And the general practice is to concatenate two or more columns. But all the time, especially if it a bulk insert statement to a table from one another table, may raise errors. Lets connect to oracle using string scott/tiger@ora9i and do the following exercise. SQL*Plus: Release 9.2.0.1.0 - Production on Fri May 1 10:18:22 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> SET LINES 1000 SQL> SET PAGES 1000 SQL> CREATE TABLE RECEIPT_T( 2 CUST_ID NUMBER, 3 BOOK_NO NUMBER, 4 REC_SNO NUMBER, 5 REC_AMT NUMBER 6 ); TABLE CREATED. SQL> INSERT INTO RECEIPT_T VALUES(1,10,1,1010); 1 ROW CREATED. SQL> INSERT INTO RECEIPT_T VALUES(2,20,1,1020); 1 ROW CREATED. SQL> INSERT INTO RECEIPT_T VALUES(3,10,2,1000); 1 ROW CREATED. SQL> INSERT INTO RECEIPT_T VALUES(4,11,1,1005); 1 ROW CREATED. SQL> INSERT INTO RECEIPT_T VALUES(5,1,11,1020); 1 ROW CREATED. SQL> SELECT * FROM RECEIPT_T ; CUST_ID BOOK_NO REC_SNO REC_AMT ---------- ---------- ---------- ---------- 1 10 1 1010 2 20 1 1020 3 10 2 1000 4 11 1 1005 5 1 11 1020 Here as the data above states no rec_sno is repeated for any book_no. Well, that’s an illusion. If you try to concatenate book_no and rec_sno it will tell you a completely different story. Let’s try the following query. SQL> SELECT CUST_ID, BOOK_NO||REC_SNO UNIQ_NO, REC_AMT FROM RECEIPT_T; CUST_ID UNIQ_NO REC_AMT ---------- ------- ---------- 1 101 1010 2 201 1020 3 102 1000 4 111 1005 5 111 1020 Did you see the record no 4 and 5. Are they unique at uniq_no, No na? All right what is the solution then? They are many. Various techniques can be used. One of them can be padding the value with lpad or rpad. Just see the data below. SQL> SELECT CUST_ID, LPAD(BOOK_NO,4,0)||LPAD(REC_SNO,3,0) UNIQ_NO, REC_AMT FROM RECEIPT_T; CUST_ID UNIQ_NO REC_AMT ---------- ------- ---------- 1 0010001 1010 2 0020001 1020 3 0010002 1000 4 0011001 1005 5 0001011 1020 »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com SQL Server, Using OSQL command line for command line query and exitSometimes working with command line makes many things easy. For example, if you want a text file that will list all employees with latest data, the following command line can be a better method. And the better suggestion is to create a batch file so that every time typing the same thing will not be required. osql.exe ^ -S myHost ^ -U myUser -P myPwd ^ -Q "select * from pubs..employee" ^ -o "d:\PUBS_EMP__%date:~-4,4%%date:~-7,2%%date:~-10,2%_%time::=%.txt" ^ -w 1024 I have used symbol ^ so that I can write a single command into multiple lines. Otherwise it’s a single command and must be given in a single line. The switch –Q is for command line query and then exit. All the all the switch used above are case sensitive and other switches can be seen by issuing osql.exe /?. It will create a file called PUBS_EMP__20090501_ 95401.57.TXT. The switch –o is for output filename and the value d:\PUBS_EMP__%date:~-4,4%%date:~-7,2%%date:~-10,2%_%time::=%.txt will form this filename. So you will get the following thing in that file EMP_ID FNAME MINIT LNAME JOB_ID JOB_LVL PUB_ID HIRE_DATE --------- ------------ ----- ---------- ------ ------- ------ ----------------------- PMA42628M Paolo M Accorti 13 35 0877 1992-08-27 00:00:00.000 PSA89086M Pedro S Afonso 14 89 1389 1990-12-24 00:00:00.000 VPA30890F Victoria P Ashworth 6 140 0877 1990-09-13 00:00:00.000 H-B39728F Helen Bennett 12 35 0877 1989-09-21 00:00:00.000 L-B31947F Lesley Brown 7 120 0877 1991-02-13 00:00:00.000 F-C16315M Francisco Chang 4 227 9952 1990-11-03 00:00:00.000 PTC11962M Philip T Cramer 2 215 9952 1989-11-11 00:00:00.000 A-C71970F Aria Cruz 10 87 1389 1991-10-26 00:00:00.000 AMD15433F Ann M Devon 3 200 9952 1991-07-16 00:00:00.000 ARD36773F Anabela R Domingues 8 100 0877 1993-01-27 00:00:00.000 PHF38899M Peter H Franken 10 75 0877 1992-05-17 00:00:00.000 PXH22250M Paul X Henriot 5 159 0877 1993-08-19 00:00:00.000 CFH28514M Carlos F Hernadez 5 211 9999 1989-04-21 00:00:00.000 PDI47470M Palle D Ibsen 7 195 0736 1993-05-09 00:00:00.000 KJJ92907F Karla J Jablonski 9 170 9999 1994-03-11 00:00:00.000 KFJ64308F Karin F Josephs 14 100 0736 1992-10-17 00:00:00.000 MGK44605M Matti G Karttunen 6 220 0736 1994-05-01 00:00:00.000 POK93028M Pirkko O Koskitalo 10 80 9999 1993-11-29 00:00:00.000 JYL26161F Janine Y Labrune 5 172 9901 1991-05-26 00:00:00.000 M-L67958F Maria Larsson 7 135 1389 1992-03-27 00:00:00.000 Y-L77953M Yoshi Latimer 12 32 1389 1989-06-11 00:00:00.000 LAL21447M Laurence A Lebihan 5 175 0736 1990-06-03 00:00:00.000 ENL44273F Elizabeth N Lincoln 14 35 0877 1990-07-24 00:00:00.000 PCM98509F Patricia C McKenna 11 150 9999 1989-08-01 00:00:00.000 R-M53550M Roland Mendel 11 150 0736 1991-09-05 00:00:00.000 RBM23061F Rita B Muller 5 198 1622 1993-10-09 00:00:00.000 HAN90777M Helvetius A Nagy 7 120 9999 1993-03-19 00:00:00.000 TPO55093M Timothy P O'Rourke 13 100 0736 1988-06-19 00:00:00.000 SKO22412M Sven K Ottlieb 5 150 1389 1991-04-05 00:00:00.000 MAP77183M Miguel A Paolino 11 112 1389 1992-12-07 00:00:00.000 PSP68661F Paula S Parente 8 125 1389 1994-01-19 00:00:00.000 M-P91209M Manuel Pereira 8 101 9999 1989-01-09 00:00:00.000 MJP25939M Maria J Pontes 5 246 1756 1989-03-01 00:00:00.000 M-R38834F Martine Rance 9 75 0877 1992-02-05 00:00:00.000 DWR65030M Diego W Roel 6 192 1389 1991-12-16 00:00:00.000 A-R89858F Annette Roulet 6 152 9999 1990-02-21 00:00:00.000 MMS49649F Mary M Saveley 8 175 0736 1993-06-29 00:00:00.000 CGS88322F Carine G Schmitt 13 64 1389 1992-07-07 00:00:00.000 MAS70474F Margaret A Smith 9 78 1389 1988-09-29 00:00:00.000 HAS54740M Howard A Snyder 12 100 0736 1988-11-19 00:00:00.000 MFS52347M Martin F Sommer 10 165 0736 1990-04-13 00:00:00.000 GHT50241M Gary H Thomas 9 170 0736 1988-08-09 00:00:00.000 DBT39435M Daniel B Tonini 11 75 0877 1990-01-01 00:00:00.000 (43 rows affected) »»»»»»» by Santosh Kumar ? Original @ http://santu4you.spaces.live.com |
|
|
