Thread: I have some questions...

Dear my friends...

My boss requested that my application is equipped with
emoicon (smiley) in the sales report. That's why I
need to know, is it possible to save a small image
into the postgres? with which data type should I
define the column (field)?

I have a currently table, salesreport. Its fields are
: noapp (appointment number, int4), salesid (int4),
custid (customer ID, int4), report (char(150)),
timestamp (timestamp).
I want to add a new column into an old table.
Something in MySQL like this:
"
alter table salesreport add column emoicon after
report
"

How can I do so on postgres?

Thank you very much in advance.



__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail

Prabu,

There are a number of approaches to do this:

1) Store your images on the file system of the server and in the data
base store only the path to it;

2) Create an icon table, and store your icons as BLOBs in it, then
reference them from your reports table.

I'm not an expert on BLOBs, so please search the message archives for
questions/answers regarding BLOBs (Binary Large Objects - there are
plenty of discussions about them), or read the documentation.

Cheers,
Csaba.


On Mon, 2004-08-02 at 13:08, Prabu Subroto wrote:
> Dear my friends...
>
> My boss requested that my application is equipped with
> emoicon (smiley) in the sales report. That's why I
> need to know, is it possible to save a small image
> into the postgres? with which data type should I
> define the column (field)?
>
> I have a currently table, salesreport. Its fields are
> : noapp (appointment number, int4), salesid (int4),
> custid (customer ID, int4), report (char(150)),
> timestamp (timestamp).
> I want to add a new column into an old table.
> Something in MySQL like this:
> "
> alter table salesreport add column emoicon after
> report
> "
>
> How can I do so on postgres?
>
> Thank you very much in advance.
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Mail - You care about security. So do we.
> http://promotions.yahoo.com/new_mail
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

Prabu Subroto wrote:
> My boss requested that my application is equipped with
> emoicon (smiley) in the sales report. That's why I
> need to know, is it possible to save a small image
> into the postgres? with which data type should I
> define the column (field)?

What do you mean by "equipped with emoicon (smiley) in the sales
report"? If you are producing some formatted output from your data
stored in database (HTML, PDF, whatever), I suggest you add the emoticon
on application level. You can store some representation of the emoticon
within the text (like ":-)" or ":-(") and then substitute this text on
application level with the image. If you realy want to store image data
in database (which I am not sure is a very good idea), you can use
binary data type, see
<http://www.postgresql.org/docs/7.4/static/datatype-binary.html>.

> Something in MySQL like this:
> "
> alter table salesreport add column emoicon after
> report
> "

Read the manual on ALTER TABLE command
<http://www.postgresql.org/docs/7.4/static/sql-altertable.html>. You can
do this easily.

--
Michal Taborsky
http://www.taborsky.cz

Noo...noo.... I have to put the emoicon (image) into
database and the applications may use them. But your
advise is enough. Your advise solved my problem.

I still have problem with a new column into the
current table (salesreport table). Because I can not
find in the documentation of postgres how to define a
new column into a current table "AFTER A CERTAIN
COLUMN".
on MySQL, I can do easily like this :
"
alter table salesreport add column emoicon(....) after
report;
"
How can I do this on postgres?

Please tell me.

Thank you very much in advance.
--- Michal Taborsky <michal@taborsky.cz> wrote:

> Prabu Subroto wrote:
> > My boss requested that my application is equipped
> with
> > emoicon (smiley) in the sales report. That's why I
> > need to know, is it possible to save a small image
> > into the postgres? with which data type should I
> > define the column (field)?
>
> What do you mean by "equipped with emoicon (smiley)
> in the sales
> report"? If you are producing some formatted output
> from your data
> stored in database (HTML, PDF, whatever), I suggest
> you add the emoticon
> on application level. You can store some
> representation of the emoticon
> within the text (like ":-)" or ":-(") and then
> substitute this text on
> application level with the image. If you realy want
> to store image data
> in database (which I am not sure is a very good
> idea), you can use
> binary data type, see
>
<http://www.postgresql.org/docs/7.4/static/datatype-binary.html>.
>
> > Something in MySQL like this:
> > "
> > alter table salesreport add column emoicon after
> > report
> > "
>
> Read the manual on ALTER TABLE command
>
<http://www.postgresql.org/docs/7.4/static/sql-altertable.html>.
> You can
> do this easily.
>
> --
> Michal Taborsky
> http://www.taborsky.cz
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
> settings
>




__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail

Prabu Subroto wrote:
> I still have problem with a new column into the
> current table (salesreport table). Because I can not
> find in the documentation of postgres how to define a
> new column into a current table "AFTER A CERTAIN
> COLUMN".
> on MySQL, I can do easily like this :
> "
> alter table salesreport add column emoicon(....) after
> report;
> "
> How can I do this on postgres?

You can't AFAIK. And you don't want to, either. I guess I know why you
ask about this--you are using "SELECT * FROM table" and then are
accessing "the fourth column". This, however, is bad practice and you
are asking for trouble. Don't count on the database engine to return
columns in any particular order, because the order is not guaranteed
(same as is not guaranteed the order of rows, if you don't specify an
ORDER BY clause.)

You should always specify column names, if your application is accessing
columns according to their position in returned result. You have two
possibilities:

a) Specify exact column names and order in query:
SELECT column1, column2 FROM table
Then it is safe to print $row[0] and $row[1] (in PHP array form, just an
example)

b) Use named columns interface:
SELECT * FROM table
Then in application level use $row['column1'] (again PHP example) -- I
think most languages provide this functionality

--
Michal Taborsky
http://www.taborsky.cz