Database

28-Nov-06 0:34

Thank you.

sql syntax

Arif Liminto27-Nov-06 15:53

Arif Liminto

27-Nov-06 15:53

hi i got problem with my sql syntax for updating table because i always got query failed <wrong syntax="">
$firstname& $lastname is the variable that user will fill it to change the old lastname to new lastname

"update friends set lastname = $lastname where firstname = $firstname"

plz help me thanks

Smile | :)

Coding C#27-Nov-06 19:24

Coding C#

27-Nov-06 19:24

Hi,

"update friends set lastname = '" + lastname + "' where firstname = '" + firstname +"'"

use this.
hope this works

Nitin...

Arif Liminto27-Nov-06 20:26

Arif Liminto

27-Nov-06 20:26

hi nitin, thanks for your help but it cause another error

Parse error: parse error, unexpected T_VARIABLE in

do u know how to fix it

asithangae27-Nov-06 21:20

asithangae

27-Nov-06 21:20

use "@" instead of "$"

it will work.

Colin Angus Mackay27-Nov-06 22:30

Colin Angus Mackay

27-Nov-06 22:30

No! NO! NO! NO! NO! NO! NO! NO! NO! NO! NO!

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

Please learn how to interact with a database properly. Read SQL Injection Attacks and Tips on How to Prevent Them[^].

Coding C#28-Nov-06 0:21

Coding C#

28-Nov-06 0:21

hi,
thanks for waking me up.
i am having a look at http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp

cu soon. Smile | :)

bye tkcr.

Nitin...

PostgreSQL, ODBC and transaction strange behavior

Paul Conrad28-Nov-06 13:40

Paul Conrad

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

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.

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.

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.

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.

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.

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?

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.

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

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.

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.

jegastar27-Nov-06 2:28

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

jegastar27-Nov-06 2:30

27-Nov-06 2:30

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