Need to know the basic approach (code) for a remote program/app to query a database and obtain the results.


Up until now my routines have always called a stored procedure to transfer or dump data.  Now however, I need to know how to make a SELECT statement and obtain the results within my program/app.  Also, I need to know what the basic error trapping code is.  In other words, if the SELECT statement fails for any reason, or if a subsequent sp call fails, how would I know?

To further clarify this senario, I would:

1)  issue a SELECT statement to table_1
2)  store the results in variables
3)  based upon the results, create a string using those variables
4)  call SQLExecDirect() to transfer the data to table_2

Again, if an error occurs with either step 1 or step 4, I need to know.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It sounds like you don't need to do the four steps that you suggest, to accomplish what you wish to accomplish.

I would suggest a faster approach like so.

insert into table_2
select *
from table_1

Now, you mention string transformations, so you might need to do something like

insert into table_2 (new_column)
when old_column = 'this' then 'that'
else 'the other'
end as new_column
from table_1

You can write the above, then, as a stored procedure and implement error handling.

create procedure usp_dostuff

begin transaction

insert into table_2 (new_column)
when old_column = 'this' then 'that'
else 'the other'
end as new_column
from table_1

if @@error != 0 rollback
else commit

what language are you using VB, ASP, Java, a .NET platform?
John500Author Commented:
I'm using VC++.

I've done quite a bit of this in the past using stored procedures but it's different this time because I'll be taking data from one database and dumping it to another.  That is, from Microsoft SQL to Sybase SQL.

Since I'm able to use the Sybase ODBC driver, there is no impact on the type of function calls I use.  That means I'm still using SQLExecDirect() to issue the transaction to both databases.  However, I'm just not sure how to get the results from a SELECT statement issued against the Microsoft database.

The result set coming from the Microsoft database will be one row of say, 16 fields.  I can't use a SELECT INTO statement because the data needs to be extracted into the C++ program.  See what I mean?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

temp tables seems to be the way you want to go.

first the easy part of your question:
capturing errors, like JimV said:

@@Error will give you the error id for the error that fired on the last transaction. but errors arent stored historically.  so for every batch of SQL code you submit you'll probably want to either store the error code in a variable with in your statement or in a temp table.

im not sure i understand the first part of the question (the steps 1-2), as I understand it you will be extracting the data from MS-SQL via a VC++ module then loading the recordset into Sybase?

if this is the case load the record set into a temp table (with a foriegn key to your error code temp table).  select from your temp table, collate into a string, load SQLExecDirect.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John500Author Commented:
Ok, would you believe I'm just now ready to revisit this!!

Here's the scoop, things have changed... the only thing I really want to accomplish from within my C++ program is to check for errors against the Sybase database.  Thus, how do I use FETCH?

To better clarify, the inherent ODBC error trapping which is returned by SQLExecDirect() is not sufficient because sometimes it returns a '1' when half the transaction went well.  Thus, the 2nd line below checks out well when it should not:

SQLstate = SQLExecDirect("MyStoredProc");
if (( rc != SQL_SUCCESS ) && ( rc != SQL_SUCCESS_WITH_INFO)) //not equal to success
return 0;

Therefore, I need to check the output error parameter of the stored procedure I am calling on Sybase (MyStoredProc).  I've been told this is done immediately after my call to SQLExecDirect().  How exactly is this done i.e. :

SQLstate  =   SQLExecDirect("MyStoredProc");
SQLstring =   FETCH("MyStoredProc");

See what I mean?  How exactly is this done?  Thanks
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.