C / C++ / MFC

31-Jul-07 0:53

Hi all,

I'm using an ODBC class that I found on CP. It works very well. I have one question regarding whether a certain function exists in the ODBC API. A function that can perform more or less the following:

Before I can insert a record into the database I need to check whether the certain record exists, so I perform a select statement on the table (with the records details), if I receive an integer value greater than 0 I know that the record exists, thus I need to perform an update statement else I perform a normal insert statement.

Is there a function that can compare the old values of the record with the new values I want to update the record with, and update the specified record.

I heard that Visual Age Smalltalk can perform this task, and had to find out if VC++ can do the same Smile | :)

P.S. If there is a better way of doing it please let me know Sigh | :sigh:

Many Thanks,
Regards,

The only programmers that are better that C programmers are those who code in 1's and 0's Green Alien | [Alien]

Programm3r
My Blog: ^_^

mandanani31-Jul-07 1:14

mandanani

31-Jul-07 1:14

The more elagant solution for the above problem is to create stored procedure that does the job (what ever you need) and call the stored procedure inplace of query

replace the below statement in place of query
{call dbo.SP_XXXXXXXXXXXXX '%1', '%2', '%3', '%4'}

dbo.SP_XXXXXXXXXXXXX is stored procedure
'%1', '%2', '%3', '%4' are arguments to stored procedure.

Programm3r31-Jul-07 1:32

31-Jul-07 1:32

Stored Procedure -> One word 'maintenance'

The only programmers that are better that C programmers are those who code in 1's and 0's Green Alien | [Alien]

Programm3r
My Blog: ^_^

James R. Twine31-Jul-07 2:58

James R. Twine

31-Jul-07 2:58

Programm3r wrote:
Stored Procedure -> One word 'maintenance'

And having raw SQL in code, or generated dynamically does not require maintenance? Smile | :)

Peace!

-=- James
Please rate this message - let me know if I helped or not!<HR>If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Avoid driving a vehicle taller than you and remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles

Programm3r31-Jul-07 3:02

31-Jul-07 3:02

Am I right at saying that each database has different stored procedure syntax ? If so catering for each database is not going to be fun ...

Thanks for the input James.

Regards,

The only programmers that are better that C programmers are those who code in 1's and 0's Green Alien | [Alien]

Programm3r
My Blog: ^_^

James R. Twine31-Jul-07 3:21

James R. Twine

31-Jul-07 3:21

   IME, that depends on how complex the SQL being executed is. For example, the syntax for executing, dropping, and creating a stored procedure is the same on MSSQL 2000, Sybase SQL, and MySQL (I believe), but cursors use different syntax. On every RBDMS that I have worked with, SELECT * FROM ORDERS WHERE ORDER_ID=3 works just fine.

   There are also some subtle "issues" with now an identical SQL batch works between different RBDMSes (which is a serious PITA, BTW), but that too is not specific to stored procedures, but the SQL within the stored procedure.

   A stored procedure also tends to be easier to update in the field - drop and reinstall it on the DB server, as opposed to building an installer that needs to run on each workstation or manually copying files to each workstation. Using an SP also greatly reduces the risk of having workstations mismatched WRT the SQL that they are using, like if all but two workstations are upgraded, so the un-upgraded ones are still building/using older SQL.

   Lastly, if you have a system that never builds dynamic SQL and only uses stored procedures, you can mess around with the DB schema easier because you can change things and just update the SPs or create views for them, quickly update the database and everything continues to work.

   So maintenance costs can actually be less with SPs... Smile | :)

Peace!

Hamed Musavi31-Jul-07 4:32

Hamed Musavi

31-Jul-07 4:32

As others mentioned here, using Stored procedures is the best choice. However if you are using MFC with ODBC, you can do this:

CTablePatient derived from CRecordSet

...
	CTablePatient tablePatient;

	tablePatient.m_strFilter.Format("PK=%ld", atol(m_szPID));
	tablePatient.Open();

        // if record does not exists
	if (tablePatient.IsEOF())
	{
		tablePatient.AddNew();
	}
	else
	{
                // Record is there already, so prepair it for edit
		tablePatient.Edit();
	}

        // Now we actually edit/set fields
	tablePatient.m_Name = m_szName;
	tablePatient.m_Birthday  = m_birthday;
...
        // Now update changes, whether add or edit
	tablePatient.Update();
	tablePatient.Close();

If you want to create a stored procedure and use it in an MFC app either, This tool will save your life:
http://www.codeproject.com/database/spcw.asp[^]

// "Life is very short and is very fragile also." Yanni
while (I'm_alive)
{
cout<<"I love programming.";
}

Programm3r31-Jul-07 4:43

Re: ODBC Database Function Query [modified]

31-Jul-07 4:43

Thanks very much for the snippet and the help, unfortunately I'm not using MFC Frown | :(

Regards,

The only programmers that are better that C programmers are those who code in 1's and 0's Green Alien | [Alien]

Programm3r
My Blog: ^_^

Hamed Musavi31-Jul-07 5:07

Hamed Musavi

31-Jul-07 5:07

Programm3r wrote:
I'm not using MFC

So it's a tough work, mate!
I think you'll need to use CDatabase class. (I think it's not MFC!)
I have an article here In MFC, but Database does not use CRecordSet:
http://www.codeproject.com/miscctrl/CBarChart.asp[^]

In the article I wrote some function that use CDatabase class to execute a query or a stored procedure(With predefined return values). CBarChart::CChartDatabase Is the class.
I wrote these functions that might help:

OpenProc OpenTable ExecuteSQL Close ExecuteProc Connect Prepare GetRow MoveNext IsEOF

I put a sample app and an access database there. You can use the app to see how it works. I hope this will help.

Sorry! This was MFC as well. Just checked MSDN.

-- modified at 11:19 Tuesday 31st July, 2007

-- modified at 11:21 Tuesday 31st July, 2007

Writing to a file

tom groezer31-Jul-07 0:51

tom groezer

31-Jul-07 0:51

I'm accumulating read text from a database in a CString reading one record at a time. Eventually I need to dump this CString into a file created through CFile.

1) The CString contains characters \r\n for indicating new line.
2) The CFile object is created as below
CFile fileObj( strAbsoluteFileName, CFile::modeCreate | CFile::modeWrite | CFile::shareDenyWrite | CFile::typeBinary);

3) The application is unicode build.

When I see the notepad to see the file created I find the following text

0 = 0 , 3 , 5 4 4 , I D S _ S E M _ D O O R _ O P E N , 0 1

1)Thus I do not get the interpretation of '\r\n' in the file
2) An extra space is coming
3)Not all the contents are copied. It should have one more entry similar to above?

Re: Writing to a file

prasad_som31-Jul-07 0:56

prasad_som

31-Jul-07 0:56

tom groezer wrote:
CFile::typeBinary);

Use CFile::typeText instead.

Prasad
MS MVP - VC++

Postthread message

shakumar_2231-Jul-07 0:50

shakumar_22

31-Jul-07 0:50

Hi In VC 8.0 I tried

PostThreadMessage(WM_USER + 100,(WPARAM)_T”tablenm”,0);

Function(WPARAM wp,LPARAM lp)
{
CString str;
Str.format(“%s”,wp);
AfxmessageBox(str);
}

No Error It output correct.

But when I trying as

CString str1=”hai”;
CString* str2;
Str2=new CString (str1);

PostThreadMessage(WM_USER + 100,(WPARAM)_T(str2),0);

Function(WPARAM wp,LPARAM lp)
{
CString str;
Str.format(“%s”,wp);
AfxmessageBox(str);
}
Output was wrong some symbols are displaying

Give me the suggestion how to pass String throuh WPARAM and converting WPARAM TO String.

Awaiting for the suggestion

Regards
shakumar

shakumar

prasad_som31-Jul-07 0:59

prasad_som

31-Jul-07 0:59

shakumar_22 wrote:
Function(WPARAM wp,LPARAM lp)
{
CString str;
Str.format(“%s”,wp);
AfxmessageBox(str);
}

Should be something like,

Function(WPARAM wp,LPARAM lp)
{
CString str;
Str.format("%s",*wp);
AfxmessageBox(str);
}

Prasad
MS MVP - VC++

mandanani31-Jul-07 1:06

mandanani

31-Jul-07 1:06

I think this was a repost.
Try changing from:
CString str1=”hai”;
To:
CString str1=_T(”hai”);

mandanani31-Jul-07 1:17

mandanani

31-Jul-07 1:17

sorry, I missed the pointer to str2. solution suggested by prasad should work.