Need SQL or Stored Procedure to verify files exist on network drive
Frank has a table of file paths and wants SQL or a stored procedure (an Informix equivalent of SQL Server's xp_cmdshell) to check quickly whether each file exists and log the misses. Replies note SPL's SYSTEM verb can run OS commands but can't return results directly (it would have to insert into a table), and suggest instead writing a C or Java UDR wrapping stat/fstat on IDS 9.x+. Another poster cautioned that this runs on the database server, not necessarily where the files live, and that the real fix may be diagnosing the slow application (e.g. network-mount timeouts). No confirmed resolution from the original poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Good morning! I am not an Informix or Unix expert so I hope I can get some help. We have a table that contains the name (including path) of files on a server. We have a windows application that uses the table information to copy the files to another folder. The application checks to see if each of the files does in fact exist but this process is taking too long so I am looking for SQL (or a stored procedure) that can verify the existence of the files separately. If a file does not exist we need to write this information to a log file. I work primarily with SQL Server and in that environment we can use xp_cmdshell to execute operating system commands. I assume I would need to use the SYSTEM statement in Informix. Thank you in advance! Frank
FrankC wrote: > Good morning! > > I am not an Informix or Unix expert so I hope I can get some help. > > We have a table that contains the name (including path) of files on a > server. We have a windows application that uses the table information > to copy the files to another folder. The application checks to see if > each of the files does in fact exist but this process is taking too > long so I am looking for SQL (or a stored procedure) that can verify > the existence of the files separately. If a file does not exist we > need to write this information to a log file. > > I work primarily with SQL Server and in that environment we can use > xp_cmdshell to execute operating system commands. I assume I would > need to use the SYSTEM statement in Informix. > It's always helpful to post your platform and Informix version information as the answers often depend on version. Yes, you can certainly use the SYSTEM verb in a stored procedure, however, there is no direct way for the program or script you execute to send back information (it would have to insert data into a table for the procedure to find on return). If that fill the bill, fine. If you have IDS 9.xx or later, you can write a UDR (User Defined Routine) in 'C' or Java that can check the existence of the file directly and be called from SQL like any stored procedure once it's been created, loaded into a shared library that's been identified to IDS, and you have defined it as a UDR. Art S. Kagel Oninit > Thank you in advance! > Frank =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================
FrankC wrote: > Good morning! > > I am not an Informix or Unix expert so I hope I can get some help. > > We have a table that contains the name (including path) of files on a > server. We have a windows application that uses the table information > to copy the files to another folder. The application checks to see if > each of the files does in fact exist but this process is taking too > long so I am looking for SQL (or a stored procedure) that can verify > the existence of the files separately. If a file does not exist we > need to write this information to a log file. > > I work primarily with SQL Server and in that environment we can use > xp_cmdshell to execute operating system commands. I assume I would > need to use the SYSTEM statement in Informix. > > Thank you in advance! > Frank Assuming you are on V9+ create a UDR and look at the fstat/fstat64 C call - this should give you want you need. As you appear to be Windows only then it should be straightforward
You're making an assumption that the file/directory exist on the same server that has the Informix instance. A C UDR or even the System Call would shell out to your current system. I would suggest rather than trying to write a stored procedure, look at the current application which is taking too long. There are a lot of questions you should be asking. Is the entire process taking too long, or just the check to see that the file exists? What is the current script/program written in? Are you attempting to query a network mounted drive? It could be that they don't check if the file exists, but try to open a file on a remote machine and wait for either success or failure. A failure would occur after a timeout period. We don't know the answer to these questions because they were never asked. Jumping to writing a UDR doesn't make sense based on the information presented and it may not be a good option. But hey! What do I know? ;-) HTH -G > Date: Thu, 6 Mar 2008 11:58:36 -0600 > From: paul@oninit.com > Subject: Re: Need SQL or Stored Procedure to verify files exist on network drive > To: informix-list@iiug.org > > FrankC wrote: > > Good morning! > > > > I am not an Informix or Unix expert so I hope I can get some help. > > > > We have a table that contains the name (including path) of files on a > > server. We have a windows application that uses the table information > > to copy the files to another folder. The application checks to see if > > each of the files does in fact exist but this process is taking too > > long so I am looking for SQL (or a stored procedure) that can verify > > the existence of the files separately. If a file does not exist we > > need to write this information to a log file. > > > > I work primarily with SQL Server and in that environment we can use > > xp_cmdshell to execute operating system commands. I assume I would > > need to use the SYSTEM statement in Informix. > > > > Thank you in advance! > > Frank > > Assuming you are on V9+ create a UDR and look at the fstat/fstat64 C > call - this should give you want you need. As you appear to be Windows > only then it should be straightforward > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Climb to the top of the charts! Play the word scramble challenge with star power. http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan
Ian Michael Gumby wrote: > You're making an assumption that the file/directory exist on the same > server that has the Informix instance. A reasonable assumption as he says he wants to copy a file between folders. > A C UDR or even the System Call would shell out to your current system. Why would a C-UDR shell out ? Seems to me that wrapping return (stat(filename,buf)) in a UDR is easier that writing a SPL that does a system call > > I would suggest rather than trying to write a stored procedure, look at > the current application which is taking too long. > There are a lot of questions you should be asking. > > Is the entire process taking too long, or just the check to see that the > file exists? What is the current script/program written in? > Are you attempting to query a network mounted drive? It could be that > they don't check if the file exists, but try to open a file on a remote > machine and wait for either success or failure. A failure would occur > after a timeout period. We don't know the answer to these questions > because they were never asked. > > Jumping to writing a UDR doesn't make sense based on the information > presented and it may not be a good option. > > But hey! What do I know? ;-) > > HTH > > -G > > > > Date: Thu, 6 Mar 2008 11:58:36 -0600 > > From: paul@oninit.com > > Subject: Re: Need SQL or Stored Procedure to verify files exist on > network drive > > To: informix-list@iiug.org > > > > FrankC wrote: > > > Good morning! > > > > > > I am not an Informix or Unix expert so I hope I can get some help. > > > > > > We have a table that contains the name (including path) of files on a > > > server. We have a windows application that uses the table information > > > to copy the files to another folder. The application checks to see if > > > each of the files does in fact exist but this process is taking too > > > long so I am looking for SQL (or a stored procedure) that can verify > > > the existence of the files separately. If a file does not exist we > > > need to write this information to a log file. > > > > > > I work primarily with SQL Server and in that environment we can use > > > xp_cmdshell to execute operating system commands. I assume I would > > > need to use the SYSTEM statement in Informix. > > > > > > Thank you in advance! > > > Frank > > > > Assuming you are on V9+ create a UDR and look at the fstat/fstat64 C > > call - this should give you want you need. As you appear to be Windows > > only then it should be straightforward > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > ------------------------------------------------------------------------ > Climb to the top of the charts! Play the word scramble challenge with > star power. Play now! > <http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan>;
Ian Michael Gumby wrote: > You're making an assumption that the file/directory exist on the same > server that has the Informix instance. A reasonable assumption as he says he wants to copy a file between folders. > A C UDR or even the System Call would shell out to your current system. Why would a C-UDR shell out ? Seems to me that wrapping return (stat(filename,buf)) in a UDR is easier that writing a SPL that does a system call > > I would suggest rather than trying to write a stored procedure, look at > the current application which is taking too long. > There are a lot of questions you should be asking. > > Is the entire process taking too long, or just the check to see that the > file exists? What is the current script/program written in? > Are you attempting to query a network mounted drive? It could be that > they don't check if the file exists, but try to open a file on a remote > machine and wait for either success or failure. A failure would occur > after a timeout period. We don't know the answer to these questions > because they were never asked. > > Jumping to writing a UDR doesn't make sense based on the information > presented and it may not be a good option. > > But hey! What do I know? ;-) > > HTH > > -G > > > > Date: Thu, 6 Mar 2008 11:58:36 -0600 > > From: paul@oninit.com > > Subject: Re: Need SQL or Stored Procedure to verify files exist on > network drive > > To: informix-list@iiug.org > > > > FrankC wrote: > > > Good morning! > > > > > > I am not an Informix or Unix expert so I hope I can get some help. > > > > > > We have a table that contains the name (including path) of files on a > > > server. We have a windows application that uses the table information > > > to copy the files to another folder. The application checks to see if > > > each of the files does in fact exist but this process is taking too > > > long so I am looking for SQL (or a stored procedure) that can verify > > > the existence of the files separately. If a file does not exist we > > > need to write this information to a log file. > > > > > > I work primarily with SQL Server and in that environment we can use > > > xp_cmdshell to execute operating system commands. I assume I would > > > need to use the SYSTEM statement in Informix. > > > > > > Thank you in advance! > > > Frank > > > > Assuming you are on V9+ create a UDR and look at the fstat/fstat64 C > > call - this should give you want you need. As you appear to be Windows > > only then it should be straightforward > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > ------------------------------------------------------------------------ > Climb to the top of the charts! Play the word scramble challenge with > star power. Play now! > <http://club.live.com/star_shuffle.aspx?icid=starshuffle_wlmailtextlink_jan>; =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================