A Small Help Needed For a Beginner

I just want install ODBC driver for doing JDBC.How I must configure while installing SQl 2005 Express?.Which authentication I have to provide ?Whether Windows or SQL authentication?I want to connect jdbc odbc driver to my own system itself.

Thank You in advance.

There is no need to use an ODBC-JDBC bridge with SQL Server. You can download the latest JDBC driver here http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

You can learn about the driver here http://msdn.microsoft.com/data/learning/jdbc/

Reference documentation is here http://msdn.microsoft.com/data/ref/jdbc/

|||

import java.sql.*;
public class AuthorsInfo
{
public static void main(String args[])
{
try
{
String str="SELECT * FROM sudheer";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
/*Establish a connection with a data source*/
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost;user=MyUserName;password=*****");
/*Create a Statement object to process the SELECT statement*/
Statement stmt=con.createStatement();
/*Execute the SELECT SQL statement*/
ResultSet rs=stmt.executeQuery(str);

while (rs.next())
{
System.out.println(rs.getString(1)+rs.getString(2)+rs.getString(3));

}
con.close();
}
catch(Exception ex)
{
System.out.println("Error occurred");
System.out.println("Error:"+ex);
}
}
}

I wrote the code and it is giving no class found error.Where to set the class path CLASSPATH =.;C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.1\enu\sqljdbc.jar?

How can I keep userid and password?

Thank You

A probably over and over again asked question of Syntax - Help please

Dear Group

I'd be grateful if you can provide me with a hint for the following:

Fields Table Contact
ContactID
Firstname
Lastname

Fields Table ContactMethod
ContactMethodID
ContactMethodTxt
ContactMethodTypeID

Linked by Tables:

Fields Table LkTbl_Contact_ContactMethod
LkTblID
ContactID
ContactMethodTypeID

Fields Table ContactMethodType
ContactMethodTypeID
ContactMethod

The purpose of this construct is that I keep all contactmethods such
as Email, Mobile, Home Work, Web in table ContactMethods. Whether it's
e.g. an Email or Mobile is identified through the ContactMethodType.
That enables me that a Contact can have 3 Mobile Numbers, 2 Email
addresses.

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

I must note that the contents of table ContactMethodType for column
Contactmethod looks like this:
Email
Mobile
Home
Work
Web

I do:

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

The question is, how do I map the result from table ContactmethodType
to my fields Home, Work and Mobile in the resultset? The Type is
identified in ContactMethodType but I can't figure out how to do this
best.

Your help and suggestions or any other better solution is very
appreciated.

Thanks in advance for your help and efforts,

MartinIs Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts? If not,
then I agree with Erland that ContactMethod should be merged with
LkTbl_Contact_ContactMethod for simplicity (unless you have a pile of
code already built on top of the existing design, in which case you
should at least create a view that combines their data).

theintrepidfox wrote:

Quote:

Originally Posted by

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

"First" in what sense? Lowest ContactMethodID value among the
candidate rows?

Quote:

Originally Posted by

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

create view v_FirstContactMethodIDs as
selectlcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
fromLkTbl_Contact_ContactMethod lcm
joinContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go

selectc.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
fromContact c
left joinv_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left joinContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left joinv_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left joinContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left joinv_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left joinContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID|||On 15 Oct, 05:52, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

Is Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts? If not,
then I agree with Erland that ContactMethod should be merged with
LkTbl_Contact_ContactMethod for simplicity (unless you have a pile of
code already built on top of the existing design, in which case you
should at least create a view that combines their data).
>
theintrepidfox wrote:
>

Quote:

Originally Posted by

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

>
"First" in what sense? Lowest ContactMethodID value among the
candidate rows?
>

Quote:

Originally Posted by

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

>
create view v_FirstContactMethodIDs as
select lcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
from LkTbl_Contact_ContactMethod lcm
join ContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go
>
select c.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
from Contact c
left join v_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left join ContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left join v_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left join ContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left join v_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left join ContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID

Hi Ed

Thanks for your message.

'Is Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts?'

Yes, Contact A and Contact B both might share the same BusinessPhone
(ContactMethodType) in which case there's a single row in table
ContactMethod that refers to both.

I got the script almost working based on Erlands sample. The only
issue is that it returns me value Phone of row 1 ('777 123') in table
Contactmethod instead row 6 ('435 675') which would be the correct
one. Here's what I got, it's probably totally wrong. Please excuse my
SQL ignorance.

SELECT i2b_jajah.JajahID, UseJajah, JajahUsername,
MIN(ISNULL(NULLIF(Firstname,'') + CHAR(32),'') +
ISNULL(NULLIF(Middlename,'') + CHAR(32),'') + ISNULL(Lastname,'')) AS
RealUsername,
Phone = Min(CASE i2b_systbl_contactmethodtype.ContactMethodTypeID
WHEN 1 THEN i2b_contactmethod.ContactMethodText END)
FROM i2b_jajah
LEFT JOIN i2b_lktbl_contact_jajah ON (i2b_lktbl_contact_jajah.JajahID
= i2b_jajah.JajahID)
JOIN i2b_contact ON (i2b_contact.ContactID =
i2b_lktbl_contact_jajah.ContactID)
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)
JOIN i2b_contactmethod ON (i2b_contactmethod.ContactMethodID =
i2b_lktbl_contact_contactmethod.ContactMethodID)
JOIN i2b_systbl_contactmethodtype ON
(i2b_systbl_contactmethodtype.ContactMethodTypeID =
i2b_contactmethod.ContactMethodTypeID)
GROUP BY i2b_jajah.JajahID, UseJajah, JajahUsername

Result:
JajahID UseJajah JajahUsername
RealUsername Phone
---- ----
-------
------- ---
1 0 JSmith007
John Smith 777 123

Thank you for your help and efforts,

Martin|||On 15 Oct, 07:29, theintrepidfox <theintrepid...@.hotmail.comwrote:

Quote:

Originally Posted by

On 15 Oct, 05:52, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

Is Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts? If not,
then I agree with Erland that ContactMethod should be merged with
LkTbl_Contact_ContactMethod for simplicity (unless you have a pile of
code already built on top of the existing design, in which case you
should at least create a view that combines their data).

>

Quote:

Originally Posted by

theintrepidfox wrote:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

For the sake of simplicity, lets say I want to return the Contact Name
and Home, Work and Mobile numbers. Not all of them but the first
matching record of each from the ContactMethod table.

>

Quote:

Originally Posted by

"First" in what sense? Lowest ContactMethodID value among the
candidate rows?

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Select Firstname, Lastname, Home, Work, Mobile FROM Contact
JOIN LkTbl_Contact_ContactMethod ON
(LkTbl_Contact_ContactMethod.ContactID = Contact.ContactID)
JOIN ContactMethod ON (ContactMethod.ContactMethodID =
LkTbl_Contact_ContactMethodID)
JOIN ContactMethodType ON (ContactMethodType.ContactMethodTypeID =
ContactMethod.ContactMethodTypeID)

>

Quote:

Originally Posted by

create view v_FirstContactMethodIDs as
select lcm.ContactID,
cm.ContactMethodTypeID,
min(cm.ContactMethodID) FirstContactMethodID
from LkTbl_Contact_ContactMethod lcm
join ContactMethod cm
on lcm.ContactMethodID = cm.ContactMethodID
group by lcm.ContactID, cm.ContactMethodTypeID
go

>

Quote:

Originally Posted by

select c.FirstName,
c.LastName,
cm_home.FirstContactMethodTxt Home,
cm_work.FirstContactMethodTxt Work,
cm_mobile.FirstContactMethodTxt Mobile
from Contact c
left join v_FirstContactMethodIDs v_fci_home
on c.ContactID = v_fci_home.ContactID
and v_fci_home.ContactMethodTypeID = 'Home'
left join ContactMethod cm_home
on v_fci_home.ContactMethodID = cm_home.ContactMethodID
left join v_FirstContactMethodIDs v_fci_work
on c.ContactID = v_fci_work.ContactID
and v_fci_work.ContactMethodTypeID = 'Work'
left join ContactMethod cm_work
on v_fci_work.ContactMethodID = cm_work.ContactMethodID
left join v_FirstContactMethodIDs v_fci_mobile
on c.ContactID = v_fci_mobile.ContactID
and v_fci_mobile.ContactMethodTypeID = 'Mobile'
left join ContactMethod cm_mobile
on v_fci_mobile.ContactMethodID = cm_mobile.ContactMethodID

>
Hi Ed
>
Thanks for your message.
>
'Is Contact <-ContactMethod a many-to-many relationship, i.e. can a
single ContactMethod be associated with multiple Contacts?'
>
Yes, Contact A and Contact B both might share the same BusinessPhone
(ContactMethodType) in which case there's a single row in table
ContactMethod that refers to both.
>
I got the script almost working based on Erlands sample. The only
issue is that it returns me value Phone of row 1 ('777 123') in table
Contactmethod instead row 6 ('435 675') which would be the correct
one. Here's what I got, it's probably totally wrong. Please excuse my
SQL ignorance.
>
SELECT i2b_jajah.JajahID, UseJajah, JajahUsername,
MIN(ISNULL(NULLIF(Firstname,'') + CHAR(32),'') +
ISNULL(NULLIF(Middlename,'') + CHAR(32),'') + ISNULL(Lastname,'')) AS
RealUsername,
Phone = Min(CASE i2b_systbl_contactmethodtype.ContactMethodTypeID
WHEN 1 THEN i2b_contactmethod.ContactMethodText END)
FROM i2b_jajah
LEFT JOIN i2b_lktbl_contact_jajah ON (i2b_lktbl_contact_jajah.JajahID
= i2b_jajah.JajahID)
JOIN i2b_contact ON (i2b_contact.ContactID =
i2b_lktbl_contact_jajah.ContactID)
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)
JOIN i2b_contactmethod ON (i2b_contactmethod.ContactMethodID =
i2b_lktbl_contact_contactmethod.ContactMethodID)
JOIN i2b_systbl_contactmethodtype ON
(i2b_systbl_contactmethodtype.ContactMethodTypeID =
i2b_contactmethod.ContactMethodTypeID)
GROUP BY i2b_jajah.JajahID, UseJajah, JajahUsername
>
Result:
JajahID UseJajah JajahUsername
RealUsername Phone
---- ----
-------
------- ---
1 0 JSmith007
John Smith 777 123
>
Thank you for your help and efforts,
>
Martin- Hide quoted text -
>
- Show quoted text -

Got It working:

LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactID = i2b_contact.ContactID)
Instead of
LEFT JOIN i2b_lktbl_contact_contactmethod ON
(i2b_lktbl_contact_contactmethod.ContactMethodID =
i2b_contact.ContactID)

Still, if you have any suggestions for improvement, please let me
know.

Thanks again for your great help and time.

All the best,

Martin

A Microsoft Native XML Database Someday?

Hi,
I was wondering if you might provide some idea of whether Microsoft is
considering building a native Xml database as Xml has become an important
part of so many of its products. I know about SQL 2005 and its method for
Xml handling, but couldn't that product family be expanded to include
something that is Xml only? Perhaps during the data-enhancements planned for
C#.
Thanks,
James
PS - Please give Anders a hug from us C# developers, he's a godsend.Hi James.
I doubt that we will ever create an XML only database system. But if you
give us more encouraging feedback, we will work on making SQL Server even
more usable as an XML database system :-).
Best regards
Michael
PS: We have forwarded the hugs to Anders :-).
"James White" <james@.rationalpath.com> wrote in message
news:u%23$PMrwrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if you might provide some idea of whether Microsoft is
> considering building a native Xml database as Xml has become an important
> part of so many of its products. I know about SQL 2005 and its method for
> Xml handling, but couldn't that product family be expanded to include
> something that is Xml only? Perhaps during the data-enhancements planned
> for
> C#.
> Thanks,
> James
> PS - Please give Anders a hug from us C# developers, he's a godsend.
>
>

A Microsoft Native XML Database Someday?

Hi,
I was wondering if you might provide some idea of whether Microsoft is
considering building a native Xml database as Xml has become an important
part of so many of its products. I know about SQL 2005 and its method for
Xml handling, but couldn't that product family be expanded to include
something that is Xml only? Perhaps during the data-enhancements planned for
C#.
Thanks,
James
PS - Please give Anders a hug from us C# developers, he's a godsend.
Hi James.
I doubt that we will ever create an XML only database system. But if you
give us more encouraging feedback, we will work on making SQL Server even
more usable as an XML database system :-).
Best regards
Michael
PS: We have forwarded the hugs to Anders :-).
"James White" <james@.rationalpath.com> wrote in message
news:u%23$PMrwrFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I was wondering if you might provide some idea of whether Microsoft is
> considering building a native Xml database as Xml has become an important
> part of so many of its products. I know about SQL 2005 and its method for
> Xml handling, but couldn't that product family be expanded to include
> something that is Xml only? Perhaps during the data-enhancements planned
> for
> C#.
> Thanks,
> James
> PS - Please give Anders a hug from us C# developers, he's a godsend.
>
>