forcing dbsqlexec to exit cleanly

[Gary]

I am developing a simple DB-Library program in C calling SQL Server 2000 on
windows 2003 and NT 4. I have some T-SQL code that checks for the existence
of a table and want to abort the program if the table doesn't exist. I issue
a raiserror if the table doesn't exist and then call RETURN.

I construct the string using sprintf and pass it dbfcnd and dbsqlexec. Since
the commands work, there is no error to halt the execution of the program.
Is there an easy, clean way to force dbsqlexec to fail? Do I need a stored
procedure to return an error code and then deal with that?

Thanks for any advice,

-Gary

[John Bell]

Hi

I have not used DB-Library but using a store procedure is usually the best
way to provide this logic. You can use an output parameter although if you
didn't want to use the return parameter.

John

"Gary" <le********@comcast.net> wrote in message
news:5d********************@comcast.com...

I am developing a simple DB-Library program in C calling SQL Server 2000 on
windows 2003 and NT 4. I have some T-SQL code that checks for the
existence
of a table and want to abort the program if the table doesn't exist. I
issue
a raiserror if the table doesn't exist and then call RETURN.

I construct the string using sprintf and pass it dbfcnd and dbsqlexec.
Since
the commands work, there is no error to halt the execution of the program.
Is there an easy, clean way to force dbsqlexec to fail? Do I need a stored
procedure to return an error code and then deal with that?

Thanks for any advice,

-Gary

[Erland Sommarskog]

[posted and mailed, please reply in news]

Gary (le********@comcast.net) writes:

I am developing a simple DB-Library program in C calling SQL Server 2000
on windows 2003 and NT 4.
How come you are using DB-Library? It's a very good API, but alas Microsoft
thinks differently and has not developed DB-Library since the release of
SQL 6.5. Because of this, there are many ffeatures in SQL Server that
you don't have support for with DB-Library.

Some future version of SQL Server will not support connecting from
DB-Library at all. And already the next version of SQL Server will come
without the files to compile, link and run DB-Library.

If you have legacy software you have. But don't use DB-Library for new
development. Use ODBC or OLE DB instead.
I have some T-SQL code that checks for the existence of a table and want
to abort the program if the table doesn't exist. I issue a raiserror if
the table doesn't exist and then call RETURN.

I construct the string using sprintf and pass it dbfcnd and dbsqlexec.
Since the commands work, there is no error to halt the execution of the
program. Is there an easy, clean way to force dbsqlexec to fail? Do I
need a stored procedure to return an error code and then deal with
that?

If you insist on using DB-Library, you should set up an message
handler with dbmsghandle. This is how DB-Libarary notifies you of
SQL errors: through a callback. I believe by setting the return
status in the message handler, you can control whether the command
fails or not.

Also, after each submitted command, you should always loop with
dbresults and dbnextrow to get all result sets and rows. The message
handler may in fact no be invoked until you call dbresults, since
the message also is a result in some meaning.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gary]

Thanks for the replies. I have a legacy program that I am modifying for a
quick one-off project.

I do have a message handler installed. I solved the problem by putting the
code that checks for the table into a stored procedure and then calling it.
Since it can return a value, returning a non-zero value from the sproc does
the trick and dbsqlexec throws an error.

Thanks again,

-Gary

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn********************@127.0.0.1...

[posted and mailed, please reply in news]

Gary (le********@comcast.net) writes:

I am developing a simple DB-Library program in C calling SQL Server 2000
on windows 2003 and NT 4.
How come you are using DB-Library? It's a very good API, but alas

Microsoft thinks differently and has not developed DB-Library since the release of
SQL 6.5. Because of this, there are many ffeatures in SQL Server that
you don't have support for with DB-Library.

Some future version of SQL Server will not support connecting from
DB-Library at all. And already the next version of SQL Server will come
without the files to compile, link and run DB-Library.

If you have legacy software you have. But don't use DB-Library for new
development. Use ODBC or OLE DB instead.

I have some T-SQL code that checks for the existence of a table and want
to abort the program if the table doesn't exist. I issue a raiserror if
the table doesn't exist and then call RETURN.

I construct the string using sprintf and pass it dbfcnd and dbsqlexec.
Since the commands work, there is no error to halt the execution of the
program. Is there an easy, clean way to force dbsqlexec to fail? Do I
need a stored procedure to return an error code and then deal with
that?

If you insist on using DB-Library, you should set up an message
handler with dbmsghandle. This is how DB-Libarary notifies you of
SQL errors: through a callback. I believe by setting the return
status in the message handler, you can control whether the command
fails or not.

Also, after each submitted command, you should always loop with
dbresults and dbnextrow to get all result sets and rows. The message
handler may in fact no be invoked until you call dbresults, since
the message also is a result in some meaning.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp