SVN setup

inst subversion
inst tortiose
create repository
change passwd file in repositiry
change the conf file in reposito to accept passwd file

now set the svn to become a service

sc create svnserve binpath= "\"C:\Program Files\Subversion\bin\svnserve.exe\" --service -r H:\backup\SVNRepo" displayname= "Subversion Server" depend= Tcpip start= auto

How to change color of JTable row having a particular value

http://www.java-forums.org/awt-swing/541-how-change-color-jtable-row-having-particular-value.html

-31-2007, 10:08 PM
johnt
Super Moderator Join Date: Apr 2007
Posts: 30


How to change color of JTable row having a particular value
I am trying to change the color of entire row which is having a column of value "FAIL". Here is my code, which is changing only that particular column. But i want to change the entire row.

Can anyone please give me an idea?

Code:
public Component getTableCellRendererComponent(
JTable table, Object value, boolean isSelected, boolean hasFocus,int row,int col)
{

String s = table.getModel().getValueAt(row,col).toString();

if(s.equalsIgnoreCase("Fail")) {
setForeground(Color.red);
}else {
setForeground(null);
}

return super.getTableCellRendererComponent(table, value, isSelected, hasFocus,
row, col);

}

Sponsored Links


#2 (permalink) 05-31-2007, 10:12 PM
levent
Senior Member Join Date: Dec 2006
Posts: 748


If you know the column in the model that the test value will reside in, set it as a constant and your could would end up looking like this.

Code:
public Component getTableCellRendererComponent(
JTable table, Object value, boolean isSelected,
boolean hasFocus, int row, int col)
{
Component comp = super.getTableCellRendererComponent(
table, value, isSelected, hasFocus, row, col);

String s = table.getModel().getValueAt(row, VALIDATION_COLUMN ).toString();

if(s.equalsIgnoreCase("Fail"))
{
comp.setForeground(Color.red);
}
else
{
comp.setForeground(null);
}

return( comp );
}

C# in VBA

1) [ProgId("FastVBA.WebDownload")]
[ComVisible(true)]
X Register for COM interoper


2) http://codebetter.com/blogs/peter.van.ooijen/archive/2005/08/02/130157.aspx

Sign in | Join | Help

Do you twitter? Follow us @CodeBetter
Peter's Gekko

* Home
* About
* Contact

Sponsors
ASP.NET Web Hosting – Click Here: 3 Months Free!
The Lounge
Telerik

Telerik OpenAccess - extensive LINQ support, forward & reverse mapping, stored procedures and more

Ads by The Lounge
Syndication

* RSS for Posts
* Atom
* RSS for Comments

Recent Posts

* Keeping a long running Silverlight application alive under forms authentication
* Every picture tells a story
* DDD and repositories. Without nHibernate but with lazy loading
* The return of CopySourceAsHtml
* Paging in a sql result set

Tags

* ASP.NET
* Chatter
* Coding
* Data
* Featured
* Hardware
* Mobile
* Out of control
* Tablet PC
* User groups and meetings

View more
News

*


Subscribe with Bloglines
I'm test-driven!

Links

* Gekko Software
* My publications
* Is this code available in VB ?
* This blog is out of control

Archives

* April 2009 (2)
* March 2009 (1)
* February 2009 (3)
* January 2009 (1)
* December 2008 (3)
* November 2008 (2)
* October 2008 (1)
* September 2008 (2)
* August 2008 (1)
* July 2008 (1)
* June 2008 (4)
* May 2008 (4)
* April 2008 (2)
* March 2008 (2)
* February 2008 (2)
* January 2008 (3)
* December 2007 (2)
* November 2007 (3)
* October 2007 (4)
* September 2007 (2)
* August 2007 (1)
* July 2007 (1)
* June 2007 (2)
* May 2007 (1)
* April 2007 (2)
* March 2007 (3)
* February 2007 (6)
* January 2007 (4)
* December 2006 (6)
* November 2006 (7)
* October 2006 (4)
* September 2006 (4)
* August 2006 (6)
* July 2006 (4)
* June 2006 (9)
* May 2006 (6)
* April 2006 (8)
* March 2006 (9)
* February 2006 (10)
* January 2006 (9)
* December 2005 (4)
* November 2005 (7)
* October 2005 (12)
* September 2005 (11)
* August 2005 (10)
* July 2005 (9)
* June 2005 (9)
* May 2005 (11)
* April 2005 (12)
* March 2005 (11)
* February 2005 (27)
* January 2005 (15)
* December 2004 (12)
* November 2004 (12)
* October 2004 (11)
* September 2004 (9)
* August 2004 (9)
* July 2004 (15)
* June 2004 (13)
* May 2004 (13)
* April 2004 (15)
* March 2004 (21)
* February 2004 (23)
* January 2004 (14)
* December 2003 (15)
* November 2003 (21)
* October 2003 (30)
* September 2003 (13)
* August 2003 (12)
* July 2003 (5)
* June 2003 (4)

Advertisement
Peter's Gekko » Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
Images in this post missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at imagehelp@codebetter.com
Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword

I am a C# guy. This is a matter of personal preference, I know how to code with VB.NET but just prefer curly brackets. So far the differences were not really worth the (sometimes quite) flaming discussion, after all we're all programming against the same framework. But recently I felt forced into using VB.NET for a part of a project. Let me explain what happened.

At first sight creating COM servers with .NET is a snap. When you set Register for COM interop to true in the project options all public types and their public members are published via COM and can be used in VBscript or from VBA code in an Office application. Use the ComVisible attribute to hide a public member from COM.
Imports System.Runtime.InteropServices
Public Class MyfirstComClass
Public Sub DoSomethingForYourCOMclient()
' Your code here
End Sub

Public Sub DoMore()
' More code
End Sub

_
Public Sub DotNetOnly()
' This code cannot be called from a COM client
End Sub
End Class


A COM class, its interface and any events it might raise are identified by a couple of GUID's. The moment you need a little more control over your class you apply these from code and identify an object as one raising events. In VB.NET this is all done in one attribute.
Imports System.IO

_
Public Class FileWatcher
Public Const ClassId = "44558DD7-87AD-433f-9B1B-C478233D6C69"
Public Const InterfaceId = "959A6835-4768-4cd5-89BE-7D408C2B86AA"
Public Const EventsId = "02E3954F-5DC1-4ad9-9167-354F0E82BCA3"

Private WithEvents watcher As FileSystemWatcher
Private Sub watcher_Created(ByVal sender As Object, ByVal e As FileSystemEventArgs) Handles watcher.Created
RaiseEvent OnNewFile(e.FullPath)
End Sub

Public Sub Watch(ByVal dirName As String, ByVal filter As String)
watcher = New FileSystemWatcher(dirName, filter)
watcher.EnableRaisingEvents = True
End Sub

Public Event OnNewFile(ByVal fullFileName As String)

End Class


This example FileWatcher class contains the guids to identify it. The ComClassAttribute applies them. The class wraps up a .NET FileSytemWatcher. The Watch method instantiates the object, sets a directory to watch, and enables raising events. When a new file matching the filter is created the COMserver's OnNewFile event will fire. You can use the server in Word like this:
Dim WithEvents mywatcher As WordUtilsVB.FileWatcher

Private Sub Document_Open()
Set mywatcher = New WordUtilsVB.FileWatcher
mywatcher.Watch "C:\USR", "*.doc"
End Sub

Private Sub mywatcher_OnNewFile(ByVal fullFileName As String)
Documents.Open (fullFileName)
End Sub


Opening the documents fires up the COM server which will start watching for new Word Documents in my C:|USR directory. When a new file is found Word will open it. (Note that the *.doc filter will also open Word temp file). This is a handy utility and took just a couple of VB.NET lines.

Being a C# guy I would like to refactor this to C# because I want to be able to make multiple call to the watch method which should result in multiple directories being watched. The way VB.NET handles event handlers is somewhat clumsy. In C# I could code like this.
public class FileWatcher
{
internal const string ClassId = "44558DD7-87AD-433f-9B1B-C478233D6C69";
internal const string InterfaceId = "959A6835-4768-4cd5-89BE-7D408C2B86AA";
internal const string EventsId = "02E3954F-5DC1-4ad9-9167-354F0E82BCA3";

private ArrayList watchers = new ArrayList();

public void Watch(string dirName, string filter)
{
FileSystemWatcher watcher = new FileSystemWatcher(dirName, filter);
watcher.Created += new FileSystemEventHandler(watcher_Created);
watchers.Add(watcher);
}

public NewFile OnNewFile;


private void watcher_Created(object sender, FileSystemEventArgs e)
{
OnNewFile(e.FullPath);
}
}

[ComVisible(false)]
public delegate void NewFile(string fileName);


To define the event I have to declare the NewFile delegate. This should not be exported to COM so the ComVisible attribute is applied. On every call to Watch a new FileSystemWatcher object is created and in C# I can attach an eventhandler on the fly, no need to declare a method which explicitly handles a specific event of a specific object. (Perhaps my VB knowledge falls short here, but I don't know how to do this elegantly in VB. The handles way does not work here) The ArrayList stores all watchers.

The hard part is registering this class in COM. The COMclassAttribute is part of the MicroSoft.VisualBasic namespace so it is by default not available in a C# project. The easy way would be to reference the Microsoft.VisualBasic.dll and use it nevertheless. Which works to get to VB specific functions like the financial ones (summary).
[Microsoft.VisualBasic.ComClassAttribute(FileWatcher.ClassId, FileWatcher.InterfaceId, FileWatcher.EventsId)]
public class FileWatcher
{
internal const string ClassId = "44558DD7-87AD-433f-9B1B-C478233D6C69";


This code will build and run. But will not do what you want it to do. By default all public members are published in COM, the moment you start applying attributes results vary. Applying this VB attribute will result in a COM class without any members. To satisfy the COM registration process in C# requires these steps

* Declare a public interface which describes the COMinterface of the class
* Declare the class as implementing this interface
* Declare a public interface which describes the events the class can sink (COM jargon for raising events)
* Decorate this interface with an InterfaceType attribute an IDispatch interface
* Decorate the class with a ComSourceInterface attribute
* Decorate the class with ClassInterface attribute
* Decorate the COMinterface, the eventsink interface and the class with Guid attributes

Resulting in :
namespace WordUtils
{
[Guid(FileWatcher.InterfaceId)]
public interface IfileWatcher
{
void Watch(string dirName, string filter);
}

[Guid(FileWatcher.EventsId)]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface IfileWatcherEvents
{
void OnNewFile(string fullFileName);
}

[Guid(FileWatcher.ClassId)]
[ClassInterface(ClassInterfaceType.None)]
[ComSourceInterfaces(typeof(IfileWatcherEvents))]

public class FileWatcher : IfileWatcher
{
internal const string ClassId = "44558DD7-87AD-433f-9B1B-C478233D6C69";
internal const string InterfaceId = "959A6835-4768-4cd5-89BE-7D408C2B86AA";
internal const string EventsId = "02E3954F-5DC1-4ad9-9167-354F0E82BCA3";

private ArrayList watchers = new ArrayList();

public void Watch(string dirName, string filter)
{
FileSystemWatcher watcher = new FileSystemWatcher(dirName, filter);
watcher.Created += new FileSystemEventHandler(watcher_Created);
watchers.Add(watcher);
}

public NewFile OnNewFile;


private void watcher_Created(object sender, FileSystemEventArgs e)
{
OnNewFile(e.FullPath);
}
}

[ComVisible(false)]
public delegate void NewFile(string fileName);
}


This is a lot more code than the VB.NET version. On the other hand in this code you do have a better overview of what this class looks to COM. Just read the two interfaces
[Guid(FileWatcher.InterfaceId)]
public interface IfileWatcher
{
void Watch(string dirName, string filter);
}

[Guid(FileWatcher.EventsId)]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
public interface IfileWatcherEvents
{
void OnNewFile(string fullFileName);
}


But there is a nasty problem with this code. When it comes to events it just does not work. The VBA designer in Word will "build" the code but as soon you run it it pops up a very nasty error:

This is described in the MS knowledge base here, but that does not really help as it makes clear there is no workaround. What is happening exactly is hidden inside the framework, the C# code seems to delegate the sinking of events to an object which VBA cannot work with. You can build C# COM servers which sink events properly. In the beta days of 1.0 I have been ploughing my way into .NET via COM. At the time unaware of the great (intended) COM event support I created a base class with working event support, implementing the desired interfaces (IConnectionPointContainer and its allies) all by hand. It's quite a long story, you can find it here and it does have sample code.

I'm "afraid" this is another notch for VB.NET, the second one when it comes to COM. I have to admit VB works very nice with named parameters and this is another one. But I'm going to be happy with the nice things of both languages. I'll use C# to solve the internal event handling stuff and I'll use VB.NET to make a COM wrapper. After all both languages live happy together in the .NET framework. To paraphrase Chuck Yeager: "it's the framework, not the language".

It takes just one keyword to get the C# COM server working as intended. Change

public NewFile OnNewFile;

to

public event NewFile OnNewFile;

and everything works. Read the background here.


Posted 08-02-2005 12:11 PM by pvanooijen
Filed under: Coding

[Advertisement]
Red-Gate
Comments
Sean wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 08-02-2005 10:44 AM
In VB.NET, you should be able to add your event handlers via code as in:

Dim watcher as FileSystemWatcher = New FileSystemWatcher(dirName, filter)
AddHandler watcher.Created AddressOf watcher_Created
watchers.Add(watcher)
pvanooijen wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 08-02-2005 12:01 PM
Thanks, that is the syntax I was looking for.

The most interesting thing is that after refactoring the VB class to the same pattern as the C# class results the VB class will show the same error.
Now the 459 error begins to make more sense. Delegating the handling of the event to another object, in the case the watcher object, will produce the problem. In both languages.

So instead of looking for the VB addhandler syntax I'll have to look for the C# equivalent of handles.

To be continued..

Peter's Gekko wrote Handles, AddHandler and RemoveHandler in VB.NET
on 08-03-2005 4:37 PM
Äfter a crash course comes sinking in. This post is a rewrite of yesterdays one on events in VB.NET....
Peter's Gekko wrote Handles, AddHandler and RemoveHandler in VB.NET
on 08-04-2005 4:23 AM
After a crash course comes sinking in. This post is a rewrite of yesterdays one
on events in VB.NET....
Peter's Gekko wrote Handles, AddHandler and RemoveHandler in VB.NET
on 08-05-2005 12:07 PM
After a crash course comes sinking in. This post is a rewrite of yesterdays one
on events in VB.NET....
Peter's Gekko wrote Handles versus Addhandler , a crash course in VB.NET event support (Error 459 revisited)
on 08-05-2005 12:10 PM
In my previous post
on the handling of COM events I described how I was driven into the
arms of VB.NET....
Visitor wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 08-10-2005 8:28 AM
To fix the nasty problem on using the C# COM server you have to declare the event with:

public event NewFile OnNewFile;
pvanooijen wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 08-10-2005 9:51 AM
That's great. Now Word does create the object without any errors. But as far as I can see the events don't hook in :? Look into that after my holiday.

A bit of a pitty you would need such a, at first sight superfluous keyword, to get it working. Not really in the spirit of C#.
Visitor wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 08-11-2005 2:19 AM
You can see the events, if you enable the watcher to raise some events with:

watcher.EnableRaisingEvents = true;

So it should do what you want.
savage wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 08-11-2005 10:01 AM
I'm trying to get this very thing working between Delphi and a C# application. I'm creating a COM class in C# and attempting to attach an event from within Delphi, but not having any luck. Any suggestions?
pvanooijen wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 08-30-2005 5:00 AM
The Events keyword indeed does the trick. I had indeed forgotten to set the filewatcher's enableraisingevents property. Setting it gave me my intended utility.

I'll write a wrapup post on the events keyword, it's documentation is bad and the idea is (looking back) quite simple.

I'll also write a post on eventsupport in Delphi, for the time you can find loads off material on this on my website www.gekko-software.nl
Peter's Gekko wrote The C# event keyword is an access modifier for delegate members
on 08-31-2005 4:08 PM
Recently I had trouble getting COM events to work in a COM automation server written in C#. A visitor's...
Peter's Gekko wrote The C# event keyword is an access modifier for delegate members
on 08-31-2005 4:11 PM
Recently I had trouble getting COM events to work in a COM automation server written in C#. A visitor's...
Peter's Gekko wrote The C# event keyword is an access modifier for delegate members
on 09-01-2005 11:54 AM
Recently I had trouble getting COM events to work in a COM automation server written in C#. A visitor's...
Peter's Gekko wrote Is this code available in VB ?
on 10-06-2005 5:32 AM
This is a question I often get. I'm a C# guy but that is just a matter of personal preference. It's the...
Khash wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 12-16-2005 12:28 PM
I think I have found a bug in CCW
Have a look at this:
http://sajadi.co.uk/dflat/archives/2005/12/net_com_callabl.html
Rogelio wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 09-21-2006 10:16 AM

I created a COM class using the first sample you provided:

Imports System.Runtime.InteropServices

Public Class MyfirstComClass

Public Sub DoSomethingForYourCOMclient()

' Your code here

End Sub

Public Sub DoMore()

' More code

End Sub

_

Public Sub DotNetOnly()

' This code cannot be called from a COM client

End Sub

End Class

The problem I'm having is calling the DoMore() public sub from javascript. Is there a way to do this?
pvanooijen wrote re: Creating a COM server with .NET. C# versus VB.NET and the WithEvents keyword
on 09-21-2006 1:20 PM

To make the call from JavaScript you have to create the COM object in JavaScript. Creating a COM (ActiveX) object in the browser is not a path to follow except when you don't see any other solution.
Add a Comment
Name: (required) *
Website: (optional)
Comments (required) *
Remember Me?

Verify that you are a human,
drag scissors into the circle.

*
*
*
*
*

Ajax Fancy Captcha

About CodeBetter.Com

CodeBetter.Com FAQ
Our Mission
Advertisers should contact Brendan


Subscribe

Google Reader or Homepage
del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo


Member Projects

Sarasota Web Design - David Hayden
Patterns & Practices - David Hayden
dotMath - Steve Hebert
Structure Map - Jeremy D. Miller
StoryTeller - Jeremy D. Miller
The Code Wiki - Karl Seguin


Friends of CodeBetter.Com

Red-Gate Tools For SQL and .NET
Telerik
ComponentArt
VistaDB
JetBrains - ReSharper
Beyond Compare
.NET Memory Profiler
NDepend
AliCommerce
Ruby In Steel
SlickEdit
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
AForge.NET
Unfuddle
Balsamiq Mockups
Scrumy <-- NEW Friend!

3)How to create Excel UDFs in VSTO managed code

One question that I frequently get is how to call managed code from VBA. In general it is not recommended to mix VBA with managed code mainly due to the non-deterministic eventing model. In other words if VBA and managed code are listening for the same event there is no guarantee of the order that the handlers will be called. Another issue with using VBA and VSTO in the same solution is that you now have to deal with two separate security models. With that said, there are still times when you want to call VSTO code from VBA. One scenario is that you are upgrading an existing VBA solution to use VSTO. In this scenario you are keeping all of the existing VBA and are adding new capabilities to your solution using VSTO. Another scenario is that you want to create a solution in VSTO but you want to use User Defined Functions (UDF) in Excel. UDFs still require that they be written in VBA, but you can create your UDFs in managed code and call them from VBA. This is the technique that I describe below. This solution requires that you pass a reference to your managed code to VBA. Once the you have a reference to the managed code you can call that code from VBA. I recommend creating a wrapper in VBA for the managed functions this allows you to “call” the managed code from VBA.

Here is any easy way to call Managed functions from VBA.

*
1. Create a class with your functions in VSTO

_
Public Class MyManagedFunctions
Public Function GetNumber() As Integer
Return 42
End Function
End Class

2. Wire up your class to VBA in VSTO

Private Sub ThisWorkbook_Open() Handles Me.Open
Me.Application.Run("RegisterCallback", New MyManagedFunctions)
End Sub

3. Create Hook for managed code and a wrapper for the functions in VBA

In a VBA module in your spreadsheet or document
Dim managedObject As Object

Public Sub RegisterCallback(callback As Object)
Set managedObject = callback
End Sub

Public Function GetNumberFromVSTO() As Integer
GetNumberFromVSTO = managedObject.GetNumber()
End Function

Now you can enter =GetNumberFromVSTO() in a cell, when excel starts the cell value should be 42.


Published Friday, December 31, 2004 8:01 PM by pstubbs

IE C# 2

http://www.codeproject.com/KB/vb/kirangoka.aspx

Introduction

Microsoft Internet Explorer comes with a fairly comprehensive, although sparsely documented, Object Model. If you've used the Web Browser control in Access, you are already familiar with the capabilities of IE's Object Model. All of the functionality in IE's object model (not counting external support, like scripting support etc.) is provided by the following two dlls:

* shdocvw.dll (Microsoft Internet Controls)
* mshtml.tlb (Microsoft HTML Object Library)

You can automate IE to save a HTML file locally , inspect all the elements, and parse out a particular item at runtime.

Here's some sample code that automate through Internet Explorer windows login into the rediffmail.com, if the user name and password are valid.

First the application opens the http://rediff.com site. It types the user name and password at specified location and click the submit button so that it goes to inbox page. It also opens the compose page for the particular user.

The application extensively uses shdocvw.InternetExplorer object and mshtml.Document object.
Collapse

Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load

Collapse

Dim wbBrowser As New SHDocVw.InternetExplorer wbBrowser.Visible = True
wbBrowser.Navigate("http://www.rediff.com", Nothing, Nothing, Nothing, Nothing) Do
Loop Until Not wbBrowser.Busy
LoginIntoSite(wbBrowser)
OpennComposePage(wbBrowser)

Collapse

End Sub

Public Sub LoginIntoSite(ByRef wbBrowser As SHDocVw.InternetExplorer)

Dim HTMLDoc As mshtml.HTMLDocument

Do
Loop Until Not wbBrowser.Busy

HTMLDoc = wbBrowser.Document

Dim iHTMLCol As IHTMLElementCollection
Dim iHTMLEle As IHTMLElement
Dim str, userName, passwd As String


iHTMLCol = HTMLDoc.getElementsByTagName("input")



' Type the user name in the username text box
For Each iHTMLEle In iHTMLCol
If Not iHTMLEle.getAttribute("name") Is Nothing Then
str = iHTMLEle.getAttribute("name").ToString
If str = "login" Then
iHTMLEle.setAttribute("value", "")
Exit For
End If
End If
Next

' Type the password in the password text box
For Each iHTMLEle In iHTMLCol
If Not iHTMLEle.getAttribute("name") Is Nothing Then
str = iHTMLEle.getAttribute("name").ToString
If str = "passwd" Then
iHTMLEle.setAttribute("value", "")
Exit For
End If
End If
Next

' Press the submit button
For Each iHTMLEle In iHTMLCol
If Not iHTMLEle.getAttribute("name") Is Nothing Then
If iHTMLEle.outerHTML = " " height=21 width=26 " & _
"src=""http://im.rediff.com/" & _
"uim/rm_go_but.gif"" border=0>" Then
iHTMLEle.click()
Exit For
End If
End If
Next

Do
Loop Until Not wbBrowser.Busy





End Sub

Public Sub OpenComposePage(ByRef wbBrowser As SHDocVw.InternetExplorer)

Dim HTMLDoc1 As mshtml.HTMLDocument
Dim iHtmlCol As IHTMLElementCollection
Dim iHtmlEle As IHTMLElement

Do
Loop Until Not wbBrowser.Busy

HTMLDoc1 = mshtml.HTMLDocument



iHtmlCol = HTMLDoc1.getElementsByTagName("a")

' Press the anchor tag to open compose page
For Each iHtmlEle In iHtmlCol
If Not iHtmlEle.outerText Is Nothing Then
If iHtmlEle.outerText.ToLower = "write mail".ToLower Then
iHtmlEle.click()
Exit For
End If
End If
Next

Do
Loop Until Not wbBrowser.Busy
End Sub

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

IE C#

http://www.omegacoder.com/?p=63

In this article I describe how to get the current URL in Internet Explorer and the current directory in Windows Explorer. This works in .Net 2 and IE 7. I am showing both at the same time because they are similar in methods of getting the address of the items, whether its a hard drive or a URL.

First step requires the access ShellWindows object which represents a collection of open windows in the system. To access that object which resides in the SHDocVw namespace we need to import a Com library Microsoft Internet Controls in to the project:

image

With that we can access the ShellWindows object and begin our work. Here is the code sample

using System.IO;



...



SHDocVw.ShellWindows shellWindows

= new SHDocVw.ShellWindowsClass();



string filename;



foreach (SHDocVw.InternetExplorer ie in shellWindows)

{

filename

= Path.GetFileNameWithoutExtension(ie.FullName).ToLower();



if (filename.Equals("iexplore"))

Console.WriteLine("Web Site : {0}", ie.LocationURL);



if (filename.Equals("explorer"))

Console.WriteLine("Hard Drive : {0}", ie.LocationURL);



}

Thanks to the magic of ShellWindows we are able to see all active windows. We discriminate and get the names of the windows we are interested in and wala here is the output:

Hard Drive : file:///C:/Work/Net2
Web Site : http://www.omegacoder.com/

Config

intInitWait=10
task.input=stockdb.work.task.CHTMLInputTask
task.input.retries=8
#batch.task.symbollist.sql=select distinct {0} from request_symbol, dual order by 1
batch.task.symbollist.sql=select /*+ first_row */ symbol, update_date from (SELECT symbol, update_date, ROWNUM rn FROM (select * from request_symbol order by update_date )) where rn <=168
#batch.task.symbollist.sql=select /*+ first_row */ symbol, update_date from (SELECT symbol, update_date, ROWNUM rn FROM (select * from request_symbol order by update_date )) where rn <=1000
#batch.task.symbollist.sql=select /*+ first_row */ symbol, update_date from (SELECT symbol, update_date, ROWNUM rn FROM (select * from request_symbol order by update_date )) where rn <=276
#select /*+ first_row */ symbol from (select symbol, update_date, ROWNUM rn from request_symbol order by 2) A where A.rn <=500
#batch.task.symbollist.sql=select distinct symbol from request_symbol where symbol > 290 And symbol < 300
#PollTargetStockID=2341,1195,205,2333,2366,258,572,587,593,682,904,274,1203

workerthread=1
dburl=jdbc:oracle:thin:@superpub:1521:orcl
dbuser=stockdb
dbpassword=stockdb781003
htmlpassword=0B6000830007382893282938286328263824B6A4
preparemode=false
flushlevel=100
restmillisecmin=10000
restmillisecrange=11000
logFile=mylogNew.txt

logFilePoll=mylogPoll.txt
PollInputURL=http://www.hkexnews.hk/listedco/listconews/mainindex/SEHK_LISTEDCO_DATETIME_TODAY_C.HTM
PollOutputClass=stockdb.work.task.CPollExchangeNewsTask
PollTargetStockID=1161,2341,2333,593,904,599,184,8285,533,1203,408,1044,2368,1068,999,2628,941,1688,1211,64,857,1088,3808,1893,1800,1766,1053,1186,8277,2319,3968,522,3988,709,999,2368,753,494,2318
DailyQueryStockID=1161,2341,2333,904,408,1044,1068,999,2628,941,1688,1211
PollBuySellID=10165300&corpn=Natural+Beauty+Bio-Technology+Ltd.,10161600&corpn=Water+Oasis+Group+Ltd.,50000108&corpn=EcoGreen+Fine+Chemicals+Group+Ltd.,50000098&corpn=China+Green+(Holdings)+Ltd.,50000092&corpn=Great+Wall+Motor+Co.+Ltd.+-+H+Shares,50000156&corpn=China+Shineway+Pharmaceutical+Group+Ltd.,10069600&corpn=E.+Bon+Holdings+Ltd.,26220100&corpn=Goldlion+Holdings+Ltd.,25310100&corpn=Yip's+Chemical+Holdings+Ltd.,28600101&corpn=Guangnan+(Holdings)+Ltd.,50000324&corpn=Ajisen+(China)+Holdings+Ltd.,10020900&corpn=Hengan+International+Group+Co.+Ltd.,50000209&corpn=China+Yurun+Food+Group+Ltd.,50000173&corpn=I.T+Ltd.,50000117&corpn=Lifestyle+International+Holdings+Ltd.,50000094&corpn=China+Life+Insurance+Co.+Ltd.+-+H+Shares,30780107&corpn=China+Mobile+Ltd.,50000381&corpn=Alibaba.com+Ltd.,10187400&corpn=BYD+Co.+Ltd.+-+H+Shares,50000355&corpn=Vinda+International+Holdings+Ltd.,50000283&corpn=China+Merchants+Bank+Co.%2c+Ltd.+-+H+Shares,50000186&corpn=China+Shenhua+Energy+Co.+Ltd.+-+H+Shares,10069300&corpn=PetroChina+Co.+Ltd.+-+H+Shares,50000085&corpn=Wumart+Stores%2c+Inc.+-+H+Shares,10187400&corpn=BYD+Co.+Ltd.+-+H+Shares,50000270&corpn=Bank+of+China+Ltd.+-+H+Shares,25180107&corpn=Giordano+International+Ltd.,50000173&corpn=I.T+Ltd.,50000064&corpn=Eagle+Nice+(International)+Holdings+Ltd.,50000162&corpn=Air+China+Ltd.+-+H+Shares,25950103&corpn=Li+%26+Fung+Ltd.,50000130&corpn=Ping+An+Insurance+(Group)+Co.+of+China+Ltd.+-+H+Shares
PollBuySellSID=00157,01161,02341,00904,02333,02877,00599,00533,00408,01203,00538,01044,01068,00999,01212,02628,00941,01688,01211,03331,03968,01088,00857,08277,01211,03988,00709,00999,02368,00753,00494,02318
#,01175,08201
#,50000163&corpn=FU+JI+Food+and+Catering+Services+Holdings+Ltd.,80016500&corpn=China+Fire+Safety+Enterprise+Group+Holdings+Ltd.
#,29880108&corpn=Perennial+International+Ltd.,30050105&corpn=Man+Yue+International+Holdings+Ltd.
#,23010108&corpn=Tomson+Group+Ltd.,50000019&corpn=Hua+Han+Bio-Pharmaceutical+Holdings+Ltd.,10102200&corpn=Chaoda+Modern+Agriculture+(Holdings)+Ltd.
#,729,894,00184,00258,00587,00682
BSPollOutputClass=stockdb.work.task.CBSPollExchangeNewsTask
PollIntervalSec=120
PollExplorer=C:\\Program Files\\Internet Explorer\\iexplore.exe
PollShowMessageApp=C:\\Documents and Settings\\Arthur Lai\\My Documents\\Visual Studio 2005\\Projects\\ShowMessageApplication\\ShowMessageApplication\\bin\\x86\\Release\\ShowMessageApplication.exe
EmailSMTPServer=mail.netvigator.com
EmailRCPTTo=s977491@netvigator.com,ilove7412369@hotmail.com,jackchlai@hotmail.com,alai@lsplab.com
EmailMAILFrom=s977491@netvigator.com
batch.task.input=stockdb.work.task.CHTMLExeInputTask

SQL SERVER - 2005 - List All The Constraint of Database - Find Primary Key and Foreign Key Constraint in Database

http://blog.sqlauthority.com/2007/09/16/sql-server-2005-list-all-the-constraint-of-database-find-primary-key-and-foreign-key-constraint-in-database/

USE AdventureWorks;
GO
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
GO

Reference : Pinal Dave (http://www.SQLAuthority.com)
Ads by Google
免費 SQL Server 2008 軟件
Get Free Software by Attending SQL Server 2008 Official Training
www.welkin.com.hk/promotions
Mssql Monitoring
Monitor SQL Server Performance Track DB usage. Download Now!
manageengine.adventnet.com
MS SQL Repair SW
Repair Corrupt MDF Database Files Instant Repair, Free Demo Preview
www.mssqldatabaserecovery.com
Sql Server
Ready to learn SQL Server? Try our free videos before you buy!
cbtnuggets.com


Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, T SQL, Technology | 25 Comments
25 Responses to “SQL SERVER - 2005 - List All The Constraint of Database - Find Primary Key and Foreign Key Constraint in Database”

1.
on September 16, 2007 at 11:07 pm abhay

Dear Pinal,

To get the idea of any relationship between tables using foreign key relationships, I think, following query will be more useful :

select Referencing_Object_name, referencing_column_Name, Referenced_Object_name, Referenced_Column_Name from
(select Referenced_Column_Name = c.name, Referenced_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.rkeyid = o.id) and c.id = o.id and c.colid = f.rkey) r,
(select referencing_column_Name = c.name, Referencing_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.fkeyid = o.id) and c.id = o.id and c.colid = f.fkey) f
where r.Referenced_Column_Name = f.referencing_column_Name
and r.constid = f.constid
order by f.Referencing_Object_name

Regards,
Abhay

2.
on September 30, 2007 at 10:38 pm Ujjaval Suthar

Hey Pinal,

I was going through google to look for the query that you’ve posted here and came across your blog.

Its surprising to see you and remember your face from Nirma Bus, if my memory is not wrong. I was studying in Nirma between 1999 - 2003. And you are also from Gandhingar, right? yeah I am from Gandhinagar.

Regards,
Ujjaval

PS:- Oh by the way, useful post. Thanks for that.

3.
on October 2, 2007 at 6:03 pm pinaldave

Hi Ujjaval Suthar,

Yes, I am Pinal from Gandhinagar. Nirma 1999-2003.

Regards,
Pinal

4.
on October 23, 2007 at 7:43 am Rob

This query works and was very helpful to me. Thanks.

5.
on December 21, 2007 at 1:11 am Shishir Khandekar

For SQL Server 2005, you can use the sys.foreign_keys view to achieve the same. The columns in this view are self explanatory so am not publishing the query here.

Regards
Shishir

6.
on December 21, 2007 at 5:47 pm Sandeep

i’m new to sql server 2005 …
can u plz suggest ny gud book for sqlserver 2005 for a beginner and i want to do certification for d same..

plz guide!!!!!

7.
on December 21, 2007 at 6:11 pm Sandeep

sir,
i got my answer, initially i didn’t search in sqlauthority.com!!

thx

8.
on December 28, 2007 at 2:25 pm rajesh

Hello Sir,
I am a .net Developer and I came across some difficult questions during the interview on database. The question
is :
There is a table with 5 coloumns, in that under country coloumn the fields should be all asain countries. If the user
enters other than asian country it should throw an error.
How to write a query for this?

9.
on January 18, 2008 at 7:21 pm Tom W

1 column should be for continent.
Put ‘Asia’ in as the value for all the Asian coutries, etc.

10.
on February 22, 2008 at 3:20 pm szolarp

Hi!
I made an easier to understand script that shows all constraint in the database including that rows where the referencing_column_name and the referenced_column_name are different and the name of the constraint
select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id

11.
on March 27, 2008 at 10:44 pm Tushar Mehere

Hi szolarp,

Your query is prefect. It had some defect, i refined it below.

select
o1.name as Referencing_Object_name
, c1.name as referencing_column_Name
, o2.name as Referenced_Object_name
, c2.name as Referenced_Column_Name
, s.name as Constraint_name
from sysforeignkeys fk
inner join sysobjects o1 on fk.fkeyid = o1.id
inner join sysobjects o2 on fk.rkeyid = o2.id
inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey
inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey
inner join sysobjects s on fk.constid = s.id
and o2.name=’tblUserDetails’ — this predicate for a specific table

12.
on March 29, 2008 at 7:42 am Aviv

Hi
Does anyone know how can I find the relationship between colomn and default constraint. I need it for dynamicly drop the column in different databases

13.
on April 16, 2008 at 4:34 pm Rakesh Dewangan

Hi Pinal,
really this procedure its very very useful,
I was facing trouble from last half an hour, and your query has resolved my problem, with in second.

thanks a lot.

great work!!!!

14.
on May 5, 2008 at 2:57 pm Henko

Thanks for the script above, it really helped me on deleting my DWH constraints. Cheers.

15.
on May 12, 2008 at 5:07 pm Gerry

This blog helped me a lot! Many thanks to you all.

I was looking for days to solve my constraint problem.
this is very usefull in my ADO application. Looks like ADO does not have functions to read detailed constraint information like this script from an SQL database. Therefore I was forced to search for a solution in SQL scripting and here it was!

Thx!

16.
on May 24, 2008 at 6:16 pm Niraimathi

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint “hrpyprc_ps_auth_r01_fk”. The conflict occurred in database “HRMS40_CBI”, table “dbo.hrpyprc_payset_auth_cnt”.
The statement has been terminated.

17.
on June 23, 2008 at 2:41 pm Tim4it

szolarp & Tushar

Many thanks .. you really helped me ..

18.
on August 4, 2008 at 5:00 pm kalyan

i’m new to sql server 2005 …
can u plz suggest ny gud book for sqlserver 2005 for a beginner and i want to do certification for d same..

plz guide!!!!!

19.
on August 7, 2008 at 4:47 pm Ankush

What r the differences b/w Sql Server 2005 and 2000

20.
on August 26, 2008 at 10:04 am Muhammad Usman Arshad

Hi,

I think using information schema is much useful than using sysobjects. To see the list of constraints you can use the query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

or you can use database name with it:

SELECT * FROM [SugarCMS].INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Here is the complete list of information_schema views:

CHECK_CONSTRAINTS: Check Constraints
COLUMN_DOMAIN_USAGE: Every column that has a user-defined data type.
COLUMN_PRIVILEGES: Every column with a privilege granted to or by the current user in the current database.
COLUMNS:Lists every column in the system
CONSTRAINT_COLUMN_USAGE: Every column that has a constraint defined on it.
CONSTRAINT_TABLE_USAGE: Every table that has a constraint defined on it.
DOMAIN_CONSTRAINTS: Every user-defined data type with a rule bound to it.
DOMAINS: Every user-defined data type.
KEY_COLUMN_USAGE: Every column that is constrained as a key
PARAMETERS: Every parameter for every user-defined function or stored procedure in the datbase. For functions this returns one row with return value information.
REFERENTIAL_CONSTRAINTS: Every foreign constraint in the system.
ROUTINE_COLUMNS: Every column returned by table-valued functions.
ROUTINES: Every stored procedure and function in the database.
SCHEMATA: Every database in the system.
TABLE_CONSTRAINTS: Every table constraint.
TABLE_PRIVILEGES: Every table privilege granted to or by the current user.
TABLES: Every table in the system.
VIEW_COLUMN_USAGE: Every column used in a view definition.
VIEW_TABLE_USAGE: Every table used in a view definition.
VIEWS: Every View

21.
on August 29, 2008 at 10:27 pm Syed

Hi,
Does any one know how to get DEFAULT Constraint along with the name of the column on which it has defined.
Nirmal query gives the table name, but not the column name, because most of the time DEFAULT is created on the column by using DEFAULT, which create the system generated name of the DEFAULT, so I would like to know how to get the column name along with the table name.

I would appreciate any suggestion.

Thanks

22.
on August 30, 2008 at 3:44 am Imran Mohammed

@SYED,

This might help,

SELECT OBJECT_NAME(PARENT_OBJECT_ID) TABLE_NAME,
COL_NAME (PARENT_OBJECT_ID, PARENT_COLUMN_ID)COLUMN_NAME ,
NAME DEFAULT_CONSTRAINT_NAME
FROM SYS.DEFAULT_CONSTRAINTS ORDER BY 1

Hope this helps,
Imran.

23.
on September 12, 2008 at 10:51 pm Arthur

I think is easier… it’s pretty much like the one publish by szolarp and “fixed” by Tushar Mehere…

is this:
SELECT RO.NAME AS ParentTable, RC.NAME AS ParentColumn, FO.NAME AS ForeignTable, FC.NAME AS ForeignColumn
FROM sysforeignkeys F INNER JOIN sysobjects RO
ON F.rkeyid = RO.id INNER JOIN syscolumns RC
ON RC.id = RO.id AND RC.colid = F.rkey INNER JOIN sysobjects FO
ON F.fkeyid = FO.id INNER JOIN syscolumns FC
ON FC.id = FO.id AND FC.colid = F.fkey
ORDER BY RO.NAME, RC.NAME, FO.NAME, FC.NAME

we don’t really to know the name…

24.
on September 27, 2008 at 4:17 am Mark

Hi Pinal,
I have a situation wherein I need to change a particular name in my entire database.
Please tell me how can i trace this particular word - it might be occuring in ‘n’ number of tables in the database, in ‘n’ number of column values and in ‘n’ number of records throughout present in any table of that database.
I need to write a cursor, go to each record and trace that word, likewise for others, for each table. I heard that sysobjects and syscolumns could help in this scenario, could you please generate the code if possible.

Thanks, Mark

Java中调用SQL Server存储过程示例

2007-09-03 08:48作者：孟子E章出处：论坛整理责任编辑：方舟

　 　最近做了个Java的小项目（第一次写Java的项目哦）， 到网上搜索了半天，找到了一个比较好点的调用存储过程的例子，而且网上普遍采用的都是setXXX((int parameterIndex, XXX x)的形式。这种形式感觉不是很直观，下面就发布一个完整的采用setXXX(String parameterName, XXX x)的编写方法。创建数据表，存储过程的代码都完整发布。

　　创建表：

CREATE TABLE [BookUser] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [Guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BookUser_Guid] DEFAULT (newid()), [BirthDate] [datetime] NOT NULL , [Description] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL , [Photo] [image] NULL , [Other] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_BookUser_Other] DEFAULT ('默认值'), CONSTRAINT [PK_BookUser] PRIMARY KEY CLUSTERED ( [UserID] ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

　　创建存储过程：

CREATE PROCEDURE InsertUser @UserName varchar(50), @Title varchar(255), @Guid uniqueidentifier, @BirthDate DateTime, @Description ntext, @Photo image, @Other nvarchar(50), @UserID int output As Set NOCOUNT ON If Exists (select UserID from BookUser Where UserName = @UserName) RETURN 0 ELSE Begin INSERT INTO BookUser (UserName,Title,Guid,BirthDate,Description,Photo,Other) VALUES(@UserName,@Title,@Guid,@BirthDate,@Description,@Photo,@Other) SET @UserID = @@IDENTITY RETURN 1 End GO

　　JSP代码：

＜%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%＞
＜%@ page import = "java.sql.*"%＞
＜!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"＞
＜html xmlns="http://www.w3.org/1999/xhtml"＞
＜head＞
＜/head＞
＜body＞
＜%
//注意：下面的连接方法采用最新的SQL Server的JDBC，
//请到 http://msdn2.microsoft.com/zh-cn/data/aa937724.aspx 下载
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;databaseName=Book;user=sa;password=";
String sql = "{? = call InsertUser(?,?,?,?,?,?,?,?)}";
Connection cn = null;
CallableStatement cmd = null;
try
{
　cn = DriverManager.getConnection(url);
　cmd = cn.prepareCall(sql);
　java.util.UUID Guid = java.util.UUID.randomUUID();
　String FilePath = application.getRealPath("") + "\test\logo.gif";
　java.io.FileInputStream f = new java.io.FileInputStream(FilePath);
　Date rightNow = Date.valueOf("2007-9-9");
　cmd.setString("UserName","mengxianhui"); //注意修改这里，存储过程验证了UserName的唯一性。
　cmd.setString("Title","孟宪会");
　cmd.setString("Guid",Guid.toString());
　cmd.setString("BirthDate","2007-9-9");
　cmd.setDate("BirthDate",rightNow);
　cmd.setString("Description","【孟子E章】");
　cmd.setBinaryStream("Photo",f,f.available());
　cmd.setString("Other",null);
　cmd.registerOutParameter(1,java.sql.Types.INTEGER);
　cmd.registerOutParameter("UserID",java.sql.Types.INTEGER);
　cmd.execute();
　int returnValue = cmd.getInt(1);
　int UserID = cmd.getInt("UserID");
　if(returnValue == 1)
　{
　　out.print("＜li＞添加成功！");
　　out.print("＜li＞UserID = " + UserID);
　　out.print("＜li＞returnValue = " + returnValue);
　}
　else
　{
　　out.print("＜li＞添加失败！");
　}
　f.close();
}
catch(Exception ex)
{
　out.print(ex.getLocalizedMessage());
}
finally
{
　try
　{
　　if(cmd != null)　
　　{
　　　cmd.close();
　　　cmd = null;
　　}
　　if(cn != null)
　　{
　　　cn.close();
　　　cn = null;
　　}
　}
　catch(Exception e)
　{
　　e.printStackTrace();
　}
}
%＞
＜/body＞
＜/html＞

Running a batch file in Java.

import java.io.*;
import java.lang.Runtime;

/**
*
* @author yamin Si
*/
public class testCallExe {
public testCallExe(){
Runtime r=Runtime.getRuntime();
Process p=null;
try
{
p = r.exec(new String[]{"cmd","/c","start C:/temp/test.bat"});
BufferedReader input = new BufferedReader(new InputStreamReader(p.getInputStream()));

String line=null;

while((line=input.readLine()) != null) {
System.out.println(line);
}
System.out.println("Exit Value = " p.waitFor());//Method waitFor() will make the current thread to wait until the external program finish and return the exit value to the waited thread.

}

catch(Exception e){
System.out.println("error===" e.getMessage());
e.printStackTrace();
}
}
public static void main(String args[]) {
testCallExe test=new testCallExe();
}
}

MiniConnectionPoolManager - A lightweight standalone JDBC connection pool manager

http://www.source-code.biz/snippets/java/8.htm

The standard Java library (JDK 1.5) does not provide a connection pool manager for JDBC database connections. There are open source connection pool managers like Apache Commons DBCP or c3p0, but these are huge complex packages. Modern JDBC drivers provide implementations of ConnectionPoolDataSource and PooledConnection. This makes it possible to build a much smaller connection pool manager.

MiniConnectionPoolManager is a lightweight JDBC connection pool manager. It may be used in Java servlets as well as in Java standalone applications. It only requires Java 1.5 (or newer) and has no dependencies on other packages.

API documentation:	MiniConnectionPoolManager.html
Source code:	MiniConnectionPoolManager.java
Test program:	TestMiniConnectionPoolManager.java
Download full package:	MiniConnectionPoolManager.zip
Related work 1:	org.opensolaris.auth.db.DbDataSource (by Alan Burlison), a DataSource wrapper class for MiniConnectionPoolManager, which can be used in JSP SQL tags.
Related work 2:	org.h2.jdbcx.JdbcConnectionPool (source code), a version of MiniConnectionPoolManager ported to Java 1.4 and adapted to H2 by Thomas Müller.

Examples of how to use the MiniConnectionPoolManager class

For H2 (embedded mode):

org.h2.jdbcx.JdbcDataSource dataSource = new org.h2.jdbcx.JdbcDataSource();
dataSource.setURL ("jdbc:h2:file:c:/temp/testDB");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Apache Derby (embedded mode):

org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource dataSource = new org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource();
dataSource.setDatabaseName ("c:/temp/testDB");
dataSource.setCreateDatabase ("create");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For JTDS:

net.sourceforge.jtds.jdbcx.JtdsDataSource dataSource = new net.sourceforge.jtds.jdbcx.JtdsDataSource();
dataSource.setDatabaseName ("Northwind");
dataSource.setServerName ("localhost");
dataSource.setUser ("sa");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For the Microsoft SQL Server driver:

com.microsoft.sqlserver.jdbc.SQLServerXADataSource dataSource = new com.microsoft.sqlserver.jdbc.SQLServerXADataSource();
// The sqljdbc 1.1 documentation, chapter "Using Connection Pooling", recommends to use SQLServerXADataSource instead of SQLServerConnectionPoolDataSource.
dataSource.setDatabaseName ("Northwind");
dataSource.setServerName ("localhost");
dataSource.setUser ("sa");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Oracle (example for Thin driver):

oracle.jdbc.pool.OracleConnectionPoolDataSource dataSource = new oracle.jdbc.pool.OracleConnectionPoolDataSource();
dataSource.setDriverType ("thin");
dataSource.setServerName ("server1.yourdomain.com");
dataSource.setPortNumber (1521);
dataSource.setServiceName ("db1.yourdomain.com");
dataSource.setUser ("system");
dataSource.setPassword ("sesame");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource,maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

Design pattern for working with JDBC connections

It is important to use error handling to ensure that Connection and Statement class objects are always closed, even when an exception occurs.

Example:

public static String getFirstName (int personKey) throws Exception {
  Connection connection = null;
  PreparedStatement statement = null;
  try {
     connection = poolMgr.getConnection();
     final String sql = "select firstName from person where personKey = ?";
     statement = connection.prepareStatement(sql);
     statement.setInt (1, personKey);
     ResultSet rs = statement.executeQuery();
     if (!rs.next()) throw new Exception ("Person not found);
     return rs.getString(1); }
   finally {
     if (statement != null) statement.close();
     if (connection != null) connection.close(); }}

Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)
Index

Java Using a Stored Procedure with Output Parameters

Using a Stored Procedure with Output Parameters

A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application. The Microsoft SQL Server 2005 JDBC Driver provides the SQLServerCallableStatement class, which you can use to call this kind of stored procedure and process the data that it returns.

When you call this kind of stored procedure by using the JDBC driver, you must use the call SQL escape sequence together with the prepareCall method of the SQLServerConnection class. The syntax for the call escape sequence with OUT parameters is the following:

{call procedure-name[([parameter][,[parameter]]...)]}

Note:
For more information about the SQL escape sequences, see Using SQL Escape Sequences.

When you construct the call escape sequence, specify the OUT parameters by using the ? (question mark) character. This character acts as a placeholder for the parameter values that will be returned from the stored procedure. To specify a value for an OUT parameter, you must specify the data type of each parameter by using the registerOutParameter method of the SQLServerCallableStatement class before you run the stored procedure.

The value that you specify for the OUT parameter in the registerOutParameter method must be one of the JDBC data types contained in java.sql.Types, which in turn maps to one of the native SQL Server data types. For more information about the JDBC and SQL Server data types, see Understanding the JDBC Driver Data Types.

When you pass a value to the registerOutParameter method for an OUT parameter, you must specify not only the data type to be used for the parameter, but also the parameter's ordinal placement or the parameter's name in the stored procedure. For example, if your stored procedure contains a single OUT parameter, its ordinal value will be 1; if the stored procedure contains two parameters, the first ordinal value will be 1, and the second ordinal value will be 2.

Note:
The JDBC driver does not support the use of CURSOR, SQLVARIANT, TABLE, and TIMESTAMP SQL Server data types as OUT parameters.

As an example, create the following stored procedure in the SQL Server 2005 AdventureWorks sample database:

Copy Code

CREATE PROCEDURE GetImmediateManager
  @employeeID INT,
  @managerID INT OUTPUT
AS
BEGIN
  SELECT @managerID = ManagerID
  FROM HumanResources.Employee
  WHERE EmployeeID = @employeeID
END

This stored procedure returns a single OUT parameter (managerID), which is an integer, based on the specified IN parameter (employeeID), which is also an integer. The value that is returned in the OUT parameter is the ManagerID based on the EmployeeID that is contained in the HumanResources.Employee table.

In the following example, an open connection to the AdventureWorks sample database is passed in to the function, and the execute method is used to call the GetImmediateManager stored procedure:

Copy Code

public static void executeStoredProcedure(Connection con) {
  try {
     CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
     cstmt.setInt(1, 5);
     cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
     cstmt.execute();
     System.out.println("MANAGER ID: " + cstmt.getInt(2));
  }
  catch (Exception e) {
     e.printStackTrace();
  }
}

This example uses the ordinal positions to identify the parameters. Alternatively, you can identify a parameter by using its name instead of its ordinal position. The following code example modifies the previous example to demonstrate how to use named parameters in a Java application. Note that parameter names correspond to the parameter names in the stored procedure's definition:

Copy Code

public static void executeStoredProcedure(Connection con) {
  try {
     CallableStatement cstmt = con.prepareCall("{call dbo.GetImmediateManager(?, ?)}");
     cstmt.setInt("employeeID", 5);
     cstmt.registerOutParameter("managerID", java.sql.Types.INTEGER);
     cstmt.execute();
     System.out.println("MANAGER ID: " + cstmt.getInt("managerID"));
     cstmt.close();
  }
  catch (Exception e) {
     e.printStackTrace();
  }

}

Note:
These examples use the execute method of the SQLServerCallableStatement class to run the stored procedure. This is used because the stored procedure did not also return a result set. If it did, the executeQuery method would be used.

Stored procedures can return update counts and multiple result sets. The Microsoft SQL Server 2005 JDBC Driver follows the JDBC 3.0 specification, which states that multiple result sets and update counts should be retrieved before the OUT parameters are retrieved. That is, the application should retrieve all of the ResultSet objects and update counts before retrieving the OUT parameters by using the CallableStatement.getter methods. Otherwise, the ResultSet objects and update counts that have not already been retrieved will be lost when the OUT parameters are retrieved. For more information about update counts and multiple result sets, see Using a Stored Procedure with an Update Count and Using Multiple Result Sets.

See Also

SQL 92 dialect question: Insert or update a record if it already exists?

ben

View Public Profile

Send a private message to ben

Find all posts by ben

ANSWER(S):

#2 (permalink) 04-17-2006, 07:09 AM

Dimitar Posts: n/a

SQL 92 answer. Re: Insert or update a record if it already exists? I am afraid there is no sql92 answer to this question. For sq192 an approach would be to insert every row and use grouping functions for reporting.

Dimitar

#3 (permalink) 04-17-2006, 07:54 AM

ben Administrator Join Date: Mar 2007 Posts: 93

MySQL answer. Re: Insert or update a record if it already exists? MySQL has a special construct for this. Assume the 'username' column below is UNIQUE: INSERT INTO users (username, email) VALUES ('Jo', 'jo@email.com') ON DUPLICATE KEY UPDATE email = 'jo@email.com' The 'ON DUPLICATE KEY' statement only works on PRIMARY KEY and UNIQUE columns.

ben

View Public Profile

Send a private message to ben

Find all posts by ben

#4 (permalink) 05-08-2006, 12:20 AM

Rob137 Posts: n/a

T-SQL answer. Re: Insert or update a record if it already exists? How about this: IF (EXISTS (SELECT * FROM AA_TestTable AS t1 WHERE t1.ord_num = 'FFF')) begin UPDATE AA_TestTable SET ord_qty = 999 WHERE ord_num = 'FFF' end else begin INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty) VALUES('GGG', 'XYZ', 567) end

Rob137

#5 (permalink) 09-18-2006, 07:35 AM

srinivas Posts: n/a

Oracle answer. Re: Insert or update a record if it already exists? merge INTO users U1 USING (U1.username = 'Jo' ) when matched then UPDATE SET U1.email = 'jo@email.com' when NOT matched then INSERT(U1.email, U1.username) VALUES('Jo', jo@email.com);

srinivas

#6 (permalink) 12-12-2006, 12:44 AM

Evil Overlord Posts: n/a

ANY answer. Re: Insert or update a record if it already exists? INSERT INTO users (username) SELECT 'Jo' WHERE 'Jo' NOT IN (SELECT username FROM users) UPDATE users SET email = 'jo@email.com' WHERE username = 'Jo' I use this method a lot. Obviously, 'Jo' would usually either be a variable or a field selected from another table.

Evil Overlord

#7 (permalink) 05-07-2007, 05:19 PM

Unregistered Posts: n/a

ANY answer. Re: Insert or update a record if it already exists? Quote: Originally Posted by Rob137 How about this: IF (EXISTS (SELECT * FROM AA_TestTable AS t1 WHERE t1.ord_num = 'FFF')) begin UPDATE AA_TestTable SET ord_qty = 999 WHERE ord_num = 'FFF' end else begin INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty) VALUES('GGG', 'XYZ', 567) end This opption worked wonders! Thanks for posting

Unregistered

#8 (permalink) 05-30-2007, 07:57 AM

Unregistered Posts: n/a

ANY answer. Re: Insert or update a record if it already exists? Quote: Originally Posted by Rob137 How about this: IF (EXISTS (SELECT * FROM AA_TestTable AS t1 WHERE t1.ord_num = 'FFF')) begin UPDATE AA_TestTable SET ord_qty = 999 WHERE ord_num = 'FFF' end else begin INSERT INTO AA_TestTable (ord_num, top_assy, ord_qty) VALUES('GGG', 'XYZ', 567) end (I assume there is a typo in the last statement, where ord_num should be set to 'FFF'). You should be VERY CAREFUL with things like this. If you can't afford to set the transaction isolation level to SERIALIZABLE, some other transaction could add the row with ord_num='FFF' after you've tested for its existence, but before you've inserted it. This way you end up with violated primary constraint and error in one of these transactions.

Unregistered

#9 (permalink) 10-03-2007, 07:22 PM

mplusch Posts: n/a

ANY answer. Re: Insert or update a record if it already exists? This is a variation that works for tables with multiple primary keys. If you have a users table with columns of username, dept, and age, and primary keys of username and dept, then this will only insert a user if it doesn't exist already. INSERT INTO users (username, dept, age) SELECT username='mp', dept='tax', age=5 WHERE (SELECT COUNT (*) FROM users WHERE username='mp' AND dept='tax')=0; I initially found this syntax confusing, but here's how I 'parsed' it. This part returns 0 if it doesn't exist: SELECT COUNT (*) FROM users WHERE username='mp' AND dept='tax' This part creates a record-like row with static values and represents the record to insert: SELECT username='mp', dept='tax', age=5 The WHERE-clause following the above part will only return the value to insert if the count is 0.