File Read and FileReadLine

I am using the commands FileRead and FileReadLine
in a JAL script to read the output of a Sql Command which was
written to a text file.

When I use FileRead or FileReadLine only the first column in the
list of rows is read into the Line variable.

How can I read the entire line.

Thanks,

Leslie

It means that the file you are reading is not a text file or it contains one of the following: carriage-return, end-of-line, end-of-file or nil (ASCII 0) character within the text after the first column. Try opening this file in the Notepad, you should see the problem right away.

: I am using the commands FileRead and FileReadLine
: in a JAL script to read the output of a Sql Command which was
: written to a text file.

: When I use FileRead or FileReadLine only the first column in the
: list of rows is read into the Line variable.

: How can I read the entire line.

: Thanks,

: Leslie

: It means that the file you are reading is not a text file or it contains one
: of the following: carriage-return, end-of-line, end-of-file or nil (ASCII
: 0) character within the text after the first column. Try opening this file
: in the Notepad, you should see the problem right away.

I have tried that .. The file looks like a Text File ..actually it
is created using your command;
RunAndWait ("c:\armstest\sql_locks.bat > c:\armstest\out.txt", "", 3, process_id)

where sql_locks runs a remote storedprocedure and the resusts are piped to out.txt

The input file looks like;

dcdb dckid user
------------------------------ ----------- ------------------------------
cdb_dcdb 49606 niless
cdb_dcdb 49226 airda
cdb_dcdb 49616 benfiel
(return status = 0)

In the Jal script I am using FileRead to count the no of lines in the file then
I am using FileReadLine to read the lines and process the detail lines.

However as stated earlier only the column dcdb is being read from the detail lines.

I am using version 3.4.2 could there be bug in the file read commands.

I have no problems reading this file (after copy and paste from your message).

Can you post the entire script?

: I have tried that .. The file looks like a Text File ..actually it
: is created using your command;
: RunAndWait ("c:\armstest\sql_locks.bat > c:\armstest\out.txt",
: "", 3, process_id)

: where sql_locks runs a remote storedprocedure and the resusts are piped to
: out.txt

: The input file looks like;

: dcdb dckid user
: ------------------------------ ----------- ------------------------------
: cdb_dcdb 49606 niless
: cdb_dcdb 49226 airda
: cdb_dcdb 49616 benfiel
: (return status = 0)

: In the Jal script I am using FileRead to count the no of lines in the file
: then
: I am using FileReadLine to read the lines and process the detail lines.

: However as stated earlier only the column dcdb is being read from the detail
: lines.

: I am using version 3.4.2 could there be bug in the file read commands.

: I have no problems reading this file (after copy and paste from your
: message).

: Can you post the entire script?

// Beginning of script
//
Dim process_id, number
Dim command, string
Dim output, string
Dim position, number
Dim err_found, boolean

RunAndWait ("c:\armstest\sql_locks.bat > c:\armstest\out.txt", "", 3, process_id)

// Send email message
//MailSend("benfiel", "", "benfiel", "ARMS LOCK MGR", "Error occured while running sql_locks.BAT")

Dim file_number, number
Dim file_number2, number
// delete this one
Dim recipient, string
Dim record_line, string
Dim end_of_file, boolean
Dim detail_line, boolean

Dim MyPos, number
Dim exception, boolean
Dim LineNo, number
Dim MaxLineNo, number

// Open recipients file for reading line by line
FileOpen( "c:\armstest\out.txt", "LineMode", "Read", "", file_number )
FileOpen( "c:\armstest\locks.out", "LineMode", "Write", "True", file_number2 )

// Read file line by line until the end of file.
// Each recipient must be on a separate line

EOF( file_number, end_of_file )

Set LineNo, 1
Set MaxLineNo, 1

LoopUntil( end_of_file, END_LOOP )

FileRead( file_number, record_line )

EOF( file_number, end_of_file )

Add MaxLineNo, 1, MaxLineNo

END_LOOP:

// Done with sending message, release file
FileClose( file_number )

LoopUntil( exception, END_LOOP )

IsGreater( LineNo, MaxLineNo, exception)

if( exception, END_WRITE, DO_WRITE )

DO_WRITE:

// Do something here

// ...

//FileReadLine( file_number, LineNo, record_line )

FileReadLine( "c:\armstest\out.txt", LineNo, record_line )

messageBox(record_line)

Pos( record_line, "cdb_dcdb", 1, MyPos )

IsGreater( MyPos, 0, detail_line)

if( detail_line, ADD_TO_FILE, END_WRITE )

ADD_TO_FILE:

FileWrite( file_number2, record_line )

END_WRITE:

//EOF( file_number, end_of_file )

Add LineNo, 1, LineNo

END_LOOP:

FileClose( file_number2 )

// End of script

--------------------------

The Batch file sql_locks.bat contains the next 2 lines below:

@echo off
isql -Umyname -Pmypassword -SServer < c:\armstest\locks.sql

---------------------------

I have realised that Sybase Formats the Output so that the
columns are alligned.... However I also realised that your
Discussion Board removes the formating. Without the formating
the ReadLine Works just fine.
Is there a way that I can remove the lines form the result file.

Thanks

Leslie

Sorry for another questions, can you post locks.sql so I can reproduce your output file as is?

: // Beginning of script
: //
: Dim process_id, number
: Dim command, string
: Dim output, string
: Dim position, number
: Dim err_found, boolean

: RunAndWait ("c:\armstest\sql_locks.bat > c:\armstest\out.txt",
: "", 3, process_id)

: // Send email message
: //MailSend("benfiel", "", "benfiel", "ARMS
: LOCK MGR", "Error occured while running sql_locks.BAT")

: Dim file_number, number
: Dim file_number2, number
: // delete this one
: Dim recipient, string
: Dim record_line, string
: Dim end_of_file, boolean
: Dim detail_line, boolean

: Dim MyPos, number
: Dim exception, boolean
: Dim LineNo, number
: Dim MaxLineNo, number

: // Open recipients file for reading line by line
: FileOpen( "c:\armstest\out.txt", "LineMode",
: "Read", "", file_number )
: FileOpen( "c:\armstest\locks.out", "LineMode",
: "Write", "True", file_number2 )

: // Read file line by line until the end of file.
: // Each recipient must be on a separate line

: EOF( file_number, end_of_file )

: Set LineNo, 1
: Set MaxLineNo, 1

: LoopUntil( end_of_file, END_LOOP )

: FileRead( file_number, record_line )

: EOF( file_number, end_of_file )

: Add MaxLineNo, 1, MaxLineNo

: END_LOOP: // Done with sending message, release file
: FileClose( file_number )

: LoopUntil( exception, END_LOOP )

: IsGreater( LineNo, MaxLineNo, exception)

: if( exception, END_WRITE, DO_WRITE )

: DO_WRITE: // Do something here

: // ...

: //FileReadLine( file_number, LineNo, record_line )

: FileReadLine( "c:\armstest\out.txt", LineNo, record_line )

: messageBox(record_line)

: Pos( record_line, "cdb_dcdb", 1, MyPos )

: IsGreater( MyPos, 0, detail_line)

: if( detail_line, ADD_TO_FILE, END_WRITE )

: ADD_TO_FILE: FileWrite( file_number2, record_line )

: END_WRITE: //EOF( file_number, end_of_file )

: Add LineNo, 1, LineNo

: END_LOOP: FileClose( file_number2 )

: // End of script

: --------------------------

: The Batch file sql_locks.bat contains the next 2 lines below: @echo off
: isql -Umyname -Pmypassword -SServer < c:\armstest\locks.sql

: ---------------------------

: I have realised that Sybase Formats the Output so that the
: columns are alligned.... However I also realised that your
: Discussion Board removes the formating. Without the formating
: the ReadLine Works just fine.
: Is there a way that I can remove the lines form the result file.

: Thanks

: Leslie

Can you run FileReplaceEx for the output file and replace that formatting symbols with spaces before starting to read that file line by line?

: // Beginning of script
: //
: Dim process_id, number
: Dim command, string
: Dim output, string
: Dim position, number
: Dim err_found, boolean

: RunAndWait ("c:\armstest\sql_locks.bat > c:\armstest\out.txt",
: "", 3, process_id)

: // Send email message
: //MailSend("benfiel", "", "benfiel", "ARMS
: LOCK MGR", "Error occured while running sql_locks.BAT")

: Dim file_number, number
: Dim file_number2, number
: // delete this one
: Dim recipient, string
: Dim record_line, string
: Dim end_of_file, boolean
: Dim detail_line, boolean

: Dim MyPos, number
: Dim exception, boolean
: Dim LineNo, number
: Dim MaxLineNo, number

: // Open recipients file for reading line by line
: FileOpen( "c:\armstest\out.txt", "LineMode",
: "Read", "", file_number )
: FileOpen( "c:\armstest\locks.out", "LineMode",
: "Write", "True", file_number2 )

: // Read file line by line until the end of file.
: // Each recipient must be on a separate line

: EOF( file_number, end_of_file )

: Set LineNo, 1
: Set MaxLineNo, 1

: LoopUntil( end_of_file, END_LOOP )

: FileRead( file_number, record_line )

: EOF( file_number, end_of_file )

: Add MaxLineNo, 1, MaxLineNo

: END_LOOP: // Done with sending message, release file
: FileClose( file_number )

: LoopUntil( exception, END_LOOP )

: IsGreater( LineNo, MaxLineNo, exception)

: if( exception, END_WRITE, DO_WRITE )

: DO_WRITE: // Do something here

: // ...

: //FileReadLine( file_number, LineNo, record_line )

: FileReadLine( "c:\armstest\out.txt", LineNo, record_line )

: messageBox(record_line)

: Pos( record_line, "cdb_dcdb", 1, MyPos )

: IsGreater( MyPos, 0, detail_line)

: if( detail_line, ADD_TO_FILE, END_WRITE )

: ADD_TO_FILE: FileWrite( file_number2, record_line )

: END_WRITE: //EOF( file_number, end_of_file )

: Add LineNo, 1, LineNo

: END_LOOP: FileClose( file_number2 )

: // End of script

: --------------------------

: The Batch file sql_locks.bat contains the next 2 lines below: @echo off
: isql -Umyname -Pmypassword -SServer < c:\armstest\locks.sql

: ---------------------------

: I have realised that Sybase Formats the Output so that the
: columns are alligned.... However I also realised that your
: Discussion Board removes the formating. Without the formating
: the ReadLine Works just fine.
: Is there a way that I can remove the lines form the result file.

: Thanks

: Leslie

: Sorry for another questions, can you post locks.sql so I can reproduce your
: output file as is?

--Locks.sql calls a stored procedure "list_locks" on a remote server "OIROUTER"

exec OIROUTER...list_locks
go

-- end of sql

What I am trying to do is to capture the results of the storedprocedure and
re-process it as an input file.
Similar to how Awk would be used in Unix to do pattern recognition.

When I edit the result file it does not contain any funny
characters the columns just seem to be padded with spaces to
make them left or right alligned.

For testing purposes you can test replace the code in locks.sql
with something like

select a,b,c from table_name

go

where a is a char field, b is numeric and c is char

: I have no problems reading this file (after copy and paste from your
: message).

: Can you post the entire script?

// Beginning of script
//
Dim process_id, number
Dim command, string
Dim output, string
Dim position, number
Dim err_found, boolean

RunAndWait ("c:\armstest\sql_locks.bat > c:\armstest\out.txt", "", 3, process_id)

// Send email message
//MailSend("benfiel", "", "benfiel", "ARMS LOCK MGR", "Error occured while running sql_locks.BAT")

Dim file_number, number
Dim file_number2, number
// delete this one
Dim recipient, string
Dim record_line, string
Dim end_of_file, boolean
Dim detail_line, boolean

Dim MyPos, number
Dim exception, boolean
Dim LineNo, number
Dim MaxLineNo, number

// Open recipients file for reading line by line
FileOpen( "c:\armstest\out.txt", "LineMode", "Read", "", file_number )
FileOpen( "c:\armstest\locks.out", "LineMode", "Write", "True", file_number2 )

// Read file line by line until the end of file.
// Each recipient must be on a separate line

EOF( file_number, end_of_file )

Set LineNo, 1
Set MaxLineNo, 1

LoopUntil( end_of_file, END_LOOP )

FileRead( file_number, record_line )

EOF( file_number, end_of_file )

Add MaxLineNo, 1, MaxLineNo

END_LOOP:

// Done with sending message, release file
FileClose( file_number )

LoopUntil( exception, END_LOOP )

IsGreater( LineNo, MaxLineNo, exception)

if( exception, END_WRITE, DO_WRITE )

DO_WRITE:

// Do something here

// ...

//FileReadLine( file_number, LineNo, record_line )

FileReadLine( "c:\armstest\out.txt", LineNo, record_line )

messageBox(record_line)

Pos( record_line, "cdb_dcdb", 1, MyPos )

IsGreater( MyPos, 0, detail_line)

if( detail_line, ADD_TO_FILE, END_WRITE )

ADD_TO_FILE:

FileWrite( file_number2, record_line )

END_WRITE:

//EOF( file_number, end_of_file )

Add LineNo, 1, LineNo

END_LOOP:

FileClose( file_number2 )

// End of script

--------------------------

The Batch file sql_locks.bat contains the next 2 lines below:

@echo off
isql -Umyname -Pmypassword -SServer < c:\armstest\locks.sql

---------------------------

I have realised that Sybase Formats the Output so that the
columns are alligned.... However I also realised that your
Discussion Board removes the formating. Without the formating
the ReadLine Works just fine.
Is there a way that I can remove the lines form the result file.

Thanks

Leslie

Ok, I don't have this procedure and cannot reproduce your result, but using SELECT * FROM syslocks works just fine for me.
I also cannot reproduce your problem with FileRead and text files.

Please email your output file to support@softtreetech.com and your JAL script so we can reproduce your situation here.

In a mean time you may want to try using 24x7 to execute your SQL directly and write results of it to an output file. You will not need to deal with an input file.
Here is how simple it can be done:

Dim rows, number

DatabaseConnectEx "Sybase SQL Server 11.x and later", &
"myserver", "mydb", "myname", "mypassword", TRUE

DatabaseRetrieve "EXEC OIROUTER..list_locks", rows
DatabaseSetFilter "mycolumn='cdb_dcdb'"
DatabaseSave "c:\\armstest\\locks.out", "TXT", rows

DatabaseDisconnect

: --Locks.sql calls a stored procedure "list_locks" on a remote
: server "OIROUTER"

: exec OIROUTER...list_locks
: go

: -- end of sql

: What I am trying to do is to capture the results of the storedprocedure and
: re-process it as an input file.
: Similar to how Awk would be used in Unix to do pattern recognition.

: When I edit the result file it does not contain any funny
: characters the columns just seem to be padded with spaces to
: make them left or right alligned.

: For testing purposes you can test replace the code in locks.sql
: with something like

: select a,b,c from table_name

: go

: where a is a char field, b is numeric and c is char

Hi Suport Guyz,

I am very impressed. I have tried your suggestion to use
the DatabaseRetrieve to get the results from the remote sybase server.

Thanks very much for your timely advice I was able to automate a very important
Task that will save our users and help Desk Staff lot of time on a daily basis,
by automatically remopving stale locks on documents in the workflow.

Thanks again,

Leslie Benfield

PS. I have added

Dim
//

DatabaseConnectEx( "Sybase SQL Server 11.x and later","SERVER" ,"DATABASE" , "ME", "MYWORD", True )
DatabaseRetrieve "EXEC OIROUTER...list_locks", rows
DatabaseSetFilter "pos(dcdb, 'cdb')>0"

ForNext( row, 1, rows, 1, NEXT )

// get the value for docket_id and user_id

DatabaseGet(row, 2, dckid )

DatabaseGet(row, 3, user_txt )

// Now find out if this user is currently logged in

Concatex "select * from master..sysprocesses where convert(char(12), suser_name(suid)) = ", "\"", user_txt, "\"" , ss_sql

DatabaseExecute( ss_sql, return_code)

IsGreater( return_code, 0, user_working )

If user_working, IGNORE_UNLOCK, UNLOCK_DOCKET

UNLOCK_DOCKET:

Concatex "OIROUTER...unlock ", "\"", "cdb_dcdb", "\"", ", ", dckid, ss_sql

DatabaseExecute( ss_sql, return_code)

MailSend "Arms.Lock.Manager", "", "benfiel", "DOCUMENT UNLOCKED" , "The Arms Unlock Manager has just released a stale Lock from the Travel System Have a good day. Please do not reply to this e-mail. This is an Automated Message"

IGNORE_UNLOCK:

//

: Ok, I don't have this procedure and cannot reproduce your result, but using
: SELECT * FROM syslocks works just fine for me.
: I also cannot reproduce your problem with FileRead and text files.

: Please email your output file to support@softtreetech.com and your JAL script
: so we can reproduce your situation here.

: In a mean time you may want to try using 24x7 to execute your SQL directly
: and write results of it to an output file. You will not need to deal with
: an input file.
: Here is how simple it can be done: Dim rows, number

: DatabaseConnectEx "Sybase SQL Server 11.x and later", &
: "myserver", "mydb", "myname",
: "mypassword", TRUE

: DatabaseRetrieve "EXEC OIROUTER..list_locks", rows
: DatabaseSetFilter "mycolumn='cdb_dcdb'"
: DatabaseSave "c:\\armstest\\locks.out", "TXT", rows

: DatabaseDisconnect