|
Guys,
I'm relatively new to using databases and have been playing around with SQL Server 2005 to do some stuff. However, I'm looking at extending an application that uses images and documents (mainly pdf) and I want to be able to store these somewhere (ideally in a database). I haven't found any useful information on Google, so wanted to ask others for their suggestions on how to do this. It doesn't have to be on SQL Server and I can basically use any db that's up to the task.
Thoughts welcome,
John.
|
|
|
|
|
jgrogan wrote: haven't found any useful information on Google
Then you are blind, this question is asked at least once a month (if not more often), has been answered with 1000s of excellent articles. Google search works just fine for me[^]. CP article search will give you some excellent articles.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
|
Yup Mycroft,Hiren and Makr already given you some link for best article over how to save image int database.
you can also download the given sample code to learn how to store and retrieve image from SQL server.
Click on the given link to Download
Sample Codes[^]
Hope it will works for you.
|
|
|
|
|
Guys,
Thanks for taking the time to point me at some good articles.
Does anyone have any thoughts on storing documents (pdf, xls, doc) etc in a database and how to do it. The only suggestion I've found is to store them in o/s file structure and use the database to store path and some info.
What would be the pros / cons of using a DB rather than the O/S?
Thanks,
John.
|
|
|
|
|
|
jgrogan wrote: oes anyone have any thoughts on storing documents
Now thats a different question, why not how, much more interesting.
As with anything there are a number of pros and cons, they will depend on your requirements, your hosting environment, the cost of maintenance and infrastructure. Basically 3 styles of storage (using sql server).
1. Files stored in file systems with path in the record.
2. Files stored as binary data in the record
3. Files stored in a database managed file store.
2 means the database may grow dramatically affecting the cost of backup
3 allows you to split the backup to exclude the images (option) reducing backup cost.
Then there is the whole discussion around access of the files, 1 requires file management 2 & 3 require database operation. Having used all 3 I still go for 1 as the most widely used and understood.
Do not take this as definitive, you need to do some research and balance that with your requirements and budget.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can I assume that the same issues apply to Oracle and mySQL?
What about distributed databases such as Cassandra? Does anyone have any experience of that?
|
|
|
|
|
jgrogan wrote: Can I assume that the same issues apply to Oracle and mySQL?
Absolutely NOT each database will have it's own issues and methods. You are moving into a more esoteric area of data storage and all of them will treat it differently. I only have (relevant) experience with sql server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I agree here... I prefer #1 in just about every case. I just never makes sense to me to store the actual images or documents in the DB itself since it really provides you nothing in the way of performance or search capabilities and I think really adds more weight to your DB than is needed.
Sure, I would guess that you can gain some backup and restore simplicity but I just don't see that as being a real benefit.
Now, it would be cool if you could use a type of image bassed query engine on a DB full of pictures... that would be really neat.. IE: fed in a picture of a particular bird and get back a record set of pictures containing that type of bird, or similar birds with that colorization or marking pattern... but I suspect that would be VERY difficult and not very efficient really. Would still be neat though.
I always wanted to try to write a function that you could feed in an image and a folder and have it search through all the images and see if your input image was a subset (IE: copied section) of one of the images in the source folders...
|
|
|
|
|
Ray Cassick wrote: I just never makes sense to me to store the actual images or documents in the DB itself since it really provides you nothing in the way of performance or search capabilities and I think really adds more weight to your DB than is needed.
The link I posted does show a performance difference.
Additionally a factor could be access control, especially when users are managed as data versus actual db users or system users.
|
|
|
|
|
If there is any chance that this could move to SqlServer 2008, you might consider FileStream Storage[^].
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
hy,
I have created an ssis package which tranfers data from one database to another,first i drop the tables and then recreat it before inserting data.
I have a cloumn which has a datatype of varchar(MAX) and on re creation becomes nvarchar(50).
what should i do to avoid it.
Thanks & Regards,
Raghvendra Panda
|
|
|
|
|
Hi,
I am having an issue executing a create procedure query to Oracle 10g. I am building the query in C++ (Exhibit A) and executing it using ADO (Exhibit B). In most systems, which I do not manage, the query is build and executed on oracle correctly (see Exhibit D). However, I have this one Oracle server that is, for some odd reason, ignoring the new line feeds (\r\n) (see Exhibit C).
Is there a setting in Oracle that I am over looking?
Your help is appreciated. Thanks in advance. Let me know if you need any more info.
EXHIBIT A - Code in C++ to build query:
CString cstrTmp;cstrTmp =
"CREATE OR REPLACE PROCEDURE \"DoSomething\" \r\n"
"(\r\n" " pTxt nvarchar2\r\n" ")\r\n"
"as\r\n"
"\r\n"
"vXml XmlType;\r\n"
"vTempCounter int;\r\n"
"\r\n" "begin\r\n"
" --my comment\r\n"
" dbms_output.put_line('test');
"end;\r\n"
EXHIBIT B - This is the code that executes the query above to the database (in c++):
if(pCommand == NULL)
{
pCommand.CreateInstance(__uuidof(Command));
pCommand->CommandType = adCmdText;
}
pCommand->CommandText= _bstr_t(*cBuffer);
pCommand->CommandTimeout = m_pConn->CommandTimeout;
pCommand->ActiveConnection = m_pConn;
pCommand->Execute(NULL, NULL, adExecuteNoRecords);
pCommand = NULL;
EXHIBIT C - This is how the query is seen in the procedure - In one single line. \r\n is ignored:
CREATE OR REPLACE PROCEDURE "DoSomething"(pTxt nvarchar2)asvXml XmlType;vTempCounter int;BEGIN --myComment dbms_utput.put_line('test');END;
EXHIBIT D - It should show up like this after it is created on the database:
CREATE OR REPLACE PROCEDURE "DoSomething"
(pTxt nvarchar2)
asv
Xml XmlType;
vTempCounter int;
BEGIN
-- my comment
dbms_output.put_line('test');
END;
|
|
|
|
|
For the one Oracle server that produces your Exhibit C, is that a Windows server or some other flavour OS that is hosting Oracle.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Its a Windows 2008 machine.
|
|
|
|
|
Since it is Windows, it's more probable that it is not dropping your escape characters. I was just wanting to see if some flavour of Unix was involved and somehow along with translating the characters, it was also dropping them. Not sure what else you can look at, though. Sorry. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
My first thought was unix as well. The user swears that it's a windows. Is there a query I can run to figure out what is the server is running on?
|
|
|
|
|
Dear Friends,
I have a query in which there is a sub query that is fetching a no of rows (single column); it may be one,two..upto six (not more than that). It is using for xml path( '' ) feature of SQL server in order to display the values as comma separated values.
The query is as follows:-
select distinct (select top 1 CLAIMMASTERID from ClaimMaster where encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6') ,EVC.EncounterID,EVC.CPT,(select (select ICDCode + ',' as [text()] from VisitTypeICDCPT soi where soi.VisitTypeID = t.VisitTypeID and VisitTypeID=VTI.VisitTypeID and encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6' order by VisitTypeID for xml path( '' )) from (select distinct VisitTypeID from VisitTypeICDCPT where VisitTypeID=VTI.VisitTypeID and encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6') as t )DiagnosisCode,EVC.Fee,convert(varchar(10),'2010-12-14 00:00:00.000',101) from EncounterVisitCode EVC inner join VisitTypeICDCPT VTI on EVC.encounterid=VTI.encounterid where EVC.encounterid='87B6D11C-3680-44A5-B1DB-AB16B3907AA6'
Here the sub query(starting from :-(select (select ICDCode...)) which is fetching the data from the database and the where "Diagnosis Code" is the column name. The data query is fetching is like this:- 004.12,001.20,...upto six values (may be less).
Here i want the values to be replaced by integer values depending upon the no of values being fetched by sub-query i.e., 1,2, if values in no. are two, 1,2,3, if values in no. are three and so on...
I am not able to find any way out to solve this problem. If someone could help me resolve this problem i would be very grateful to him/her.
Thanks
Varun Sareen
modified on Friday, December 17, 2010 6:53 AM
|
|
|
|
|
Please wrap your query in <pre lang="sql"><pre> TAG. so that we can see something from that mess.
|
|
|
|
|
I copied it out and reformatted, then decided I did not have enough time to decipher all the queries within queries.
|
|
|
|
|
djj55 wrote: I copied it out and reformatted, then decided I did not have enough time to decipher all the queries within queries.
Agreed!
But reformatting takes 10 seconds, so if someone else wants to give it a shot:
SELECT DISTINCT (SELECT TOP 1 claimmasterid
FROM claimmaster
WHERE encounterid = '87B6D11C-3680-44A5-B1DB-AB16B3907AA6'),
evc.encounterid,
evc.cpt,
(SELECT (SELECT icdcode + ',' AS [text()]
FROM visittypeicdcpt soi
WHERE soi.visittypeid = t.visittypeid
AND visittypeid = vti.visittypeid
AND encounterid =
'87B6D11C-3680-44A5-B1DB-AB16B3907AA6'
ORDER BY visittypeid
FOR XML PATH( '' ))
FROM (SELECT DISTINCT visittypeid
FROM visittypeicdcpt
WHERE visittypeid = vti.visittypeid
AND encounterid =
'87B6D11C-3680-44A5-B1DB-AB16B3907AA6') AS t
)
diagnosiscode,
evc.fee,
CONVERT(VARCHAR(10), '2010-12-14 00:00:00.000', 101)
FROM encountervisitcode evc
INNER JOIN visittypeicdcpt vti
ON evc.encounterid = vti.encounterid
WHERE evc.encounterid = '87B6D11C-3680-44A5-B1DB-AB16B3907AA6'
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Dear djj55,
I have formatted the query. Please go through it and if you have any answer to it then kindly let me know.
Thanks & Regards
Varun Sareen
|
|
|
|
|
I think you haven't applied lang="sql" Yet. It's not indented.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
