Re: SQL Query - join or exists? - Database Discussion Boards

Paul Conrad28-Nov-06 13:40

28-Nov-06 13:40

Colin Angus Mackay wrote:
Damn IT! This "solution" compromises the security of the application by making it vulnerable to a SQL Injection Attack.

I agree, and as usual, people need to look at your article Big Grin | :-D

Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon

PostgreSQL, ODBC and transaction strange behavior

Le Sourcier27-Nov-06 3:55

Le Sourcier

27-Nov-06 3:55

I've a serious problem Cry | :((

in my software while inserting data in PostgreSQL tables.

I'm using VC6.0 sp6, the last PostgresSQL release and the last associated psqlODBC driver.

I can not find anything in msdn help, so I post my problem and I except that someone could help me. Roll eyes | :rolleyes:

That is the way I insert my data:

1. I create a database connection to my database, using psqlODBC driver
2. I open a recordset (with appendOnly and bulkrowadd options)
3. Start a transaction with CDatabase::BeginTrans()
3. I insert lots new records to my table (hundreds of records)
4. Commit transaction with CDatabase::CommitTrans();
5. Close database connection

But, when an insert fails (a CDBExcpetion is thrown in Update() function), then all previously non commited records are lost !!! Unsure | :~

I just want only bad insert to be lost but not all my records updated since BeginTrans()

Here is my code :

CDatabase * pDatabase = new CDatabase();<br />
pDatabase->OpenEx(); //ask user to select a DSN<br />
<br />
CRecordsetST myRecordset(pDatabase);<br />
myRecordset.Open(AFX_DB_USE_DEFAULT_TYPE, NULL, CRecordset::optimizeBulkAdd | CRecordset::appendOnly);<br />
if(!myRecordset.IsOpen())<br />
{<br />
	pDatabase->Close();<br />
	return;<br />
}<br />
<br />
pDatabase->BeginTrans();<br />
<br />
while( //file is not EOF )<br />
{<br />
	try<br />
	{<br />
		myRecordset.AddNew();<br />
		//fill recordset fields with file data<br />
		//.....<br />
		//.....<br />
		//.....<br />
		//.....<br />
		myRecordset.Update();<br />
	}<br />
	catch(CDBException *e)<br />
	{<br />
		//if a CDBException is catched, then<br />
		//cancelupdate to pass current record<br />
		//and continue to next data without<br />
		//doing rollback<br />
<br />
		//cancel update to prepare next record<br />
		myRecordset.CancelUpdate();<br />
		//trace some message<br />
		OutputDebugString(e->m_strError+_T("\n"));<br />
		e->Delete();<br />
<br />
		//here I do not want to rollback my transaction, but just cancel current update<br />
		//and continue my process with keeping all previously added records (good ones)<br />
	}<br />
	catch(...)<br />
    	{<br />
		//if an unknown exception if catched then rollback all<br />
		myRecordset.CancelUpdate();<br />
		//trace some message<br />
		OutputDebugString(_T("FATAL error while inserting data\n"));<br />
		//perform rollback<br />
		pDatabase->Rollback();<br />
		pDatabase->Close();<br />
		return;<br />
    	}<br />
	//read next line in file<br />
}<br />
<br />
pDatabase->CommitTrans();<br />
pDatabase->Close();<br />
...

I've tried to commit current transaction in first catch, but records are not saved.
I've searched for some options in CDatabase or CRecordset, but I didn't find anything.

I excepect that you could help me.
thanks for all.

Le Sourcier

SQL Query - join or exists?

Dewald27-Nov-06 3:26

Dewald

27-Nov-06 3:26

I'm hoping someone can help me with this query that I've been struggling with. I'll use an example to explain my problem.

Let's say I have a table named Orders with the following three fields:
CustID, OrderDescr and OrderDate

Now I want to select from this table the first order (oldest date) for each customer. In other words I want a resulting table that lists one entry for every unique CustID in Orders that shows the oldest order for that CustID.

I was hoping the following SQL query would do the trick but it doesn't.

SELECT * FROM Orders O1 WHERE exists<br />
(SELECT TOP 1 * from Orders O2 WHERE O1.CustID = O2.CustID AND O1.OrderDate = O2.OrderDate Order by OrderDate)

Alternatively I was thinking that doing an INNER JOIN with the table on itself there might be a way but I have as yet not been able to figure it out.

Re: SQL Query - join or exists?

ed^nrgc27-Nov-06 3:33

ed^nrgc

27-Nov-06 3:33

If you're looking for the oldest date, remove the check for O1.OrderDate = O2.OrderDate and use a having min clause.

Re: SQL Query - join or exists?

Dewald27-Nov-06 4:05

Dewald

27-Nov-06 4:05

OK, sorry for being the klutz here but could you be a little bit more specific please?

I am familiar with the MIN() function as in SELECT MIN(OrderDate) FROM Orders but I'm not sure how to use it with the HAVING clause.

Re: SQL Query - join or exists?

ed^nrgc27-Nov-06 4:08

ed^nrgc

27-Nov-06 4:08

If the explanation was not clear, you need to learn how to use agregates in sql statement clauses.

Re: SQL Query - join or exists?

Dewald27-Nov-06 4:21

Dewald

27-Nov-06 4:21

edboe wrote:
If the explanation was not clear, you need to learn how to use agregates in sql statement clauses.

So sorry for wasting your time. I'm sure the explanation was clear and I know that I need to learn how to use aggregates in sql statement clauses - which is exactly why I came here.

My apologies again if I asked for too much help. Here's hoping that someone else might be a little bit more forgiving.

Re: SQL Query - join or exists?

ed^nrgc27-Nov-06 9:15

ed^nrgc

27-Nov-06 9:15

Have you looked up the HAVING clause and realized how easy it actually is?

Re: SQL Query - join or exists?

Pete O'Hanlon27-Nov-06 9:16

Pete O'Hanlon

27-Nov-06 9:16

You could always do this:

select o1.* from orders o1<br />
where o1.orderdate = (select<br />
o2.orderdate from orders o2<br />
where o1.custid = o2.custid)

Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."

Deja View - the feeling that you've seen this post before.

Re: SQL Query - join or exists?

Michael Potter27-Nov-06 10:16

Michael Potter

27-Nov-06 10:16

The only tricky part is getting the OrderDescr field into your result. There are a few ways to accomplish this and I have one listed below (TSQL) that should work. Life would be easier if you had a primary key on the order table.

SELECT 
    *
FROM
    Orders o1
INNER JOIN
    (SELECT 
        custID,
        MIN(OrderDate)
     FROM 
        Orders 
     GROUP BY
	custId) o2
    ON (o1.CustId = o2.CustId AND
        o1.OrderDate = o2.OrderDate)

SQL help needed

jegastar27-Nov-06 2:06

jegastar

27-Nov-06 2:06

Hi everybody,
I hava a table like this:

Table Name- TmpTbl

id PD_Id Remark
1 1 Good
2 2 Bad
3 2 fair
4 3 Bad
5 3 Good

In this i want the select query for the result like this:

id PD_Id Remark
1 1 Good
2 2 Bad
4 3 Bad

Can you help me?

Regards,
Jegastar D.

Re: SQL help needed

Pete O'Hanlon27-Nov-06 2:15

Pete O'Hanlon

27-Nov-06 2:15

In your example, just select where id = 1, 2 or 4.

If you are looking for more complex behaviour, then you need to specify what it is that you are looking for, as we can't divine it from your example.

Re: SQL help needed

jegastar27-Nov-06 2:28

jegastar

27-Nov-06 2:28

Acctuly this is my table

id PD_Id Remark
1 1 Good
2 2 Bad
3 2 fair
4 3 Bad
5 3 Good

In this i want the select query for the following result

id PD_Id Remark
1 1 Good
2 2 Bad
4 3 Bad

Re: SQL help needed

jegastar27-Nov-06 2:30

jegastar

27-Nov-06 2:30

Id, PD_ID, Remark are the 3 fields that i have used in this table.

Re: SQL help needed

Rob Graham27-Nov-06 2:55

Rob Graham

27-Nov-06 2:55

There is (almost) nothing consistent or special about your desired result set (no specific visible relationship between the value of remark, id , PD_ID).

The resultset can be produced with
"select top 3 id,pd_Id,remark from table where Remark in ('Good','Bad') order by PD_Id ASC"

But that does not seem particularly useful.

"trigger"

ravikiranreddydharmannagari26-Nov-06 20:16

ravikiranreddydharmannagari

26-Nov-06 20:16

iam having 10 triggers for a table
i want view the triggers
which trigger is firing

is it possible to know
or any system stored prodeure is avaliable in sqlserver 2000

with regards

Re: "trigger"

Pete O'Hanlon27-Nov-06 1:41

Pete O'Hanlon

27-Nov-06 1:41

In Enterprise Manager, right click on the table and select Manage Triggers. The listbox at the top allows you to view the different triggers - by default it shows the New trigger option.

Re: "trigger"

Eric Dahlvang27-Nov-06 3:23

Eric Dahlvang

27-Nov-06 3:23

This will give you a list of all triggers on a table:
sp_helptrigger[^]

You might want to look at this too:
Using Nested Triggers[^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Connecting Problem [modified]

firebow300726-Nov-06 19:40

firebow3007

26-Nov-06 19:40

Hello everyone:

I have a MySQL server running on a linux that is working properly, and i have errors trying to connect to the server on a windows NT client using ODBC. The error i am getting is Can't connect to MySQL 10060. The driver i am using is ODBC 3.51,(i wish to create a vb app using the driver to access the database) I am quite sure that the parameters that i specified for the host name etc are correct. I even downloaded the MySQL control centre to test the connection to the MySQL linux server, but it still doesnt work. I tried using the same ODBC driver and the same windows client to connect to another dummy MySQL server running on a remote windows PC and it works. Also I have a website coded in PHP that can access the MySQL database on any windows pc, so it couldn't possibly be that the MySQL server blocks out my clients IP address right?
Please help Thanks in advance

andy

-- modified at 2:47 Monday 27th November, 2006

Re: Connecting Problem

Paul Conrad25-Dec-06 18:22

Paul Conrad

25-Dec-06 18:22

firebow3007 wrote:
it couldn't possibly be that the MySQL server blocks out my clients IP address right?

It shouldn't. Sounds like problem on client end...

If you try to write that in English, I might be able to understand more than a fraction of it. - Guffa

Connection pool and open & close ...???

devboycpp25-Nov-06 19:07

devboycpp

25-Nov-06 19:07

How much expensive openning and closing connection is when connection pool is used ???

Assume I invoke four stored procedures in a page , I open and close the connection for invoking each . How much will it affect the performance of that page ??

calling this in a page :

<br />
Connection.Open();<br />
//Invoke the stored procedure n1<br />
Connection.Close();<br />
<br />
Connection.Open();<br />
//Invoke the stored procedure n2<br />
Connection.Close();<br />
<br />
Connection.Open();<br />
//Invoke the stored procedure n3<br />
Connection.Close();<br />
<br />
Connection.Open();<br />
//Invoke the stored procedure n4<br />
Connection.Close();<br />

Is it worth replacing it with this. I have made the site like above.It is so hard for me to replace that. What is your suggestion ?

<br />
Connection.Open();<br />
<br />
//Invoke the stored procedure n1<br />
<br />
<br />
//Invoke the stored procedure n2<br />
<br />
<br />
//Invoke the stored procedure n3<br />
<br />
<br />
//Invoke the stored procedure n4<br />
<br />
Connection.Close();<br />

Best regards
Hossein
Yahoo ID :hosseinnick

Re: Connection pool and open & close ...???

nguyenvhn26-Nov-06 15:12

nguyenvhn

26-Nov-06 15:12

I think the first way is acceptable. The close method returns connection to the pool and open method get a freely connection from pool. The second way seems to be a bit better performance but it will charge a lots of cost of changing sources

Oracle [modified]

Chira Chandra25-Nov-06 16:03

Chira Chandra

25-Nov-06 16:03

Hello All experts,
I am Jayachandra.C, My problem is
I am able to create New User in Oracle database and able to give previleges on sequences, insert, update like. User created. But when i am trying to create a table in the created user table is not creating. Please give me the solution & reason where i failed. please

thank you sir,

Jayachandra.C

-- modified at 4:07 Sunday 3rd December, 2006

how to do normalization in sql database

Rmesh25-Nov-06 5:06

Rmesh

25-Nov-06 5:06

in my project i am using sql server i would like to do normalization how to do help

Re: how to do normalization in sql database

User 171649225-Nov-06 5:26

User 1716492

25-Nov-06 5:26

For basic normalisation theory here are some tutorial links.

1. http://www.bkent.net/Doc/simple5.htm[^]

2. http://www.cs.sunysb.edu/~liu/cse305/ch8part1.ps.gz[^]

You could always visit both Microsoft, IBM and the major database makers' websites, there you will find excellent tutorials.

modified 1-Aug-19 21:02pm.

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