Database Discussion Boards

Re: how to insert data in foreign key using jsp

Mycroft Holmes13-Mar-14 15:12

13-Mar-14 15:12

By the error I suspect you need to set the ID field in Student to an IDENTITY (assuming sql server).

You seem to be inserting a new subject for every student. I suspect your table design is crap and needs to be redone.

Never underestimate the power of human stupidity
RAH

Re: how to insert data in foreign key using jsp

Faisal_Abdullah14-Mar-14 12:31

Faisal_Abdullah

14-Mar-14 12:31

dear friend i am using mysql and oracle. would you create the tables and send me their code here. Please because i am searching this problem solution from last 3 months. Please i humbly request you help me.

Re: how to insert data in foreign key using jsp

Faisal_Abdullah19-Mar-14 11:14

Faisal_Abdullah

19-Mar-14 11:14

yes you are absolutly right, but how i can do it in oracle and mysql?

Re: how to insert data in foreign key using jsp

Mycroft Holmes19-Mar-14 15:13

Mycroft Holmes

19-Mar-14 15:13

Faisal_Abdullah wrote:
yes you are absolutly right, but how i can do it in oracle and mysql?

Get a book, follow some examples, learn, no one here is going to do your work for you.

Never underestimate the power of human stupidity
RAH

Re: how to insert data in foreign key using jsp

Richard Deeming14-Mar-14 3:20

Richard Deeming

14-Mar-14 3:20

Your code is subject to SQL Injection[^]. You should fix that ASAP.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

to set foreign key or not to set?

Jassim Rahma13-Mar-14 10:46

Jassim Rahma

13-Mar-14 10:46

Hi,

I want to ask about indexing.

I have employee table and department table with employee_id and department_id primary keys. Also I have department_id in the employee table.

Do I set the employee.department_id as a foreign key for a better performance?

Thanks,
Jassim

Technology News @ www.JassimRahma.com

modified 13-Mar-14 15:58pm.

Re: to set foreign key or not to set?

_AK_13-Mar-14 11:16

_AK_

13-Mar-14 11:16

Creating a foreign key is required for refrential integrity and is normally a good practice. It is also true that a foreign key has a say inperformance. To make it better what you can do is to create index on the foreign keys.

.AK.

Re: to set foreign key or not to set?

Jassim Rahma13-Mar-14 11:30

Jassim Rahma

13-Mar-14 11:30

I did indexed it but do I still need (or is it better) to declare it as foreign key?

Technology News @ www.JassimRahma.com

Re: to set foreign key or not to set?

_AK_13-Mar-14 11:49

_AK_

13-Mar-14 11:49

As I mentioned creating a foreign key constraint is required for refrential integrety and normally is a good practice. It do have an impact on performance which can be reduced by creating an index. If you craete the index on foreign key it will reduce the lookups.

.AK.

Re: to set foreign key or not to set?

Jassim Rahma13-Mar-14 11:51

Jassim Rahma

13-Mar-14 11:51

so declaring it as foreign key is a good practice and will have an impact on the performance but after declaring it do you still recommend to index it or declaring is enough?

Technology News @ www.JassimRahma.com

Re: to set foreign key or not to set?

_AK_13-Mar-14 12:07

_AK_

13-Mar-14 12:07

Creating a foreign key is not mandatory but is advised to create. Here[^] is a link which tells about the importance of it.
Now here[^] is another article which tells about refrential integrity and performance degradation which might give you a bit more idea about this.

.AK.

Re: to set foreign key or not to set?

Jassim Rahma13-Mar-14 12:16

Jassim Rahma

13-Mar-14 12:16

Great

Thanks

Technology News @ www.JassimRahma.com

Re: to set foreign key or not to set?

_AK_13-Mar-14 12:21

_AK_

13-Mar-14 12:21

You are welcome.

.AK.

Group by (first letter)

nelsonpaixao12-Mar-14 17:42

nelsonpaixao

12-Mar-14 17:42

Hello,

in some table, how can i perform a group by not by the column string but only by the letter of that column

thanks Big Grin | :-D

nelsonpaixao@yahoo.com.br

trying to help & get help

Re: Group by (first letter)

Kornfeld Eliyahu Peter12-Mar-14 21:03

Kornfeld Eliyahu Peter

12-Mar-14 21:03

Yes.

SQL

GROUP BY SUBSTR([COLUMN], 1, 1)

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Re: Group by (first letter)

nelsonpaixao16-Mar-14 10:50

nelsonpaixao

16-Mar-14 10:50

thanks

nelsonpaixao@yahoo.com.br

trying to help & get help

sum of datediff to many dates

ahmad_yossef11-Mar-14 5:08

ahmad_yossef

11-Mar-14 5:08

i have this periods of experience i want to calculate the sum or total period of these periods

2004-01-06 2004-07-05
2004-07-07 2004-11-30
2010-07-01 2010-07-29
2011-06-12 2011-07-28
2012-07-22 2012-08-20
2013-10-17 2015-05-17

Lost & Forgotten

Re: sum of datediff to many dates

Tim Carmichael11-Mar-14 6:40

Tim Carmichael

11-Mar-14 6:40

If you provide the query you are trying to get working, you are more likely to get help.
What is the underlying database? SQLServer, Oracle, Postgres, etc?

Re: sum of datediff to many dates

ahmad_yossef12-Mar-14 0:05

ahmad_yossef

12-Mar-14 0:05

sql
i made a query calculate the datediff in days and sumed all days now i want to convert this days (integer) to years months and days
thanks

Lost & Forgotten

Re: sum of datediff to many dates

Tim Carmichael12-Mar-14 11:57

Tim Carmichael

12-Mar-14 11:57

ahmad_yossef wrote:
i want to convert this days (integer) to years months and days

Well, break it down.. if you have the number of days, how many days in a year? Let's assume 365; so number of years in int(number of days / 365).
Months... let's assume 30 days on average... Months = int((number of days - years * 365) / 30)
And days are then number of days - years * 365 - months * 30

If that doesn't work, better define what you're after.
Given the sample dates you provided, what are the values you calculated for years, months and days?

Re: sum of datediff to many dates

ahmad_yossef13-Mar-14 0:19

ahmad_yossef

13-Mar-14 0:19

thanks for your replay
but i face a problem when i have a number like 1456

the query will be like that

SELECT dayss, dayss / 365 AS years, (dayss - dayss / 365 * 365) / 30 AS Months, (dayss - dayss / 365 * 365) - (dayss - dayss / 365 * 365) / 30 * 30 AS days
FROM dbo.lv_npay_tmp3

which will return

days1 years months days
1456 3 12 1

and it should be

days1 years months days
1456 4 0 1

i hope you got me

thanks alot

Lost & Forgotten

Re: sum of datediff to many dates

Richard Deeming13-Mar-14 2:10

Richard Deeming

13-Mar-14 2:10

1456 == (3 × 365) + 361.

That's clearly not 4 years and 1 day.

The problem is that you're assuming 365 days per year, but only 30 days per month. (30 × 12) = 360, so you've lost five days.

You need to assume (365 / 12) = 30.4166666... days per month, which will give you an answer of 3 years, 11 months and 27 days.

SQL

SELECT
    dayss, 
    dayss / 365 As years, 
    Cast(Floor((dayss % 365) * 12 / 365.0) As int) As Months, 
    (dayss % 365) - Cast(Floor((dayss % 365) * 12 / 365.0) * 365.0 / 12 As int) As days
FROM
    dbo.lv_npay_tmp3

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: sum of datediff to many dates

Kornfeld Eliyahu Peter12-Mar-14 0:48

Kornfeld Eliyahu Peter

12-Mar-14 0:48

If the answer for your question isn't satisfying, than refine your question - do not post it again...

http://www.codeproject.com/Forums/1725/Database.aspx?select=4775204&tid=4775204[^]

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Re: sum of datediff to many dates

ahmad_yossef12-Mar-14 3:23

ahmad_yossef

12-Mar-14 3:23

i am sorry

Lost & Forgotten

Re: sum of datediff to many dates

Richard MacCutchan12-Mar-14 4:53

Richard MacCutchan

12-Mar-14 4:53

Click on your name and you can find all your previous messages.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.