loading file from the CLIENT (in SP)

Hi there, does anybody know is there a way of loading a file from the client file system to the SP running on the same client connected to IDS? I would use LOAD if that would work in SP, I would use EXTERNAL TABLE but it reads the file from the server side not the client's filesystem. Client is a Windows box (JDBC connection). I was thinking about installing cygwin on this Windows if I could force EXTERNAL TABLE to use local windows filesystem with that? Any one has some idea maybe? Thanks in advance Waldek

WALDEMAR ZNOINSKI wrote: > Hi there, > does anybody know is there a way of loading a file from the client file system > to the SP running on the same client connected to IDS? I would use LOAD if > that would work in SP, I would use EXTERNAL TABLE but it reads the file from > the server side not the client's filesystem. Client is a Windows box (JDBC > connection). I was thinking about installing cygwin on this Windows if I could > force EXTERNAL TABLE to use local windows filesystem with that? > Any one has some idea maybe? FTP? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

There is no way to read a client side file using a stored procedure which runs on the server side. Ther only way would be to expose the client file to the server using something like NFS, then you could possibly use EXTERNAL TABLEs. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jul 29, 2010 at 11:53 AM, WALDEMAR ZNOINSKI <waldek@znoinski.pl>wrote: > Hi there, > does anybody know is there a way of loading a file from the client file > system > to the SP running on the same client connected to IDS? I would use LOAD if > that would work in SP, I would use EXTERNAL TABLE but it reads the file > from > the server side not the client's filesystem. Client is a Windows box (JDBC > connection). I was thinking about installing cygwin on this Windows if I > could > force EXTERNAL TABLE to use local windows filesystem with that? > Any one has some idea maybe? > > Thanks in advance > Waldek > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd2105e086864048c88d00c

hey there, thanks Art for your, nailing as usual, reply We are currently developing our own releasing system, by that I mean: CLIENT machine (Windows, JDBC) releasing Developers code to SERVER IDS(11.5). The assumption here is to use LOAD to load .unl files. Can't find a good way of doing it cause LOAD is NOT working in JDBC, LOAD is not working in SPL (which could be called by JDBC CLIENT) - even if that the file has to reside on the SERVER. We are considering: (1) JDBC to call Stored Proc, which then uses External Tables to load file (either FTP'd or mounted) Implications: - change for all developers in how we load data - more moving parts: stored procs required for releases, FTPing/mounting etc. - would be nice to use existing jdbc ant task rather than invoke a standalone app like dbaccess Outstanding Questions: - none (2) dbaccess on windows, which uses LOAD syntax to stream file directly from windows client (not server) Implications: - consistent with current approach for both developers and support (DBUpdate uses dbaccess already) - less moving parts - need ant to invoke dbaccess executable directly. A little raw but ok Outstanding Questions: - can data file used in LOAD remain on client machine, as long as its accessible to user dbaccess (i.e. no FTP/mounting)? - is dbaccess for windows just a binary on shared drive or does it need to be an install on each developer PC? - can dbaccess specify username & password on command line or does windows user have to be a trusted user on server? Does anybody have any ideas or suggestions? cheers

See comments below: Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 4, 2010 at 5:30 AM, WALDEMAR ZNOINSKI <waldek@znoinski.pl>wrote: > hey there, > thanks Art for your, nailing as usual, reply > We are currently developing our own releasing system, by that I mean: > CLIENT machine (Windows, JDBC) releasing Developers code to SERVER > IDS(11.5). > The assumption here is to use LOAD to load .unl files. Can't find a good > way > of doing it cause LOAD is NOT working in JDBC, LOAD is not working in SPL > (which could be called by JDBC CLIENT) - even if that the file has to > reside > on the SERVER. > Right, LOAD is a verb implemented in dbaccess, not an SQL command that the engine recognizes. > We are considering: > > (1) JDBC to call Stored Proc, which then uses External Tables to load file > (either FTP'd or mounted) > Implications: > > - change for all developers in how we load data > > - more moving parts: stored procs required for releases, FTPing/mounting > etc. > > - would be nice to use existing jdbc ant task rather than invoke a > standalone > app like dbaccess > Outstanding Questions: > > - none > > (2) dbaccess on windows, which uses LOAD syntax to stream file directly > from > windows client (not server) > Implications: > > - consistent with current approach for both developers and support > (DBUpdate > uses dbaccess already) > > - less moving parts > > - need ant to invoke dbaccess executable directly. A little raw but ok > Outstanding Questions: > > - can data file used in LOAD remain on client machine, as long as its > accessible to user dbaccess (i.e. no FTP/mounting)? > Correct. Since it is dbaccess that is actually reading the file for a LOAD, it resides on the client. > > - is dbaccess for windows just a binary on shared drive or does it need to > be > an install on each developer PC? > AFAIK, dbaccess would have to be locally accessible to the client. I think running it from a shared drive would work. Note that dbaccess is part of the engine distribution and so not distributable. You will have to see about licensing issues, though with the new free "editions" available, there should be a way around that one now. > > - can dbaccess specify username & password on command line or does windows > user have to be a trusted user on server? > Not on the command line, no. Trusted user is easiest. If you can get Jonathan Leffler's sqlcmd to compile on Windows (there was a thread on this recently) it can accept userid and password on the command line and does have LOAD functionality (as well as a command line LOAD option capability). > > Does anybody have any ideas or suggestions? > > cheers > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e0cb4e887827431b8c048cfcbf4e

W dniu 2010-08-04 12:17, Art Kagel pisze: > See comments below: > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Aug 4, 2010 at 5:30 AM, WALDEMAR ZNOINSKI<waldek@znoinski.pl>wrote: > >> hey there, >> thanks Art for your, nailing as usual, reply >> We are currently developing our own releasing system, by that I mean: >> CLIENT machine (Windows, JDBC) releasing Developers code to SERVER >> IDS(11.5). >> The assumption here is to use LOAD to load .unl files. Can't find a good >> way >> of doing it cause LOAD is NOT working in JDBC, LOAD is not working in SPL >> (which could be called by JDBC CLIENT) - even if that the file has to >> reside >> on the SERVER. >> > Right, LOAD is a verb implemented in dbaccess, not an SQL command that the > engine recognizes. > >> We are considering: >> >> (1) JDBC to call Stored Proc, which then uses External Tables to load file >> (either FTP'd or mounted) >> Implications: >> >> - change for all developers in how we load data >> >> - more moving parts: stored procs required for releases, FTPing/mounting >> etc. >> >> - would be nice to use existing jdbc ant task rather than invoke a >> standalone >> app like dbaccess >> Outstanding Questions: >> >> - none >> >> (2) dbaccess on windows, which uses LOAD syntax to stream file directly >> from >> windows client (not server) >> Implications: >> >> - consistent with current approach for both developers and support >> (DBUpdate >> uses dbaccess already) >> >> - less moving parts >> >> - need ant to invoke dbaccess executable directly. A little raw but ok >> Outstanding Questions: >> >> - can data file used in LOAD remain on client machine, as long as its >> accessible to user dbaccess (i.e. no FTP/mounting)? >> > Correct. Since it is dbaccess that is actually reading the file for a LOAD, > it resides on the client. > >> - is dbaccess for windows just a binary on shared drive or does it need to >> be >> an install on each developer PC? >> > AFAIK, dbaccess would have to be locally accessible to the client. I think > running it from a shared drive would work. Note that dbaccess is part of > the engine distribution and so not distributable. You will have to see > about licensing issues, though with the new free "editions" available, there > should be a way around that one now. > >> - can dbaccess specify username& password on command line or does windows >> user have to be a trusted user on server? >> > Not on the command line, no. Trusted user is easiest. If you can get > Jonathan Leffler's sqlcmd to compile on Windows (there was a thread on this > recently) it can accept userid and password on the command line and does > have LOAD functionality (as well as a command line LOAD option capability). > >> Does anybody have any ideas or suggestions? >> >> cheers >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --e0cb4e887827431b8c048cfcbf4e > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > As you are on windows platform the simplest thing would be to grab my load.exe utility. http://sites.google.com/site/robsosno2/downloads It requires only CSDK. It was linked with old CSDK but I believe that it should work with newer one too. If not then it is possible to recompile. sqlcmd is a good too but only under unix. In Windows it doesn't compile currently (only some old versions). Robert