Creating an External table

Hi All, I am trying to create an external table in informix as: create external table test_table(cols varchar(10)) using ( datafiles('C:/sample.txt'), format 'delimited', deluxe); but it gives me the error "[Error Code: -26174, SQL State: IX000] Incorrect DATAFILE entry C:/sample.txt." Can anyone suggest me the right syntax for path entry... I have also tried load from "C:\\\\sample.txt" insert into test_table; which gives me a syntax error. Any help is greatly appreciated!!

Reverse the slash ?? Paul Watson Oninit www.oninit.com +1 913 387 7529 On Dec 19, 2013, at 17:20, "BISKET RAJA" <bisketraja@gmail.com> wrote: > Hi All,=20 >=20 > I am trying to create an external table in informix as:=20 >=20 > create external table test_table(cols varchar(10)) using (=20 > datafiles('C:/sample.txt'), format 'delimited', deluxe);=20 >=20 > but it gives me the error "[Error Code: -26174, SQL State: IX000] Incorrec= t=20 > DATAFILE entry C:/sample.txt."=20 >=20 > Can anyone suggest me the right syntax for path entry...=20 >=20 > I have also tried=20 >=20 > load from "C:\\\\sample.txt" insert into test_table;=20>=20 > which gives me a syntax error. Any help is greatly appreciated!!=20 >=20 >=20 > **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20

I did try it but it didn't work out:( Let me give you some more info... I am a new bie to Informix(Zero Knowledge). I am using DBVisualizer tool for connecting to database. Connection type as Informix and load ifxjdbc jar file for jdbc driver. I am not sure if this is same as using Informix platform itself. Anyone please help me understand:(

There's a keyword missing. Try it this way: create external table test_table(cols varchar(10)) using ( datafiles('disk:C:/sample.txt'), format 'delimited', deluxe); You have to identify, inside the quotes, whether the path is a disk file or a pipe. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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, Dec 19, 2013 at 6:20 PM, BISKET RAJA <bisketraja@gmail.com> wrote: > Hi All, > > I am trying to create an external table in informix as: > > create external table test_table(cols varchar(10)) using ( > datafiles('C:/sample.txt'), format 'delimited', deluxe); > > but it gives me the error "[Error Code: -26174, SQL State: IX000] Incorrect > DATAFILE entry C:/sample.txt." > > Can anyone suggest me the right syntax for path entry... > > I have also tried > > load from "C:\\\\sample.txt" insert into test_table;> > which gives me a syntax error. Any help is greatly appreciated!! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b6d99b0c225d204edec6de3

I'm guessing your server is on windows with the path 'c:' referenced. The following works for me with a windows server and dbvisualiser on my pc: CREATE EXTERNAL TABLE (cols varchar(10)) USING (DATAFILES ("DISK:C:\\\\sample.txt"), RECORDEND '\\\\012'); use to get errors until included the 'recordend' piece. Regards, Bryce Stenberg.

Thanks much. I have tried it but it gives me an error saying 'No Resource Permission'. I can create a normal table though. What might be going wrong? Also, is there anyway to directly read the file into a normal table instead of external table? Please let me know. Here's the error.. "09:52:31 [CREATE - 0 row(s), 0.000 secs] [Error Code: -388, SQL State: IX000] No resource permission."

The error means that you do not have the necessary permissions to make changes to the schema of the data such as adding an external table. That complicates life; it means you'll need to negotiate with the DBA for such permission, or ask the DBA to create the table for you, or you'll have to connect to the DB as a user with the relevant privileges. However, it also means you've got past the syntactic problems. On Fri, Dec 20, 2013 at 8:41 AM, BISKET RAJA <bisketraja@gmail.com> wrote: > Thanks much. I have tried it but it gives me an error saying 'No Resource > Permission'. I can create a normal table though. What might be going wrong? > Also, is there anyway to directly read the file into a normal table > instead of > external table? Please let me know. Here's the error.. "09:52:31 [CREATE - > 0 > row(s), 0.000 secs] [Error Code: -388, SQL State: IX000] No resource > permission." > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --001a1133d1f840c1d204ee229ad9