1.

Solve : Need help with finding a string in a file?

Answer»

Hi,

I am executing a bunch of sql files and storing the output in a text file.
If I find an error in the output file, the execution should stop and throw an error message and exit.

Here is the code that I am trying to write. However its not working for me. Can someone point out the MISTAKES I am making.

Code: [Select]@ECHO OFF
cls

set SERVER=SM_SHAREDT5_SQL
set DB=gpldb
set USER=gpldb_dbo
set PWD=gpldb_dbo


for /f "tokens=1 delims=" %%S in (sqlfile.txt) do (

echo EXECUTING FILE %%S
set filename=%%S

isql -S %SERVER% -D %DB% -U %USER% -P %PWD% -i %filename% -o output.txt

for /f "tokens=1 delims=" %%T in (output.txt) do (
echo %%T
echo %%T | find /v /i "msg">errortext.txt
REM IF EXISTS errortext.txt goto :end %filename%
)

)
pause
exit

:end
echo "Error occured in %1"
exit

sqlfile.txt contains 5 sqlfiles. isql does not stop executing the next sql file even If there is an error executing the previous one. Hence I would like to capture the output in the next for loop and stop execution If an error occurs.

Sybase always throwsout a standard error syntax, something like this for example:

Quote

Msg 102, Level 15, State 1:
Obviously the Numbers change based on the error but the line is standard otherwise. What I mean is everytime there is an error:
Quote
Msg "some number", Level "some number", State "Some number":

I dont care about the numbers. I am not sure If I can parse such a line using regular expressions or something.

If someone can help that would be great!!
There's a thread just like this. But I'll answer anyways. This code will test to see if there's an error. If it finds an error it will send out a message.

Code: [Select]if not %errorlevel% equ 0 echo There was an error!


There, if there's an error it will give out an error message. PRETTY simple.I am sorry. I guess I wasn't clear enough. I am not looking for any error's in the batch files. But rather from sybase. If sybase server throws back an error, I want to parse the output file and check if there was an error. Like I mentioned in the post, sybase wont stop execution if it finds an error in the sql scripts. It moves on to execute the next sql script.

For example, If there are 5 Database objects to be executed, Even if there was an error in the second object, Ideally I want the execution to stop there and not move forward. However with sybase(using isql) the execution doesn't stop. Hence I want to capture the error from the output file.

If still there is a CONFUSION, I could try putting more example.I think now I see what you are trying to say. Let me try the error level option. Also saw the other topic with a similar question. The only reason I started this new one was CAUSE I felt its different than the other topic. Cause I was thinking on regular expression terms.

~HiteshRather than this,

Code: [Select]if not %errorlevel% equ 0 echo There was an error!
I prefer this:

Code: [Select]if %errorlevel% GTR 0 echo There was an error!
But this will not find your errors, because

Quote
sybase wont stop execution if it finds an error in the sql scripts.

But we need more examples. How are we going to know if there has been an error?
Let me post the output file here. I have also attached the output file. This is for a wrong syntax(just an example).

The contents of the output file are:

Quote
Msg 102, Level 15, State 1:
Server 'SM_SHAREDT5_SQL', Line 1:
Incorrect syntax near ';'.

The error message will change depending on the type of error.
In general the format of the error will be:

Quote
Msg [Number], Level [Number], State [Number]:


So If you look at the code again, what it does is: for each line in sqlfile.txt it will execute the following statement:


Code: [Select]isql -S %SERVER% -D %DB% -U %USER% -P %PWD% -i %filename% -o output.txt
Before moving on to execute the next line in sqlfile.txt, I wish to check the contents of output.txt and If there is an error, exit out of the for loop printing an error message saying the error occured while executing so and so filename.

Looking at the responses, I am guessing I dont need the 2nd for loop. Let me try removing that and do a find.

I know I have been repeating the same story again and again. Apologies for that, And If you have any specific questions please let me know.



[attachment deleted by admin]Really we need to know of a word or other thing that signals each error. For example if we scanned the output file and saw "Incorrect syntax" then we could capture the error you used as an example. You could do it thus

set syntaxerror=0
type "output.txt" | find /i "incorrect syntax">nul && set syntaxerror=1
if %syntaxerror% EQU 1 (do something)

How many likely errors are there?

Is it the case that the presence of the words Msg, Level, and State always mean an error?



Quote from: Dias de VERANO on January 23, 2009, 10:36:35 AM
Really we need to know of a word or other thing that signals each error.
I totally see your point here. Let me try and get as much information I can about what remains constant during each error.

Quote
For example if we scanned the output file and saw "Incorrect syntax" then we could capture the error you used as an example. You could do it thus

set syntaxerror=0
type "output.txt" | find /i "incorrect syntax">nul && set syntaxerror=1
if %syntaxerror% EQU 1 (do something)
I would rather not want to capture Incorrect Syntax cause I would not expect every error to be a syntax error. But whenever there is an error, there will be a Msg, Level, State error line.

Quote
Is it the case that the presence of the words Msg, Level, and State always mean an error?

One answer I know for sure is that YES the presense of the words Msg, Level and State always mean an error and it will be present everytime there is an error. The numbers change and hence I was thinking in the terms of parsing the line to look for only the characters and avoiding the numbers.

Quote
How many likely errors are there?
Regarding the question about how many likely errors are there, I am not too sure. I have mailed my company's Sybase DBA and the moment I hear from him back I shall update the information here.


Discussion

No Comment Found

Related InterviewSolutions