How to trace the return value of shell command in VB

Posted on 2003-02-25
Medium Priority
Last Modified: 2008-02-01
Hi friends

I'm executing a .sql file(which has around 100 insert statements) from VB using shell command.
If any one of the insert statements fail in .sql i want to rollback the other transactions.
Any idea how to trap it.

I'm doing like this...

nResultVal = Shell("sqlplus vri/vri@gpoint @C:\Query.sql", vbMinimizedNoFocus)

This nResultVal is not consistent also.So i'mnt able to check for the success of all the queries.

Can u let me know??

pls mail at my id vidhyalakshmi_s@infy.com

Question by:vidhyalakshmi
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: 8015940

Expert Comment

ID: 8015959
its always suggested to extract the file contents and execute each statement seperately to get the most accurate results.  

Expert Comment

ID: 8019655
Just so you know what "nResultVal" really is: it's the process ID and can be used, for example, to check if the process is still running or not.

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.


Expert Comment

ID: 8020611
I would try to keep the processes inside the VB app if possible.  Each line could be executed individually and you will know exactly where you are in the list of insert statements.

However, I have one other option for you: stored procedures.  You could take that .sql file and create a stored procedure. You didn't mention if you were in Access, oracle, SQLServer, etc.  But all of them have a way of creating a stored procedure that can handle a rollback for any missed transactions or failures.  Also, you are more likely to have fewer errors when the procedures are compiled inside your database because they can check for dependencies at compile/test.

They also can return specific error codes (you can even customize your own error codes in the stored procedure).  This can help with your error messages or error trapping in your front end code because the message could originate from the database where whomever is doing that work knows the error best and can give a more detailed reasoning for the problem.

Dan M

Expert Comment

ID: 8901152
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 
Experts: Post your closing recommendations!  Who deserves points here?
LVL 49

Expert Comment

ID: 8972761
vidhyalakshmi, an EE Moderator will handle this for you.
Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer

Accepted Solution

Computer101 earned 0 total points
ID: 9667965
PAQed - no points refunded (of 50)

E-E Admin

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

770 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