Problem with passing filename for extract proc

[Sharon Zzz]

Hi,

I have an issue with the passing the filename to the proc that extract data.  Here is my proc

*****************************************************************************************************************

alter proc extractFOData 

(@startdate integer,

@endDate integer,

@archiveFileName varchar(255))

begin

select getDate();

set temporary option TEMP_EXTRACT_NULL_AS_EMPTY='ON';

set temporary option TEMP_EXTRACT_COLUMN_DELIMITER=':|:';

set temporary option TEMP_EXTRACT_NAME1=@archiveFileName;

select * from FOrder2012 where tradedateid between @startdate and @enddate;

select getDate();

end

******************************************************************************

However, if I pass '/dump/abc'  I cannot find the output file under /dump

If I hard code in the proc with the file name '/dump/abc',  I will see the file out without any problem.

I wonder if anyone knows how to fix the issue.  Maybe some escape character needed?  I tried some escape character, but still failed.  

Thanks.

Sharon

[cjd]

temp_extract_directory

regards
cjd

--
You received this message because you are subscribed to the Google Groups "iqug" group.
To unsubscribe from this group and stop receiving emails from it, send an email to iqug+uns...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mark Mumy]

I would add, too, that most options cannot include an variable as it is written.  Typically, we would use a string to store the entire command then execute the string/command.

set @str = ‘set temporary option TEMP_EXTRACT_NAME1=‘ || @archiveFileName || ‘;’   ;

execute @str;

Mark

[cjd]

You might need to add char (39) at both side of  @archiveFileName.

set @str = ‘set temporary option TEMP_EXTRACT_NAME1=‘ ||char (39)|| @archiveFileName || char(39)||‘;’   ;

regards
cjd

[Mark Mumy]

Correct. I forgot that little, yet very important, piece!  Good catch. 

Mark

 

========================

Sent from my mobile device

[Sharon Zzz]

I have tried, however, I got the following error: (using interactive SQL)

Could not execute statement.

Syntax error near '@str' on line 9

SQLCODE=-131, ODBC 3 State="42000"

Line 1, column 1

create procedure extractFCOData (

  @tid integer, @endDate integer, @fname varchar(252))

begin

  declare @str varchar(254);

  select getDate();

  set temporary option TEMP_EXTRACT_NULL_AS_EMPTY = 'ON';

  set temporary option TEMP_EXTRACT_COLUMN_DELIMITER = ':|:';

  set @str = 'set temporary option TEMP_EXTRACT_NAME1=' ||char (39)|| @fname || char(39)||';'   ;

  execute @str;

  select * from FcombinedOrder where tradedateid=@tid;

  select getDate()

end

[Mark Mumy]

I was going from memory. It may be execute immediate. 

Mark

 

========================

Sent from my mobile device

[Sharon Zzz]

Hi Mark,

Thanks, yes, execute immediate works.  And finally the passing of the fileName worked!!!

Thanks again