Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

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.

1 Solution
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?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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.

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.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now