How do I modify .sql files with ruby?

Question

I have a sql file (alice.sql) which looks like this in the editor

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_dg_object_extern_pub_dg_extern_pub_status]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[dg_object_extern_pub] DROP CONSTRAINT FK_dg_object_extern_pub_dg_extern_pub_status GO

if I load that file into irb it looks like this

 f = File.open("alice.sql").readlines

it looks like this :(

=> ["\377\376i\000f\000 \000e\000x\000i\000s\000t\000s\000 \000(\000s\000e\000l\000e\000c\000t\000 \00

I wanted to search and replace some strings in the file but this seems impossible now

any ideas?

It's using UTF-16 or UCS-2 encoding. Look up how localization and encodings work. — geekosaur, May 13 '12 at 17:26
http://stackoverflow.com/questions/1378185/how-to-specify-output-file-encoding-in-ruby (is about output but the same things apply to input). — geekosaur, May 13 '12 at 18:12

score 2 · Accepted Answer · answered May 13 '12 at 18:21

With f = File.open("alice.sql").readlines you open a file handle, but never close it.

You should do:

f = File.open("alice.sql")
lines = f.readlines
f.close

or

File.open("alice.sql"){|f|
  lines = f.readlines
}

With File#readlines you get an array of lines. If you want to do replacements in a string, you shoudl use readinstead:

File.open("alice.sql"){|f|
  content = f.read
}

And last, but not least: Your alice.sql seems to be UTF16, so you must read it as UTF-16:

File.open("alice.sql", :encoding => 'UTF-16BE:UTF-8'){|f|
  content = f.read
}

Now you get \uFEFFif exists (sele... You see the leading BOM? To get rid of it, use:

File.open("alice.sql", :encoding => 'BOM|UTF-16BE:UTF-8'){|f|
  content = f.read
}

(Needs ruby 1.9, maybe the BOM-version requires 1.9.3).

If you need the content outside the block, the variable must be defined outside the block (or you use File#close)

content = nil #define variable, so you get the content after the open-block
File.open("alice.sql", :encoding => 'BOM|UTF-16BE:UTF-8'){|f|
  content = f.read
}
p content

looks funny...seems to be not UTF-16 \uFFFE椀昀攀砀椀猀琀猀 — tabaluga, May 13 '12 at 19:13
Can you try `UTF-16LE`? I haven't checked, if you have big or little endian. — knut, May 13 '12 at 19:18

How do I modify .sql files with ruby?

1 Answers1

Linked