CODEWITHSUNDEEP
ruby-on-railssql-serverdatabasepostgresqlgmaps4rails
Jermaine Subia
Jermaine Subia

Reputation: 796

SQL Server statements imported into Postgres DB now unable to see images - Rails Project

Yesterday I was able to figure out how to use the following query in order to get rid of duplicate results (see SQL Query to delete duplicate values in a 3 tables inner join with two different databases for additional info if necessary):

SELECT 
    "AM-Martin".dbo.CpCore_Site.Number,"AM-Martin".dbo.CpCore_Site.Latitude,"AM-Martin".dbo.CpCore_Site.Longitude,"AM-Martin".dbo.CpSm_Face.RetiredOn,**CAST("AM-Martin_bin".dbo.CpCore_Image.Bytes as Varbinary)**, "AM-Martin".dbo.CpCore_Site.Name, "AM-Martin".dbo.CpCore_Site.Zipcode
FROM
    "AM-Martin".dbo.CpCore_Site

INNER JOIN "AM-Martin".dbo.CpSm_Face on "AM-Martin".dbo.CpSm_Face.SiteId = "AM-Martin".dbo.CpCore_Site.Oid
INNER JOIN "AM-Martin_bin".dbo.CpCore_Image on "AM-Martin".dbo.CpSm_Face.Oid = "AM-Martin_bin".dbo.CpCore_Image.OwnerId

WHERE
    "AM-Martin".dbo.CpSm_Face.RetiredOn LIKE '%9999%'

     AND "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LA%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LC%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%BH%'


     AND "AM-Martin".dbo.CpCore_Site.Latitude > 0.0

GROUP BY "AM-Martin".dbo.CpCore_Site.Number,"AM-Martin".dbo.CpCore_Site.Latitude,"AM-Martin".dbo.CpCore_Site.Longitude,"AM-Martin".dbo.CpSm_Face.RetiredOn,**CAST("AM-Martin_bin".dbo.CpCore_Image.Bytes as Varbinary)**, "AM-Martin".dbo.CpCore_Site.Name, "AM-Martin".dbo.CpCore_Site.Zipcode;

This query allowed me to use Group By on the images column. Afterwards I took the data and put it into SQL Statements and imported them into my Postgres SQL DB. When I click on an image value I am unable to open it the message I get in my program (RazorSQL) is:

Unable to display image

I also checked in the SQL Database Table directly where I ran the query and I am unable to open the images. I suspect it has to do with this line:

**CAST("AM-Martin_bin".dbo.CpCore_Image.Bytes as Varbinary)**

Now the issue is the image values are encoded and I do not know how to decode them when I transfer them over like the SQL statement below:

CREATE TABLE "map" (
    number varchar(32) NOT NULL,
    latitude float NOT NULL,
    longitude float NOT NULL,
    retiredon timestamp NOT NULL,
    image bytea NOT NULL,
    name varchar(256) NOT NULL,
    zipcode varchar(11) NOT NULL
);

INSERT INTO map("number", "latitude", "longitude", "retiredon", "image", "name", "zipcode") VALUES ('BH-0001', 34.059858, -118.376056, '9999-12-31 00:00:00.0', decode('FFD8FFE000104A46494600010100000100010000FFDB0043000604050605','hex'), 'NB La Cienega Blvd FS Olympic Blvd NEC', '90035');

I want to render the images on my website but they appear to be encoded. How do I decode them when I bring them over to postgresql from SQL server in the SQL statement?

Update

I tried to render the images in my rails project without the image column everything works smoothly. As soon as I try to render the image data I get the following error message:

Encoding::UndefinedConversionError

Also see Error Message

Upvotes: 0

Views: 60

Answers (1)

Awraymond
Awraymond

Reputation: 1

You may want to consider translating your byte to hex before migrating the images into the database as strings and then translating them back to byte form before displaying. See this blog for help in the translations.

Alternatively, you could always try hosting your images on AWS and saving the image url as a string in your database.

Upvotes: 0

Related Questions