I'm using SQL Server 2000. What's the command to insert a picture (.bmp, .jp
g.
..) into an Image type column?
Regards,
Pedestrian, Penang.
Message posted via http://www.webservertalk.comi've had luck with BULK INSERT. Here's an example of how to use
it for binary files. The only awkwardness is that
you need to get the file size into the format file before each import,
and how best to automate that will depend on the particular situation.
set nocount on
go
create table DocFiles (
fileNum int identity(1,1),
fName varchar(40),
doc image
)
/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
with the byte length of the .doc file in place of 12755529:
8.0
1
1 SQLIMAGE 0 12755529 "" 1 c1
SQL_Latin1_General_Cp1_CI_AI
*/
-- Create the format file with T-SQL, if desired:
exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
exec master..xp_cmdshell
'echo 1 SQLIMAGE 0 12755529 "" 3 zf
SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
with (
FORMATFILE='e:\txtsrv\doc.fmt'
)
update DocFiles
set fName = 'e:\txtsrv\yourfile.doc'
where fName is NULL
select fileNum, fName as fileName, datalength(doc) as fileSize from DocFiles
GO
-- Steve Kass
-- Drew University
-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||here is another route using textcopy.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"pedestrian via webservertalk.com" <u16758@.uwe> wrote in message
news:61d64a13600ed@.uwe...
> I'm using SQL Server 2000. What's the command to insert a picture (.bmp,
> .jpg.
> ..) into an Image type column?
> Regards,
> --
> Pedestrian, Penang.
> Message posted via http://www.webservertalk.com|||Thanks for replying... Steve Kass... It's looks a bit complex here
I'll figure it out... since I don't see the purpose of the .fmt file...
Thanks for oj too...
Steve Kass wrote:
>i've had luck with BULK INSERT. Here's an example of how to use
>it for binary files. The only awkwardness is that
>you need to get the file size into the format file before each import,
>and how best to automate that will depend on the particular situation.
>set nocount on
>go
>create table DocFiles (
> fileNum int identity(1,1),
> fName varchar(40),
> doc image
> )
>/* Create this three-line tab-separated format file e:\txtsrv\doc.fmt,
> with the byte length of the .doc file in place of 12755529:
>8.0
>1
>1 SQLIMAGE 0 12755529 "" 1 c1
>SQL_Latin1_General_Cp1_CI_AI
>*/
>-- Create the format file with T-SQL, if desired:
>exec master..xp_cmdshell 'echo 8.0> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell 'echo 1 >> e:\txtsrv\doc.fmt', no_output
>exec master..xp_cmdshell
>'echo 1 SQLIMAGE 0 12755529 "" 3 zf
>SQL_Latin1_General_Cp1_CI_AI>> e:\txtsrv\doc.fmt', no_output
>bulk insert DocFiles from 'e:\txtsrv\yourfile.doc'
>with (
> FORMATFILE='e:\txtsrv\doc.fmt'
> )
>update DocFiles
>set fName = 'e:\txtsrv\yourfile.doc'
>where fName is NULL
>select fileNum, fName as fileName, datalength(doc) as fileSize from DocFile
s
>GO
>-- Steve Kass
>-- Drew University
>-- Ref: 45562585-E771-405F-B2C5-7256A4B9870A
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1sql
Showing posts with label image. Show all posts
Showing posts with label image. Show all posts
Friday, March 30, 2012
How to insert NULL when using bcp_moretext?
How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order must al
so be bound with a NULL vardata -- i.e. once you use bcp_moretext for a 'tex
t'/'image', it's really all *following* that must be also sent with bcp_more
text. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with length
0, for that column. Setting length to SQL_NULL_DATA will cause an error late
r, whether you try to call bcp_moretext with length SQL_NULL_DATA, with leng
th 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since the d
ocumentation doesn't cover this at all.
Thanks,
Jim FloodCan I expect some sort of response from Microsoft in this group within two d
ays, as part of the benefit of my MSDN subscription?
-- Jim Flood wrote: --
How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order mu
st also be bound with a NULL vardata -- i.e. once you use bcp_moretext for a
'text'/'image', it's really all *following* that must be also sent with bcp
_moretext. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with le
ngth 0, for that column. Setting length to SQL_NULL_DATA will cause an error
later, whether you try to call bcp_moretext with length SQL_NULL_DATA, with
length 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since
the documentation doesn't cover this at all.
Thanks,
Jim Flood|||I'm checking with our BCP guy.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Jim Flood" <bezspam@.bezspam.cz> wrote in message
news:C95C8F5A-BF58-48F3-A516-AC5980947BDE@.microsoft.com...
> Can I expect some sort of response from Microsoft in this group within two
days, as part of the benefit of my MSDN subscription?
> -- Jim Flood wrote: --
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5
DB-LIB BCP). Setting the param length to zero (bcp_bind or bcp_collen) for
7.0 BCP tells it that you are sending a zero-length param. You still need to
call bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Brannon,
Have you heard back about the BCP problem yet? Is there anything else I need
to do besides post to this group, to get the two-day response from Microsof
t? It's been well over two days.
Thanks,
Jim Flood|||Hi Jim,
I've faced the same problem. I can't insert NULLs using bcp_moretext.
Have you found a solution?
Thanks in advance,
Alberto.
"Jim Flood" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:0BB0853D-430D-4299-88D2-7343C6C024A8@.microsoft.com...
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB
BCP). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP
tells it that you are sending a zero-length param. You still need to call
bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Alberto,
If all of your NULLs are lined up contiguous to the left in the set of colum
ns for a given row, then just avoid bcp_moretext for those columns -- althou
gh, according to the documentation, this isn't supposed to work. But, once y
ou've set bcp_moretext for
a column, going left-to-right, then all other columns to the right will have
to be bcp_moretext'ed as well, and then you can't set any of them to NULL.
So, I have no solution.
Can I please get some kind of response from Microsoft on this problem? I bel
ieve my MSDN subscription should guarantee a two-day response time, and it h
as been *far* more than two days.
Jim Flood
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order must al
so be bound with a NULL vardata -- i.e. once you use bcp_moretext for a 'tex
t'/'image', it's really all *following* that must be also sent with bcp_more
text. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with length
0, for that column. Setting length to SQL_NULL_DATA will cause an error late
r, whether you try to call bcp_moretext with length SQL_NULL_DATA, with leng
th 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since the d
ocumentation doesn't cover this at all.
Thanks,
Jim FloodCan I expect some sort of response from Microsoft in this group within two d
ays, as part of the benefit of my MSDN subscription?
-- Jim Flood wrote: --
How do you insert a NULL value when using bcp_moretext?
If I have a table with two 'text' or 'image' columns, and I am using bcp_mor
etext to insert a very large LOB into one of them, then BCP requires that I
use bcp_moretext for both of them ("use it for one 'text'/'image', then must
use it for all").
(In fact, I've discovered that I can bcp_bind a NULL with a non-NULL vardata
address for earlier 'text'/'image', but once I bcp_bind a NULL vardata, ind
icating my wish to use bcp_moretext, then all following 'text'/'image' colum
ns in ordinal order mu
st also be bound with a NULL vardata -- i.e. once you use bcp_moretext for a
'text'/'image', it's really all *following* that must be also sent with bcp
_moretext. At least that's the behavior I see.)
I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB BC
P). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP te
lls it that you are sending a zero-length param. You still need to call bcp_
moretext later with le
ngth 0, for that column. Setting length to SQL_NULL_DATA will cause an error
later, whether you try to call bcp_moretext with length SQL_NULL_DATA, with
length 0, or try to skip calling bcp_moretext for this column.
I don't believe it's possible to send NULL, via BCP, to a 'text' or 'image'
column, once you've sent 0 or >0 length data to any earlier (in ordinal orde
r) 'text' or 'image' column using bcp_moretext. But, I would like Microsoft
to verify this, since
the documentation doesn't cover this at all.
Thanks,
Jim Flood|||I'm checking with our BCP guy.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Jim Flood" <bezspam@.bezspam.cz> wrote in message
news:C95C8F5A-BF58-48F3-A516-AC5980947BDE@.microsoft.com...
> Can I expect some sort of response from Microsoft in this group within two
days, as part of the benefit of my MSDN subscription?
> -- Jim Flood wrote: --
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5
DB-LIB BCP). Setting the param length to zero (bcp_bind or bcp_collen) for
7.0 BCP tells it that you are sending a zero-length param. You still need to
call bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Brannon,
Have you heard back about the BCP problem yet? Is there anything else I need
to do besides post to this group, to get the two-day response from Microsof
t? It's been well over two days.
Thanks,
Jim Flood|||Hi Jim,
I've faced the same problem. I can't insert NULLs using bcp_moretext.
Have you found a solution?
Thanks in advance,
Alberto.
"Jim Flood" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:0BB0853D-430D-4299-88D2-7343C6C024A8@.microsoft.com...
> How do you insert a NULL value when using bcp_moretext?
> If I have a table with two 'text' or 'image' columns, and I am using
bcp_moretext to insert a very large LOB into one of them, then BCP requires
that I use bcp_moretext for both of them ("use it for one 'text'/'image',
then must use it for all").
> (In fact, I've discovered that I can bcp_bind a NULL with a non-NULL
vardata address for earlier 'text'/'image', but once I bcp_bind a NULL
vardata, indicating my wish to use bcp_moretext, then all following
'text'/'image' columns in ordinal order must also be bound with a NULL
vardata -- i.e. once you use bcp_moretext for a 'text'/'image', it's really
all *following* that must be also sent with bcp_moretext. At least that's
the behavior I see.)
> I'm using the SQL Server 7 ODBC driver extensions (rather than 6.5 DB-LIB
BCP). Setting the param length to zero (bcp_bind or bcp_collen) for 7.0 BCP
tells it that you are sending a zero-length param. You still need to call
bcp_moretext later with length 0, for that column. Setting length to
SQL_NULL_DATA will cause an error later, whether you try to call
bcp_moretext with length SQL_NULL_DATA, with length 0, or try to skip
calling bcp_moretext for this column.
> I don't believe it's possible to send NULL, via BCP, to a 'text' or
'image' column, once you've sent 0 or >0 length data to any earlier (in
ordinal order) 'text' or 'image' column using bcp_moretext. But, I would
like Microsoft to verify this, since the documentation doesn't cover this at
all.
> Thanks,
> Jim Flood|||Hello Alberto,
If all of your NULLs are lined up contiguous to the left in the set of colum
ns for a given row, then just avoid bcp_moretext for those columns -- althou
gh, according to the documentation, this isn't supposed to work. But, once y
ou've set bcp_moretext for
a column, going left-to-right, then all other columns to the right will have
to be bcp_moretext'ed as well, and then you can't set any of them to NULL.
So, I have no solution.
Can I please get some kind of response from Microsoft on this problem? I bel
ieve my MSDN subscription should guarantee a two-day response time, and it h
as been *far* more than two days.
Jim Flood
Wednesday, March 28, 2012
how to insert images...
Complete SQL newbie here.......running SQL 2k.
I created a datatype called 'Picture' of type Image using Enterprise
Manager's Design Table function.
My question, how can I specify what the image is for a given table
row/entry? For text data types I can just type in what I want the
value to be.
Most answers I've seen talk about running scripts to do inserts.
There's got to be a drag 'n drop or File/Open type way of doing this.
I mean I remember watching people do this with a database app on a
NextStation way back when (drag n drop).....you'd think the mighty
SQL Server 2000 would have similiar abilities.
Any help appreciated.
J.Sorry, SQL Server isn't a WYSIWYG editor, and there is no drag 'n' drop
interface.
In most situations, it's probably not a good idea to store an image in your
database anyway... See for more info:
http://www.aspfaq.com/show.asp?id=2149
"James" <lee.james@.spartan.ab.ca> wrote in message
news:17084052.0403121132.1aa413a7@.posting.google.com...
> Complete SQL newbie here.......running SQL 2k.
> I created a datatype called 'Picture' of type Image using Enterprise
> Manager's Design Table function.
> My question, how can I specify what the image is for a given table
> row/entry? For text data types I can just type in what I want the
> value to be.
> Most answers I've seen talk about running scripts to do inserts.
> There's got to be a drag 'n drop or File/Open type way of doing this.
> I mean I remember watching people do this with a database app on a
> NextStation way back when (drag n drop).....you'd think the mighty
> SQL Server 2000 would have similiar abilities.
> Any help appreciated.
> J.|||Hi James,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if our community member's information is helpful
to your question. For any more question, please post your message here and
we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql
I created a datatype called 'Picture' of type Image using Enterprise
Manager's Design Table function.
My question, how can I specify what the image is for a given table
row/entry? For text data types I can just type in what I want the
value to be.
Most answers I've seen talk about running scripts to do inserts.
There's got to be a drag 'n drop or File/Open type way of doing this.
I mean I remember watching people do this with a database app on a
NextStation way back when (drag n drop).....you'd think the mighty
SQL Server 2000 would have similiar abilities.
Any help appreciated.
J.Sorry, SQL Server isn't a WYSIWYG editor, and there is no drag 'n' drop
interface.
In most situations, it's probably not a good idea to store an image in your
database anyway... See for more info:
http://www.aspfaq.com/show.asp?id=2149
"James" <lee.james@.spartan.ab.ca> wrote in message
news:17084052.0403121132.1aa413a7@.posting.google.com...
> Complete SQL newbie here.......running SQL 2k.
> I created a datatype called 'Picture' of type Image using Enterprise
> Manager's Design Table function.
> My question, how can I specify what the image is for a given table
> row/entry? For text data types I can just type in what I want the
> value to be.
> Most answers I've seen talk about running scripts to do inserts.
> There's got to be a drag 'n drop or File/Open type way of doing this.
> I mean I remember watching people do this with a database app on a
> NextStation way back when (drag n drop).....you'd think the mighty
> SQL Server 2000 would have similiar abilities.
> Any help appreciated.
> J.|||Hi James,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if our community member's information is helpful
to your question. For any more question, please post your message here and
we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.sql
how to insert image
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
RajeshHi Rajesh,
I am presently working on storing and retrieving image on SQL 2000 using VB6.
I would be very much grateful if you could pls send me codes u have that can hlp me.
Thanks a lot
prakash
NB my email is prak_07@.servihoo.com|||Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
Hi There,
I would also be interested to have this stored procedure.
I would be greatful if you send it to me
Thanks
gunnar.westerberg@.iftech.se|||Rajesh I would also like to have the code and the SP for using the same in my project. please send me a copy also. My mail Id is dineshyadav@.yahoo.com. Thanks in Advance
Regards
Dinesh|||Can you post the Code ?
BTW what are you store in the DB ? the path to the Image ?
Thanks,
Eyal|||Hi Rajesh,
Does your SP supports storing upto 100 MB of data??
Please send me the code ...It would be of great help for me.
I am waiting for a long time for such help. I have my own one ...but it fails for 100 MB. Please send urs.
My E-mail id is avneesh@.newgen.co.in
Thanks in advance and hope u send it ASAP.
Avneesh|||Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
Hai ...
Please send me your code too. Cause i need it for my next project.
thx
ps : My email desy_g@.hotmail.com|||hello rajesh
I want to store word documents and pictures in SQL server 2000.is it possible with SQL server2000.if please send me your code in
subyphilip@.indiatimes.com|||I have seen similar thread in other forums with no results for getting SP from the originator.
You can use REDADTEXT/WRITETEXT/UPDATETEXT to dealwith text/image datatypes and refer to books online for more information.
Also may check under Umachander's (http://www.umachandar.com/) homepage for the code.|||Please send me the code
My Email-id is mdabidullah@.indiatimes.com
Thanks in advance and hope u send it ASAP.
Abid
Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh|||This looks like a hot thread. I also have a similar problem - inserting into image columns from a script. Im interested in that code too. Cheers!|||I bet you will not get reply/mail from the originator about the code.
As referred you browse thru the Umachander's page and also search under Planet source code (http://www.planet-source-code.com) for similar issue, this will save your time and gives you more resources.|||Thanks for that link. Once i come up with the soln to ma kinda of problem, i will share it with you forum members.|||Appreciate your spirit.|||Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
I too would be interested in the code. Thanks
victor@.keilman.com|||what's up with this "know-how"?? hasn't anybody tried it at all?
create table tblImages (ImageID int identity(1,1) not null, Contents image null)
go
create procedure sp_AddImage (
@.image image )
as
declare @.ImageID int, @.txtptr varbinary(16)
begin tran
insert tblImages (Contents) values (null)
set @.ImageID = scope_identity()
--this part is to ensure that NULL is written to the image field
update tblImages set Contents = null where ImageID = @.ImageID
select @.txtptr = textptr(Contents)
from tblImages
where ImageID = @.ImageID
writetext tblImages.Contents @.txtptr @.image
if @.@.error != 0 begin
raiserror ('Failed to write new image to the table!', 15, 1)
rollback tran
return (1)
end
commit tran
select RetVal = @.ImageID
return (0)
go
exec sp_AddImage 0x00000fffff|||ms_sql_dba, just think of all the people you have made happy today!
A thousand smilies for you: POWER(POWER( :) :) :) :) :) :) :) :) :) :) , 10), 10)|||ms_sql_dba: I like the code. I have never worked with image/text data before, so I don't know how long it would have taken me to get that insert null trick to recover the identity value. Nice job.|||Please send the code to me
lord_n@.msn.com|||Hi,
I have never worked with images in SQL. Your code works like butter. Beautiful. But I have a question. How would I use it? What would an application pass to me(image, image reference or path to the image?)? How would I give the image back to the application?
Thanks.|||i am interested in this script.
i am happy if you send to me...thanks
my e-mail is hckot@.yahoo.com.hk|||hi,
I am Manoj from INDIA.
I have installed Microsoft SQL SERVER 2000 Desktop Edition.
I want to see all the databases created in SQL SERVER in the VISUAL BASIC. If it were ORACLE we can query by "select * from tab" from VB and populate the listbox with the resulset. But in SQLSERVER the query is not working. Ehat should i do?
Awaiting for ur reply..
-Manoj|||Do you need all the databases or all the tables
select * from tab in Oracle would give you all the tables .. not the databases.
For selecting all tables from a particular database in sql server
select * from sysobjects where type = 'U'|||Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
Rajesh!
I would like to look at your stored procedure. Can you send it to me I would be very greatful.
papd_sweden@.hotmail.com
Thanks in advance
//Moon|||Dosen't anyody read the entire post ....
Originally posted by ms_sql_dba
what's up with this "know-how"?? hasn't anybody tried it at all?
code:------------------------
create table tblImages (ImageID int identity(1,1) not null, Contents image null)
go
create procedure sp_AddImage (
@.image image )
as
declare @.ImageID int, @.txtptr varbinary(16)
begin tran
insert tblImages (Contents) values (null)
set @.ImageID = scope_identity()
--this part is to ensure that NULL is written to the image field
update tblImages set Contents = null where ImageID = @.ImageID
select @.txtptr = textptr(Contents)
from tblImages
where ImageID = @.ImageID
writetext tblImages.Contents @.txtptr @.image
if @.@.error != 0 begin
raiserror ('Failed to write new image to the table!', 15, 1)
rollback tran
return (1)
end
commit tran
select RetVal = @.ImageID
return (0)
go
exec sp_AddImage 0x00000fffff
------------------------|||Hello, people! This thread is 2 YEARS OLD! And the guy never posted again...
Everything on this thread should be deleted except MS_SQL_DBA's code.|||Where is the moderator when you need him the most :)|||can you send me your proc that store images to ..
does your code do this..
get all rows in a tables.. in a stroc proc..and save them back to a new table and one table is a image field.. ?
beaulieu1@.hotmail.com
thanx... been searching for tree days now..on this
Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh|||Wow! This thread still hasn't been read properly. ;)|||Do you think some ppl are just dumb? or someone is using diff logins just for the fun of it and posting the same request over and over again......|||Hey Patrick ... what does SQL Padawan mean|||Originally posted by Enigma
Hey Patrick ... what does SQL Padawan mean
Dear SQL Apostle,
Have you seen Star Wars? Before you become a Jedi, u'r a Padawan...
:)
This thread is really going out from it original topic....|||Hmm .. Star Wars was never my kinda science fiction ... unbelievable (pun intended) ... Gattaca is more like it ...
So ... when you get your fourth star ..do you become a SQL Jedi Knight ;)
BTW .. this thread was never on topic anyway.|||Kinda already taken...
http://www.sqlteam.com/forums/members.asp|||Gattaca gets four stars from me.
"I never saved anything for the swim back."|||Originally posted by blindman
Gattaca gets four stars from me.
"I never saved anything for the swim back."
My favourite quote too ...|||is this code still available|||Dawn Of The Dead Post|||The code is available halfway through this thread. I believe ms_sql_dba was good enough to put it in here for everyone.
So why can't this thread get sent to the marketplace?|||Send it to the trash bin.|||soz of the dumb question before...didnt actually read the whole thread SOZ...listen what exactly does this bit of code do?
PLEASE HELP i really need to store images on my database....
THANKS|||It's best not to store images in the database, rather, store only a
reference to the image in the database, ie, a text field
containing "c:\wwwroot\images\userphotos\user1\user1.jpg"
If you are having people upload files to your site, you can use asp's
filesystemobject to dynamically create such a file structure.
Follow this http://www.sqlteam.com/item.asp?ItemID=986 link for more information to store images to database.|||I have a question: How can I copy the binary value from tableA.columnA to tableB.columnB? Both of them are image fields. I have tried using updatetext but kept giving the error 'NULL textptr (text, ntext, or image pointer) passed to UpdateText function'. Does you have any samples or code snippets? Thanks!
-------
Originally posted by Satya
I have seen similar thread in other forums with no results for getting SP from the originator.
You can use REDADTEXT/WRITETEXT/UPDATETEXT to dealwith text/image datatypes and refer to books online for more information.
Also may check under Umachander's (http://www.umachandar.com/) homepage for the code.|||Originally posted by Patrick Chua
Do you think some ppl are just dumb? or someone is using diff logins just for the fun of it and posting the same request over and over again...... good questions, hey?!|||Originally posted by stephanie_lim
I have a question: How can I copy the binary value from tableA.columnA to tableB.columnB? Both of them are image fields. I have tried using updatetext but kept giving the error 'NULL textptr (text, ntext, or image pointer) passed to UpdateText function'. Does you have any samples or code snippets? Thanks!
------- you have to update your text/image field to null before retrieving the txtptr.|||why not using 3rd tool to help u.
I suggest u trying borland datapump(an extended tool in delphi install package).
Last ,don't forget u can write a program(vb,delphi,java,etc) to do it by yourself .
It's very easy.|||Originally posted by Satya
It's best not to store images in the database, rather, store only a
reference to the image in the database, ie, a text field
containing "c:\wwwroot\images\userphotos\user1\user1.jpg"
If you are having people upload files to your site, you can use asp's
filesystemobject to dynamically create such a file structure.
Follow this http://www.sqlteam.com/item.asp?ItemID=986 link for more information to store images to database. satya,
you sound like it's been an industry standard to store the actual images as file system objects, rather than in the database. it's actually a matter of trust you have with one over the other. it also depends on the app itself, - surely you don't plan to search for the contents of the file even if it is of type text if it is stored in the database. i successfully implemented storing hard-copy documents in the database that get displayed whenever a hard-copy is requested by the user. i also stored report definitions as well as report outputs into image fields so that they can be viewed using the same format/the same content as when they originally produced. i just don't see a justification for this dogmatic approach. do you?|||ON the basis of performance and managebility I recommended (industry standard as you mentioned) to use the file paths instead of storing them on the database.
If your approach are fetching good results without any issues... say no issues with the database consistency then follow it. As far as my experience is concerned I tend to suggest to use file paths rather than storing images directly on the database.:)|||could you be so kind to refer me to a white paper or a publication where this "industry standard" is being discussed? i'd like to at least conceptually imagine the reasoning for this "standard" to even exist.
thanks|||[http://www.sql-server-performance.com/q&a17.asp] for instance.|||even though it's not a white paper or publication, but rather a personal opinion of an author, i'd like to comment on it:
"...storing BLOB objects in SQL Server is cumbersome..." - only if you are not sure how to do it or the method to do it is not the most efficient
"The most commonly accepted way of managing images for a website is not to store the images in the database, but only to store the URL in the database, and to store the images on a file share that has been made a virtual folder of your web server." - please note that (while still being the personal opinion of the author) the paragraph above states that it's "the most commonly accepted". i'd question that because the shops that i worked for and know of accepted the "more cumbursome" way of dealing with images and documents, and that's how they managed to be more successful than the others. you'd ask me what happened to the others? one example is when system administrator decided to reorganize the file structure which resulted in web-site being out of commission until the "more cumbursome" way was introduced and resolved the issue...forever ;)|||I need the code. Thank you.
My email: kim78@.streamyx.com|||Is this thread ever gonna die down ?|||The Code:
Dim ReferTread as this.tread
Dim Understand boolean
Answer=ReferTread.prevpage
if Understand=1
Response.write("You figured it out!")
elseif Understand=0
Response.write("Forget programming")
end if|||OK, ms_sql_dba's code was neat, but there is ADO.Stream object that pretty much does the same.
The alternative to it is the following (please do not ask to repost!!!) which I left unmodified:
-- Ensure QUOTED_IDENTIFIER setting is OFF
set quoted_identifier off
go
-- Drop all participating objects
if object_id('dbo.sp_OA_CreateFile') is not null
drop procedure dbo.sp_OA_CreateFile
go
if object_id('dbo.sp_OA_WriteLineToFile') is not null
drop procedure dbo.sp_OA_WriteLineToFile
go
if object_id('dbo.sp_OA_CloseFile') is not null
drop procedure dbo.sp_OA_CloseFile
go
if object_id('dbo.sp_StoreImage2Table') is not null
drop procedure dbo.sp_StoreImage2Table
go
if object_id('dbo.tblDocumentSubCategories') is not null
drop table dbo.tblDocumentSubCategories
go
if object_id('dbo.tblDocumentCategories') is not null
drop table dbo.tblDocumentCategories
go
if object_id('dbo.tblDocumentTypes') is not null
drop table dbo.tblDocumentTypes
go
if object_id('dbo.tblImages_tmp') is not null
drop table dbo.tblImages_tmp
go
if object_id('dbo.fn_DateFromCharacter') is not null
drop function dbo.fn_DateFromCharacter
go
if object_id('dbo.tblDocuments') is not null
drop table dbo.tblDocuments
go
-- Build the database structure to store binary data
create table dbo.tblDocumentTypes (
TypeID int identity(1,1) not null primary key clustered,
[Description] nvarchar(450) not null )
go
create table dbo.tblDocumentCategories (
CategoryID int identity(1,1) not null primary key clustered,
[Description] nvarchar(450) not null )
go
create table dbo.tblDocumentSubCategories (
SubCategoryID int identity(1,1) not null primary key clustered,
CategoryID int not null,
[Description] nvarchar(450) not null )
go
alter table dbo.tblDocumentSubCategories
add constraint FK_DocumentCategories foreign key (CategoryID)
references dbo.tblDocumentCategories (CategoryID)
go
alter table dbo.tblDocumentCategories
add constraint UC_UniqueDocumentCategory unique ([Description])
go
create table dbo.tblImages_tmp (fImage image null)
go
create table dbo.tblDocuments (
DocumentID int identity(1,1) not null primary key nonclustered,
TypeID int not null,
CategoryID int not null,
SubCategoryID int not null,
DocumentName nvarchar(2000) not null,
[Description] nvarchar(255) not null,
DocumentSize int not null,
AlternateName nchar(12) not null,
CreattionDate datetime not null,
LastModified datetime not null,
LastAccessed datetime not null,
Attributes varchar(10) not null,
DocumentImage image null)
go
create function dbo.fn_DateFromCharacter (
@.DatePart char(8),
@.TimePart char(6) ) returns datetime
as begin
declare @.Date datetime, @.MonthDay char(4), @.Year char(4), @.Month char(2), @.Day char(2)
declare @.Hour char(2), @.Minute char(2), @.Second char(2)
set @.MonthDay = reverse(cast(reverse(@.DatePart) as char(4)))
set @.Month = cast(@.MonthDay as char(2))
set @.Day = reverse(cast(reverse(@.MonthDay) as char(2)))
set @.Year = cast(@.DatePart as char(4))
set @.TimePart = right('000000'+@.TimePart, 6)
set @.Hour = cast(@.TimePart as char(2))
set @.Second = reverse(cast(reverse(@.TimePart) as char(2)))
set @.Minute = cast(reverse(cast(reverse(@.TimePart) as char(4))) as char(2))
return cast(@.Month + '/' + @.Day + '/' + @.Year + ' ' + @.Hour + ':' + @.Minute + ':' + @.Second as datetime)
end
go
create procedure dbo.sp_OA_CreateFile (
@.FileName nvarchar(2000) = null,
@.FileID int output,
@.fs int output)
as
declare @.result int
exec @.result = sp_OACreate 'Scripting.FileSystemObject', @.fs output
if @.result != 0 begin
raiserror ('Failed to create Scripting.FileSystemObject!', 15, 1)
return (1)
end
exec @.result = sp_OAMethod @.fs, 'CreateTextFile', @.FileID output, @.FileName, 1
if @.result != 0 begin
raiserror ('Failed to create/overwrite specified file!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_OA_WriteLineToFile (
@.FileID int,
@.Line varchar(8000) = null)
as
declare @.result int
exec @.result = sp_OAMethod @.FileID, 'WriteLine', null, @.Line
if @.result != 0 begin
raiserror ('Failed to write specified line to file!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_OA_CloseFile (
@.FileID int,
@.fs int )
as
declare @.result int
exec @.result = sp_OADestroy @.FileID
if @.result != 0 begin
raiserror ('Failed to close file!', 15, 1)
return (1)
end
exec @.result = sp_OADestroy @.fs
if @.result != 0 begin
raiserror ('Failed to close Scripting.FileSystemObject!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_StoreImage2Table (
@.FileName nvarchar(4000) = null,
@.FileDescription varchar (255) = null,
@.TypeID int = 0,
@.CategoryID int = 0,
@.SubCategoryID int = 0)
as
set nocount on
declare @.FileSize int,
@.Path nvarchar(4000),
@.FmtFile nvarchar(4000),
@.cmd varchar(8000),
@.DocumentID int,
@.ptr binary(16),
@.ptr_tmp binary(16),
@.fs int,
@.FileID int,
@.error int
if @.FileName is null begin
raiserror ('File name must be specified!', 15, 1)
return (1)
end
if @.FileDescription is null begin
raiserror ('File description must be specified!', 15, 1)
return (1)
end
if charindex('\', @.FileName) = 0 begin
set @.cmd = 'File name <' + upper(@.FileName) + '> is of invalid name!'
raiserror (@.cmd, 15, 1)
return (1)
end
if @.TypeID = 0 begin --Type of document has noot been specified
raiserror ('Document type is not specified!', 15, 1)
return (1)
end
if @.CategoryID = 0 begin --Document category has noot been specified
raiserror ('Document category is not specified!', 15, 1)
return (1)
end
if @.SubCategoryID = 0 begin --Document Sub-category has noot been specified
raiserror ('Document sub-category is not specified!', 15, 1)
return (1)
end
set @.Path = reverse(substring(reverse(@.FileName), charindex('\', reverse(@.FileName), 1), 4000))
set @.FmtFile = @.Path + 'fmt.fmt'
begin tran
create table #FileInfo (
AlternateName nchar(12) null,
FileSize int null,
CreationDate char(8) null,
CreationTime char(6) null,
LastWrittenDate char(8) null,
LastWrittenTime char(6) null,
LastAccessedDate char(8) null,
LastAccessedTime char(6) null,
Attributes varchar(10) null )
create table #output ([output] varchar(8000) null)
commit tran
insert #FileInfo exec master.dbo.xp_getfiledetails @.FileName
select @.FileSize = FileSize from #FileInfo
if @.FileSize is null begin
set @.cmd = 'Specified file <' + upper(@.FileName) + '> does not exist!'
raiserror (@.cmd, 15, 1)
return (1)
end
-- Create format file
exec dbo.sp_OA_CreateFile @.FmtFile, @.FileID output, @.fs output
exec dbo.sp_OA_WriteLineToFile @.FileID, '8.0'
exec dbo.sp_OA_WriteLineToFile @.FileID, '1'
set @.cmd = "1 " +
space(8) + "SQLIMAGE" + space(8) + "0" + space(8) +
cast(@.FileSize as varchar(25)) + space(8) +
char(34) + char(34) + space(8) + "1" + space(8) +
"fImage" + space(8) + char(34) + char(34)
exec dbo.sp_OA_WriteLineToFile @.FileID, @.cmd
exec dbo.sp_OA_CloseFile @.FileID, @.fs
-- Store binary data to temporary location in the database
if exists (select 1 from dbo.tblImages_tmp (nolock)) delete dbo.tblImages_tmp
set @.cmd = "bulk insert " + db_name() + ".dbo.tblImages_tmp from '" + @.FileName + "' " +
"with (formatfile = '" + @.FmtFile + "')"
exec (@.cmd)
set @.cmd = "exec master.dbo.xp_cmdshell 'del " + @.FmtFile + "'"
insert #output exec (@.Cmd)
if not exists (select 1 from dbo.tblImages_tmp) begin
set @.cmd = 'Error occurred while storing file <' + upper(@.FileName) + '> into a temporary table!'
raiserror (@.cmd, 15, 1)
return (1)
end
select @.DocumentID = DocumentID from dbo.tblDocuments d (nolock)
inner join #FileInfo f on d.AlternateName = f.AlternateName and d.DocumentName = @.FileName
-- Insert new tblDocuments record if it doesn't exist
if @.DocumentID is null begin
begin tran
insert dbo.tblDocuments (
TypeID,
CategoryID,
SubCategoryID,
DocumentName ,
[Description] ,
DocumentSize ,
AlternateName ,
CreattionDate ,
LastModified ,
LastAccessed ,
Attributes )
select @.TypeID, @.CategoryID, @.SubCategoryID,
@.FileName, @.FileDescription, @.FileSize, AlternateName,
dbo.fn_DateFromCharacter(CreationDate, CreationTime),
dbo.fn_DateFromCharacter(LastWrittenDate, LastWrittenTime),
dbo.fn_DateFromCharacter(LastAccessedDate, LastAccessedTime),
Attributes from #FileInfo
select @.DocumentID = scope_identity(), @.error = @.@.error
if @.error != 0 begin
raiserror ('Failed to initialize target table!', 15, 1)
rollback tran
return (1)
end
commit tran
end
-- Initialize IMAGE field with NULL
begin tran
update dbo.tblDocuments set DocumentImage = null where DocumentID = @.DocumentID
if @.@.error != 0 begin
raiserror ('Failed to initialize target field!', 15, 1)
rollback tran
return (1)
end
commit tran
-- Use UPDATETEXT to transfer the image from temporary table
select @.ptr = textptr(DocumentImage) from dbo.tblDocuments where DocumentID = @.DocumentID
select @.ptr_tmp = textptr(fImage) from dbo.tblImages_tmp
begin tran
updatetext tblDocuments.DocumentImage @.ptr 0 null tblImages_tmp.fImage @.ptr_tmp
if @.@.error != 0 begin
raiserror ('Failed to store document into target table!', 15, 1)
rollback tran
return (1)
end
-- Clear the temporary table
delete dbo.tblImages_tmp
if @.@.error != 0 begin
raiserror ('Failed to clear temporary table!', 15, 1)
rollback tran
return (1)
end
commit tran
return (0)
go|||Microsoft says it's not wise http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproasp/html/binarydata.asp
Here is some samplecode to do it
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
------
--TEXTCOPY IN
------
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
------
--TEXTCOPY OUT
------
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go|||Please read from the link that you posted and look for the authors (wink-wink, it's not Microsoft!)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproasp/html/professionalactiveserverpages.asp|||I see. I guess I assume if it's on MSDN it at least passed some review. A few reasons I can think of not to do it are DB performance in general, complexity of the code and apps to support it, and not being able to do incremental backups on a DB like you can for file system documents. It's an often debated topic. We have had at least 5 database backed commercial document management systems and none of them stored documents in the DB. Does MS's terraserver store images in the DB? (I don't know, just wondering.)|||I think that's the reason why it's called terra server, there is nothing else in it, just images.
Yukon will still support TEXT/NTEXT and IMAGE datatypes, but will introduce VARCHAR/NVARCHAR(MAX) as well.|||I am also working on saving IMAGE to MSSQL. I am using JAVA as language.
Please send me code at kuldips@.bsharp.com|||I would recommend to re-read the previous posts to eliminate further questions, because all the answers that can be possibly given to the questions...have been posted...|||THIS THREAD IS DEAD!
DEAD, DEAD, DEAD!
IT HAS BEEN DEAD FOR A YEAR. NOBODY IS GOING TO SEND YOU ANY HELPFUL SOFTWARE. NOBODY IS GOING TO GIVE YOU ANY USEFUL ADVICE.
By posting to this thread and expecting any sort of helpful reply, you hearby declare yourself a moron of insufficient intelligence to browse the internet. For your own protection from phishers, overseas pharmaceutical marketers, and deposed Nigerian despots, please disconnect your 56K modem NOW!|||Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
Plz send the code to me too
thanks
my mail is :
yosif4444@.gmail.com|||yosif4444, you have hereby declared yourself a moron of the highest degree. A poster of insufficient intelligence to browse the internet. Please remove your coffee cup from your CD Drive tray, wipe the white-out off of your screen, unplug your computer, and go outside and play in the driveway.|||This sounds very useful.
Please message me with the code.
Many thanks.
DbaJames.|||This thread collects idiots like flypaper collects flies. DbaJames, you seem to have something stuck to your shoe...|||OK, I'm locking this thread to prevent others from displaying their ignorance.
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
RajeshHi Rajesh,
I am presently working on storing and retrieving image on SQL 2000 using VB6.
I would be very much grateful if you could pls send me codes u have that can hlp me.
Thanks a lot
prakash
NB my email is prak_07@.servihoo.com|||Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
Hi There,
I would also be interested to have this stored procedure.
I would be greatful if you send it to me
Thanks
gunnar.westerberg@.iftech.se|||Rajesh I would also like to have the code and the SP for using the same in my project. please send me a copy also. My mail Id is dineshyadav@.yahoo.com. Thanks in Advance
Regards
Dinesh|||Can you post the Code ?
BTW what are you store in the DB ? the path to the Image ?
Thanks,
Eyal|||Hi Rajesh,
Does your SP supports storing upto 100 MB of data??
Please send me the code ...It would be of great help for me.
I am waiting for a long time for such help. I have my own one ...but it fails for 100 MB. Please send urs.
My E-mail id is avneesh@.newgen.co.in
Thanks in advance and hope u send it ASAP.
Avneesh|||Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
Hai ...
Please send me your code too. Cause i need it for my next project.
thx
ps : My email desy_g@.hotmail.com|||hello rajesh
I want to store word documents and pictures in SQL server 2000.is it possible with SQL server2000.if please send me your code in
subyphilip@.indiatimes.com|||I have seen similar thread in other forums with no results for getting SP from the originator.
You can use REDADTEXT/WRITETEXT/UPDATETEXT to dealwith text/image datatypes and refer to books online for more information.
Also may check under Umachander's (http://www.umachandar.com/) homepage for the code.|||Please send me the code
My Email-id is mdabidullah@.indiatimes.com
Thanks in advance and hope u send it ASAP.
Abid
Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh|||This looks like a hot thread. I also have a similar problem - inserting into image columns from a script. Im interested in that code too. Cheers!|||I bet you will not get reply/mail from the originator about the code.
As referred you browse thru the Umachander's page and also search under Planet source code (http://www.planet-source-code.com) for similar issue, this will save your time and gives you more resources.|||Thanks for that link. Once i come up with the soln to ma kinda of problem, i will share it with you forum members.|||Appreciate your spirit.|||Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
I too would be interested in the code. Thanks
victor@.keilman.com|||what's up with this "know-how"?? hasn't anybody tried it at all?
create table tblImages (ImageID int identity(1,1) not null, Contents image null)
go
create procedure sp_AddImage (
@.image image )
as
declare @.ImageID int, @.txtptr varbinary(16)
begin tran
insert tblImages (Contents) values (null)
set @.ImageID = scope_identity()
--this part is to ensure that NULL is written to the image field
update tblImages set Contents = null where ImageID = @.ImageID
select @.txtptr = textptr(Contents)
from tblImages
where ImageID = @.ImageID
writetext tblImages.Contents @.txtptr @.image
if @.@.error != 0 begin
raiserror ('Failed to write new image to the table!', 15, 1)
rollback tran
return (1)
end
commit tran
select RetVal = @.ImageID
return (0)
go
exec sp_AddImage 0x00000fffff|||ms_sql_dba, just think of all the people you have made happy today!
A thousand smilies for you: POWER(POWER( :) :) :) :) :) :) :) :) :) :) , 10), 10)|||ms_sql_dba: I like the code. I have never worked with image/text data before, so I don't know how long it would have taken me to get that insert null trick to recover the identity value. Nice job.|||Please send the code to me
lord_n@.msn.com|||Hi,
I have never worked with images in SQL. Your code works like butter. Beautiful. But I have a question. How would I use it? What would an application pass to me(image, image reference or path to the image?)? How would I give the image back to the application?
Thanks.|||i am interested in this script.
i am happy if you send to me...thanks
my e-mail is hckot@.yahoo.com.hk|||hi,
I am Manoj from INDIA.
I have installed Microsoft SQL SERVER 2000 Desktop Edition.
I want to see all the databases created in SQL SERVER in the VISUAL BASIC. If it were ORACLE we can query by "select * from tab" from VB and populate the listbox with the resulset. But in SQLSERVER the query is not working. Ehat should i do?
Awaiting for ur reply..
-Manoj|||Do you need all the databases or all the tables
select * from tab in Oracle would give you all the tables .. not the databases.
For selecting all tables from a particular database in sql server
select * from sysobjects where type = 'U'|||Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
Rajesh!
I would like to look at your stored procedure. Can you send it to me I would be very greatful.
papd_sweden@.hotmail.com
Thanks in advance
//Moon|||Dosen't anyody read the entire post ....
Originally posted by ms_sql_dba
what's up with this "know-how"?? hasn't anybody tried it at all?
code:------------------------
create table tblImages (ImageID int identity(1,1) not null, Contents image null)
go
create procedure sp_AddImage (
@.image image )
as
declare @.ImageID int, @.txtptr varbinary(16)
begin tran
insert tblImages (Contents) values (null)
set @.ImageID = scope_identity()
--this part is to ensure that NULL is written to the image field
update tblImages set Contents = null where ImageID = @.ImageID
select @.txtptr = textptr(Contents)
from tblImages
where ImageID = @.ImageID
writetext tblImages.Contents @.txtptr @.image
if @.@.error != 0 begin
raiserror ('Failed to write new image to the table!', 15, 1)
rollback tran
return (1)
end
commit tran
select RetVal = @.ImageID
return (0)
go
exec sp_AddImage 0x00000fffff
------------------------|||Hello, people! This thread is 2 YEARS OLD! And the guy never posted again...
Everything on this thread should be deleted except MS_SQL_DBA's code.|||Where is the moderator when you need him the most :)|||can you send me your proc that store images to ..
does your code do this..
get all rows in a tables.. in a stroc proc..and save them back to a new table and one table is a image field.. ?
beaulieu1@.hotmail.com
thanx... been searching for tree days now..on this
Originally posted by rajeshm
Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh|||Wow! This thread still hasn't been read properly. ;)|||Do you think some ppl are just dumb? or someone is using diff logins just for the fun of it and posting the same request over and over again......|||Hey Patrick ... what does SQL Padawan mean|||Originally posted by Enigma
Hey Patrick ... what does SQL Padawan mean
Dear SQL Apostle,
Have you seen Star Wars? Before you become a Jedi, u'r a Padawan...
:)
This thread is really going out from it original topic....|||Hmm .. Star Wars was never my kinda science fiction ... unbelievable (pun intended) ... Gattaca is more like it ...
So ... when you get your fourth star ..do you become a SQL Jedi Knight ;)
BTW .. this thread was never on topic anyway.|||Kinda already taken...
http://www.sqlteam.com/forums/members.asp|||Gattaca gets four stars from me.
"I never saved anything for the swim back."|||Originally posted by blindman
Gattaca gets four stars from me.
"I never saved anything for the swim back."
My favourite quote too ...|||is this code still available|||Dawn Of The Dead Post|||The code is available halfway through this thread. I believe ms_sql_dba was good enough to put it in here for everyone.
So why can't this thread get sent to the marketplace?|||Send it to the trash bin.|||soz of the dumb question before...didnt actually read the whole thread SOZ...listen what exactly does this bit of code do?
PLEASE HELP i really need to store images on my database....
THANKS|||It's best not to store images in the database, rather, store only a
reference to the image in the database, ie, a text field
containing "c:\wwwroot\images\userphotos\user1\user1.jpg"
If you are having people upload files to your site, you can use asp's
filesystemobject to dynamically create such a file structure.
Follow this http://www.sqlteam.com/item.asp?ItemID=986 link for more information to store images to database.|||I have a question: How can I copy the binary value from tableA.columnA to tableB.columnB? Both of them are image fields. I have tried using updatetext but kept giving the error 'NULL textptr (text, ntext, or image pointer) passed to UpdateText function'. Does you have any samples or code snippets? Thanks!
-------
Originally posted by Satya
I have seen similar thread in other forums with no results for getting SP from the originator.
You can use REDADTEXT/WRITETEXT/UPDATETEXT to dealwith text/image datatypes and refer to books online for more information.
Also may check under Umachander's (http://www.umachandar.com/) homepage for the code.|||Originally posted by Patrick Chua
Do you think some ppl are just dumb? or someone is using diff logins just for the fun of it and posting the same request over and over again...... good questions, hey?!|||Originally posted by stephanie_lim
I have a question: How can I copy the binary value from tableA.columnA to tableB.columnB? Both of them are image fields. I have tried using updatetext but kept giving the error 'NULL textptr (text, ntext, or image pointer) passed to UpdateText function'. Does you have any samples or code snippets? Thanks!
------- you have to update your text/image field to null before retrieving the txtptr.|||why not using 3rd tool to help u.
I suggest u trying borland datapump(an extended tool in delphi install package).
Last ,don't forget u can write a program(vb,delphi,java,etc) to do it by yourself .
It's very easy.|||Originally posted by Satya
It's best not to store images in the database, rather, store only a
reference to the image in the database, ie, a text field
containing "c:\wwwroot\images\userphotos\user1\user1.jpg"
If you are having people upload files to your site, you can use asp's
filesystemobject to dynamically create such a file structure.
Follow this http://www.sqlteam.com/item.asp?ItemID=986 link for more information to store images to database. satya,
you sound like it's been an industry standard to store the actual images as file system objects, rather than in the database. it's actually a matter of trust you have with one over the other. it also depends on the app itself, - surely you don't plan to search for the contents of the file even if it is of type text if it is stored in the database. i successfully implemented storing hard-copy documents in the database that get displayed whenever a hard-copy is requested by the user. i also stored report definitions as well as report outputs into image fields so that they can be viewed using the same format/the same content as when they originally produced. i just don't see a justification for this dogmatic approach. do you?|||ON the basis of performance and managebility I recommended (industry standard as you mentioned) to use the file paths instead of storing them on the database.
If your approach are fetching good results without any issues... say no issues with the database consistency then follow it. As far as my experience is concerned I tend to suggest to use file paths rather than storing images directly on the database.:)|||could you be so kind to refer me to a white paper or a publication where this "industry standard" is being discussed? i'd like to at least conceptually imagine the reasoning for this "standard" to even exist.
thanks|||[http://www.sql-server-performance.com/q&a17.asp] for instance.|||even though it's not a white paper or publication, but rather a personal opinion of an author, i'd like to comment on it:
"...storing BLOB objects in SQL Server is cumbersome..." - only if you are not sure how to do it or the method to do it is not the most efficient
"The most commonly accepted way of managing images for a website is not to store the images in the database, but only to store the URL in the database, and to store the images on a file share that has been made a virtual folder of your web server." - please note that (while still being the personal opinion of the author) the paragraph above states that it's "the most commonly accepted". i'd question that because the shops that i worked for and know of accepted the "more cumbursome" way of dealing with images and documents, and that's how they managed to be more successful than the others. you'd ask me what happened to the others? one example is when system administrator decided to reorganize the file structure which resulted in web-site being out of commission until the "more cumbursome" way was introduced and resolved the issue...forever ;)|||I need the code. Thank you.
My email: kim78@.streamyx.com|||Is this thread ever gonna die down ?|||The Code:
Dim ReferTread as this.tread
Dim Understand boolean
Answer=ReferTread.prevpage
if Understand=1
Response.write("You figured it out!")
elseif Understand=0
Response.write("Forget programming")
end if|||OK, ms_sql_dba's code was neat, but there is ADO.Stream object that pretty much does the same.
The alternative to it is the following (please do not ask to repost!!!) which I left unmodified:
-- Ensure QUOTED_IDENTIFIER setting is OFF
set quoted_identifier off
go
-- Drop all participating objects
if object_id('dbo.sp_OA_CreateFile') is not null
drop procedure dbo.sp_OA_CreateFile
go
if object_id('dbo.sp_OA_WriteLineToFile') is not null
drop procedure dbo.sp_OA_WriteLineToFile
go
if object_id('dbo.sp_OA_CloseFile') is not null
drop procedure dbo.sp_OA_CloseFile
go
if object_id('dbo.sp_StoreImage2Table') is not null
drop procedure dbo.sp_StoreImage2Table
go
if object_id('dbo.tblDocumentSubCategories') is not null
drop table dbo.tblDocumentSubCategories
go
if object_id('dbo.tblDocumentCategories') is not null
drop table dbo.tblDocumentCategories
go
if object_id('dbo.tblDocumentTypes') is not null
drop table dbo.tblDocumentTypes
go
if object_id('dbo.tblImages_tmp') is not null
drop table dbo.tblImages_tmp
go
if object_id('dbo.fn_DateFromCharacter') is not null
drop function dbo.fn_DateFromCharacter
go
if object_id('dbo.tblDocuments') is not null
drop table dbo.tblDocuments
go
-- Build the database structure to store binary data
create table dbo.tblDocumentTypes (
TypeID int identity(1,1) not null primary key clustered,
[Description] nvarchar(450) not null )
go
create table dbo.tblDocumentCategories (
CategoryID int identity(1,1) not null primary key clustered,
[Description] nvarchar(450) not null )
go
create table dbo.tblDocumentSubCategories (
SubCategoryID int identity(1,1) not null primary key clustered,
CategoryID int not null,
[Description] nvarchar(450) not null )
go
alter table dbo.tblDocumentSubCategories
add constraint FK_DocumentCategories foreign key (CategoryID)
references dbo.tblDocumentCategories (CategoryID)
go
alter table dbo.tblDocumentCategories
add constraint UC_UniqueDocumentCategory unique ([Description])
go
create table dbo.tblImages_tmp (fImage image null)
go
create table dbo.tblDocuments (
DocumentID int identity(1,1) not null primary key nonclustered,
TypeID int not null,
CategoryID int not null,
SubCategoryID int not null,
DocumentName nvarchar(2000) not null,
[Description] nvarchar(255) not null,
DocumentSize int not null,
AlternateName nchar(12) not null,
CreattionDate datetime not null,
LastModified datetime not null,
LastAccessed datetime not null,
Attributes varchar(10) not null,
DocumentImage image null)
go
create function dbo.fn_DateFromCharacter (
@.DatePart char(8),
@.TimePart char(6) ) returns datetime
as begin
declare @.Date datetime, @.MonthDay char(4), @.Year char(4), @.Month char(2), @.Day char(2)
declare @.Hour char(2), @.Minute char(2), @.Second char(2)
set @.MonthDay = reverse(cast(reverse(@.DatePart) as char(4)))
set @.Month = cast(@.MonthDay as char(2))
set @.Day = reverse(cast(reverse(@.MonthDay) as char(2)))
set @.Year = cast(@.DatePart as char(4))
set @.TimePart = right('000000'+@.TimePart, 6)
set @.Hour = cast(@.TimePart as char(2))
set @.Second = reverse(cast(reverse(@.TimePart) as char(2)))
set @.Minute = cast(reverse(cast(reverse(@.TimePart) as char(4))) as char(2))
return cast(@.Month + '/' + @.Day + '/' + @.Year + ' ' + @.Hour + ':' + @.Minute + ':' + @.Second as datetime)
end
go
create procedure dbo.sp_OA_CreateFile (
@.FileName nvarchar(2000) = null,
@.FileID int output,
@.fs int output)
as
declare @.result int
exec @.result = sp_OACreate 'Scripting.FileSystemObject', @.fs output
if @.result != 0 begin
raiserror ('Failed to create Scripting.FileSystemObject!', 15, 1)
return (1)
end
exec @.result = sp_OAMethod @.fs, 'CreateTextFile', @.FileID output, @.FileName, 1
if @.result != 0 begin
raiserror ('Failed to create/overwrite specified file!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_OA_WriteLineToFile (
@.FileID int,
@.Line varchar(8000) = null)
as
declare @.result int
exec @.result = sp_OAMethod @.FileID, 'WriteLine', null, @.Line
if @.result != 0 begin
raiserror ('Failed to write specified line to file!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_OA_CloseFile (
@.FileID int,
@.fs int )
as
declare @.result int
exec @.result = sp_OADestroy @.FileID
if @.result != 0 begin
raiserror ('Failed to close file!', 15, 1)
return (1)
end
exec @.result = sp_OADestroy @.fs
if @.result != 0 begin
raiserror ('Failed to close Scripting.FileSystemObject!', 15, 1)
return (1)
end
return (0)
GO
create procedure dbo.sp_StoreImage2Table (
@.FileName nvarchar(4000) = null,
@.FileDescription varchar (255) = null,
@.TypeID int = 0,
@.CategoryID int = 0,
@.SubCategoryID int = 0)
as
set nocount on
declare @.FileSize int,
@.Path nvarchar(4000),
@.FmtFile nvarchar(4000),
@.cmd varchar(8000),
@.DocumentID int,
@.ptr binary(16),
@.ptr_tmp binary(16),
@.fs int,
@.FileID int,
@.error int
if @.FileName is null begin
raiserror ('File name must be specified!', 15, 1)
return (1)
end
if @.FileDescription is null begin
raiserror ('File description must be specified!', 15, 1)
return (1)
end
if charindex('\', @.FileName) = 0 begin
set @.cmd = 'File name <' + upper(@.FileName) + '> is of invalid name!'
raiserror (@.cmd, 15, 1)
return (1)
end
if @.TypeID = 0 begin --Type of document has noot been specified
raiserror ('Document type is not specified!', 15, 1)
return (1)
end
if @.CategoryID = 0 begin --Document category has noot been specified
raiserror ('Document category is not specified!', 15, 1)
return (1)
end
if @.SubCategoryID = 0 begin --Document Sub-category has noot been specified
raiserror ('Document sub-category is not specified!', 15, 1)
return (1)
end
set @.Path = reverse(substring(reverse(@.FileName), charindex('\', reverse(@.FileName), 1), 4000))
set @.FmtFile = @.Path + 'fmt.fmt'
begin tran
create table #FileInfo (
AlternateName nchar(12) null,
FileSize int null,
CreationDate char(8) null,
CreationTime char(6) null,
LastWrittenDate char(8) null,
LastWrittenTime char(6) null,
LastAccessedDate char(8) null,
LastAccessedTime char(6) null,
Attributes varchar(10) null )
create table #output ([output] varchar(8000) null)
commit tran
insert #FileInfo exec master.dbo.xp_getfiledetails @.FileName
select @.FileSize = FileSize from #FileInfo
if @.FileSize is null begin
set @.cmd = 'Specified file <' + upper(@.FileName) + '> does not exist!'
raiserror (@.cmd, 15, 1)
return (1)
end
-- Create format file
exec dbo.sp_OA_CreateFile @.FmtFile, @.FileID output, @.fs output
exec dbo.sp_OA_WriteLineToFile @.FileID, '8.0'
exec dbo.sp_OA_WriteLineToFile @.FileID, '1'
set @.cmd = "1 " +
space(8) + "SQLIMAGE" + space(8) + "0" + space(8) +
cast(@.FileSize as varchar(25)) + space(8) +
char(34) + char(34) + space(8) + "1" + space(8) +
"fImage" + space(8) + char(34) + char(34)
exec dbo.sp_OA_WriteLineToFile @.FileID, @.cmd
exec dbo.sp_OA_CloseFile @.FileID, @.fs
-- Store binary data to temporary location in the database
if exists (select 1 from dbo.tblImages_tmp (nolock)) delete dbo.tblImages_tmp
set @.cmd = "bulk insert " + db_name() + ".dbo.tblImages_tmp from '" + @.FileName + "' " +
"with (formatfile = '" + @.FmtFile + "')"
exec (@.cmd)
set @.cmd = "exec master.dbo.xp_cmdshell 'del " + @.FmtFile + "'"
insert #output exec (@.Cmd)
if not exists (select 1 from dbo.tblImages_tmp) begin
set @.cmd = 'Error occurred while storing file <' + upper(@.FileName) + '> into a temporary table!'
raiserror (@.cmd, 15, 1)
return (1)
end
select @.DocumentID = DocumentID from dbo.tblDocuments d (nolock)
inner join #FileInfo f on d.AlternateName = f.AlternateName and d.DocumentName = @.FileName
-- Insert new tblDocuments record if it doesn't exist
if @.DocumentID is null begin
begin tran
insert dbo.tblDocuments (
TypeID,
CategoryID,
SubCategoryID,
DocumentName ,
[Description] ,
DocumentSize ,
AlternateName ,
CreattionDate ,
LastModified ,
LastAccessed ,
Attributes )
select @.TypeID, @.CategoryID, @.SubCategoryID,
@.FileName, @.FileDescription, @.FileSize, AlternateName,
dbo.fn_DateFromCharacter(CreationDate, CreationTime),
dbo.fn_DateFromCharacter(LastWrittenDate, LastWrittenTime),
dbo.fn_DateFromCharacter(LastAccessedDate, LastAccessedTime),
Attributes from #FileInfo
select @.DocumentID = scope_identity(), @.error = @.@.error
if @.error != 0 begin
raiserror ('Failed to initialize target table!', 15, 1)
rollback tran
return (1)
end
commit tran
end
-- Initialize IMAGE field with NULL
begin tran
update dbo.tblDocuments set DocumentImage = null where DocumentID = @.DocumentID
if @.@.error != 0 begin
raiserror ('Failed to initialize target field!', 15, 1)
rollback tran
return (1)
end
commit tran
-- Use UPDATETEXT to transfer the image from temporary table
select @.ptr = textptr(DocumentImage) from dbo.tblDocuments where DocumentID = @.DocumentID
select @.ptr_tmp = textptr(fImage) from dbo.tblImages_tmp
begin tran
updatetext tblDocuments.DocumentImage @.ptr 0 null tblImages_tmp.fImage @.ptr_tmp
if @.@.error != 0 begin
raiserror ('Failed to store document into target table!', 15, 1)
rollback tran
return (1)
end
-- Clear the temporary table
delete dbo.tblImages_tmp
if @.@.error != 0 begin
raiserror ('Failed to clear temporary table!', 15, 1)
rollback tran
return (1)
end
commit tran
return (0)
go|||Microsoft says it's not wise http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproasp/html/binarydata.asp
Here is some samplecode to do it
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
------
--TEXTCOPY IN
------
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
------
--TEXTCOPY OUT
------
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go|||Please read from the link that you posted and look for the authors (wink-wink, it's not Microsoft!)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnproasp/html/professionalactiveserverpages.asp|||I see. I guess I assume if it's on MSDN it at least passed some review. A few reasons I can think of not to do it are DB performance in general, complexity of the code and apps to support it, and not being able to do incremental backups on a DB like you can for file system documents. It's an often debated topic. We have had at least 5 database backed commercial document management systems and none of them stored documents in the DB. Does MS's terraserver store images in the DB? (I don't know, just wondering.)|||I think that's the reason why it's called terra server, there is nothing else in it, just images.
Yukon will still support TEXT/NTEXT and IMAGE datatypes, but will introduce VARCHAR/NVARCHAR(MAX) as well.|||I am also working on saving IMAGE to MSSQL. I am using JAVA as language.
Please send me code at kuldips@.bsharp.com|||I would recommend to re-read the previous posts to eliminate further questions, because all the answers that can be possibly given to the questions...have been posted...|||THIS THREAD IS DEAD!
DEAD, DEAD, DEAD!
IT HAS BEEN DEAD FOR A YEAR. NOBODY IS GOING TO SEND YOU ANY HELPFUL SOFTWARE. NOBODY IS GOING TO GIVE YOU ANY USEFUL ADVICE.
By posting to this thread and expecting any sort of helpful reply, you hearby declare yourself a moron of insufficient intelligence to browse the internet. For your own protection from phishers, overseas pharmaceutical marketers, and deposed Nigerian despots, please disconnect your 56K modem NOW!|||Hi there
I have a Stored procedure which stores image in SQL tables , if u are interested let me know , I will send you the code
Regards
Rajesh
Plz send the code to me too
thanks
my mail is :
yosif4444@.gmail.com|||yosif4444, you have hereby declared yourself a moron of the highest degree. A poster of insufficient intelligence to browse the internet. Please remove your coffee cup from your CD Drive tray, wipe the white-out off of your screen, unplug your computer, and go outside and play in the driveway.|||This sounds very useful.
Please message me with the code.
Many thanks.
DbaJames.|||This thread collects idiots like flypaper collects flies. DbaJames, you seem to have something stuck to your shoe...|||OK, I'm locking this thread to prevent others from displaying their ignorance.
how to insert data into to image datatype
i have a table gg. It has two fields one is ggno int, ggfig image .
how can i insert into the record into 'gg' table help me with examppleDepends on your coding language, there should be many exmaples out
there for each coding language. Normally you will need to open the
stream and fill a blob, but that is different in every coding language.
how can i insert into the record into 'gg' table help me with examppleDepends on your coding language, there should be many exmaples out
there for each coding language. Normally you will need to open the
stream and fill a blob, but that is different in every coding language.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
How to insert an image to sql db?
Hi there! how to insert an image to sql db using FileUpload Control? I saw a data type named "image", so it means to say you are able to insert an image and not the path, am I right? I spent a lot of time to do this but still, I don't know how to store the image to the db. Please post your example code if you want... I will appreciate all your suggestions or comments about this. Thanks!
Yes, you are on the right track, you want to get the file into a btye array then you can assign it to a sql image parameter. It works with any file not just images, it really means file image.
There is some stuff in this thread that may help
http://forums.asp.net/thread/1322372.aspx
Hope it helps,
Joe
|||http://forums.asp.net/thread/1405280.aspxHow to insert an image or logo on the report manager?
Is there anybody who knows how to set up the report manager so that it will display an image on the home page of the report manager?
Thanks!
Report Manger was not built to be customizable by default. The only thing you can do is hack it by overwriting the images or to change the CSS file it uses.
You need to look in "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\" or whatever your installation directory is. In there you will find a "Styles" directory that has the CSS and an "images" directory in which you need to look for the .JPG files that start with 48 e.g. The home page user 48folderopen.jpg by default. Depending on which page you are RS will use a different image. The main downside is that you have no control over sizing so your image needs to 48x48 otherwise it will look distorted.
|||Adam,
Thx for your response. I want to confirm one thing with you. In order to use my own image, do I have to delete the 48folderopen.jpg file and paste my own image? Please tell me the proper procedure.
Sincerely,
|||I got the answer.
How to insert an image into a table?..is it possible to insert?
I have a created a table pub_info with two fields 1.pub_id string 2.logo
image ...now i want to insert images into logo column and i want to retreive
that into my c#.net program...How can i do that'..please helpYou want to start here:
http://support.microsoft.com/defaul...kb;en-us;317016
http://support.microsoft.com/defaul...kb;en-us;309158
http://support.microsoft.com/defaul...kb;en-us;317043
http://support.microsoft.com/defaul...kb;en-us;317701
-oj
"saroja" <saroja@.discussions.microsoft.com> wrote in message
news:02231950-4493-4F6E-9126-E866337BB032@.microsoft.com...
>I have a created a table pub_info with two fields 1.pub_id string 2.logo
> image ...now i want to insert images into logo column and i want to
> retreive
> that into my c#.net program...How can i do that'..please helpsql
image ...now i want to insert images into logo column and i want to retreive
that into my c#.net program...How can i do that'..please helpYou want to start here:
http://support.microsoft.com/defaul...kb;en-us;317016
http://support.microsoft.com/defaul...kb;en-us;309158
http://support.microsoft.com/defaul...kb;en-us;317043
http://support.microsoft.com/defaul...kb;en-us;317701
-oj
"saroja" <saroja@.discussions.microsoft.com> wrote in message
news:02231950-4493-4F6E-9126-E866337BB032@.microsoft.com...
>I have a created a table pub_info with two fields 1.pub_id string 2.logo
> image ...now i want to insert images into logo column and i want to
> retreive
> that into my c#.net program...How can i do that'..please helpsql
Monday, March 26, 2012
How to insert a german ole objet (bmg-Image) to a RS Report
Hi all,
how can i get an image from a db (blob) field that was inseret into the DB
with Access (OLE-Object) into a Report?!
On Internet (Microsoft KB) i found the following Source
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
But on my side this source does not work. I have researched that there are
different length on the ole header. How do i get the ole header length for an
german ole bmp object?!
Thanks
Danieli have found a solution
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),97))
"Daniel Doerfel" wrote:
> Hi all,
> how can i get an image from a db (blob) field that was inseret into the DB
> with Access (OLE-Object) into a Report?!
> On Internet (Microsoft KB) i found the following Source
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
> But on my side this source does not work. I have researched that there are
> different length on the ole header. How do i get the ole header length for an
> german ole bmp object?!
> Thanks
> Daniel
>sql
how can i get an image from a db (blob) field that was inseret into the DB
with Access (OLE-Object) into a Report?!
On Internet (Microsoft KB) i found the following Source
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
But on my side this source does not work. I have researched that there are
different length on the ole header. How do i get the ole header length for an
german ole bmp object?!
Thanks
Danieli have found a solution
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),97))
"Daniel Doerfel" wrote:
> Hi all,
> how can i get an image from a db (blob) field that was inseret into the DB
> with Access (OLE-Object) into a Report?!
> On Internet (Microsoft KB) i found the following Source
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
> But on my side this source does not work. I have researched that there are
> different length on the ole header. How do i get the ole header length for an
> german ole bmp object?!
> Thanks
> Daniel
>sql
Wednesday, March 7, 2012
How to implement this case?
Hi,
I want the select result 1 record show 1 image but not use table or list, how could I do?
More...
The select result has 2 columns, "Org" and "Image".
"Org" is orgainzation and "Image" save the image, its data type is image.
After select, the record like this
Org Image
2211 000C10000BB0000B30000A..
2212 1FFFC2AFFFF39FFFF3FFFF..
2213 000C10000BB0000B30000A..
2214 ...
...
Every record show its own image, how can I implement it?
Thanks!
can you give more information!!
i think that you can use the table report items in reporting service
|||Dear ,
Drop the image control on the report desinger and check how to get he image from the database.
In the image wizard u will get the option to get image from the database.
HTH
from
sufian
Subscribe to:
Posts (Atom)