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

Posted on 2003-04-01
Medium Priority
Last Modified: 2012-05-04

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.

Question by:John500
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8247628
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


Expert Comment

ID: 8248726
what language are you using VB, ASP, Java, a .NET platform?

Author Comment

ID: 8249486
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?
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.


Accepted Solution

mnye earned 1000 total points
ID: 8249568
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.


Author Comment

ID: 8937164
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

Expert Comment

ID: 9275382
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question