Database

Go with storing files on fileserver

musefan1-Jun-09 4:36

musefan

1-Jun-09 4:36

Thanks for your input

David Mujica wrote:
will they need to be updated ?

Will be a case of uploading a newer version, i.e. delete the old, create the new

David Mujica wrote:
Will there be concurrent access to them ?

Definitely possible

David Mujica wrote:
Do you need version control ?

David Mujica wrote:
Do you need to track who changed the files ?

Currently the 'uploader' is stored in a Database table along with file name..

Do any of these answers change your mind?

Life goes very fast. Tomorrow, today is already yesterday.

David Mujica1-Jun-09 5:29

David Mujica

1-Jun-09 5:29

Based on your input, I still believe I would go with storing the files on a fileserver.

You can control concurrent access to the files by implementing a "check-out/check-in" logic where a user would be required to make a request to "check-out" a file, have him/her edit it and "check-in" the newly revised version.

Using this method you could create a nice audit log of who is updating the files, when the change was made, etc. You could also create a revision mechanism by renaming the old file on the server with an extension of .001, .002, etc

The down-side of this "check-out/check-in" logic is that someone could check out a file and forget to check it back in, causing someone else not to be able to perform their edits. The work-around to this is an administrative override where the first user would loose their "check-out" status and allow the second user to update the file.

Re: Go with storing files on fileserver

musefan1-Jun-09 5:46

musefan

1-Jun-09 5:46

Thanks, a great suggestion. Thou I think I am covered with concurrent users.

Each upload is basically a new file so a file should never even be open before uploaded. Even an edit will be a new file. BTW file uniqueness is achieved with the use of an ID value from the database.

I am concerned with download of a file by multiple uses but I am assuming this is something that is handled with IIS which is providing the FTP support. I will go and look into this now thou...

Again, thanks for your input. An idea I will definitely keep in mind for the future, perhaps if I create my owner server application that handles file transfer (which I guess is a possible solution for my application needs).

Life goes very fast. Tomorrow, today is already yesterday.

Eddy Vluggen1-Jun-09 5:05

Eddy Vluggen

1-Jun-09 5:05

musefan wrote:
found a number of sources that basically agree on one thing - Databases are good for small files ( < 256KB) where as file systems are better for files larger than this.

Sounds like a rule of thumb gone mad. It would be crazy to store ISO-files in a database, I'll agree to that. There is no limit in size to determine whether or not to store data in a database; it's more appropriate to consider the type of data and it's usage.

There are various pro's and con's for both;

Pro Filesystem
* Simpler maintenance (manipulating files through Windows' Explorer is easier then SQL)
* Auto-integration into your Google Desktop Search / Whatever filesystem-indexer.

Against Filesystem
* Harder maintenance (filesystem rights, networking issues etc)

Pro Database
* Full-text search available, as well as an easy option for replication
* Ability to add auditing easily, as well as file-comments, tags etc.

Against Database
* Files aren't linked to the Active Directory
* GDS/Windows Indexing Service doesn't look "inside" the database to index your files

For both options; don't forget to add a backup-strategy.

I are troll Smile | :)

musefan1-Jun-09 5:13

musefan

1-Jun-09 5:13

Thanks, I appreciate your opinions. Here[^] is one article I found in case you fancy a read, it's related to performance between the two (there is a download on the page for .Doc or .PDF).

Personally, I want to go for the file system option as I prefer the idea of the files being stored as files, rather than in a database.

Life goes very fast. Tomorrow, today is already yesterday.

VFP OLE DB Provider error: Variable X is not found

Eddy Vluggen1-Jun-09 7:57

Eddy Vluggen

1-Jun-09 7:57

Thanks for the read Smile | :)

I are troll Smile | :)

dasha_pl1-Jun-09 3:35

dasha_pl

1-Jun-09 3:35

I need help!
My function inserts certain number of rows into dbf with INSERT INTO statement, dbf has 66 fields.

When I insert 983 records it fails with VFP OLE DB Provider error: Variable X is not found, but for less number of rows - aplication doesn't fails.
I think something wrong with my VFP OLE DB Provider. Surfing the Internet didn't help me Frown | :(

(I'have tried to update my provider - didn't help or I did something wrong) .
Did anyone have this issue?
Here is my code:

OleDbConnection oleDbConnection = new OleDbConnection("Provider=vfpoledb;Data Source=" + _tempFolderPath)

string insertCommandText = String.Format("INSERT INTO {0} ({1}) VALUES ({2})",
                                                                dataTable.TableName,
                                                                columnsList.TrimEnd(','),
                                                                arguments.Replace("P", "?,").TrimEnd(','));

                       int it = 0;
                       foreach (DataRow dataRow in dataTable.Rows)
                       {
                           using (OleDbCommand oleDbCommand = new OleDbCommand(insertCommandText,
                                                                               oleDbConnection,
                                                                               oleDbTransaction))
                           {
                               for (int i = 0; i < dataRow.ItemArray.Length; i++)
                               {
                                   if(IsAdditionalSortColumn(dataTable.Columns[i].ColumnName))
                                       continue;
                                   oleDbCommand.Parameters.AddWithValue("@param" + i, dataRow.ItemArray[i]);
                               }

                               oleDbCommand.ExecuteNonQuery();
                               it++;
                           }
                       }
                   }

SQL 2008 Workgroup - how do CALs work?

aastudent1-Jun-09 0:38

aastudent

1-Jun-09 0:38

Hi
I have a general question about SQL server:

I am running a server that needs a very limited number of user connections (max of 5 at a time). So the 5-CAL edition of SQL 2008 Workgroup seems perfect for me. Now, what I need to know is the following: do I actually need to physically install a CAL on each machine/user that will be using the server (by using I mean reading and writing) or does the 5-CAL licence simply mean that only 5 machines/users will be able to log at any one time?

I.e. can 6 or 7 different people use this as long as only 5 of them are connecting simultaneously?

Thanks
Karl

Re: SQL 2008 Workgroup - how do CALs work?

Mycroft Holmes1-Jun-09 1:14

Re: SQL 2008 Workgroup - how do CALs work?

1-Jun-09 1:14

There may not be a LMBTFY yet but try this[^], 4-5th result looks promising

Never underestimate the power of human stupidity
RAH

aastudent1-Jun-09 1:26

aastudent

1-Jun-09 1:26

Lots of general info - unfortunately nothing specific to what I am asking. Thanks for the link though

problem in executing stored procedure

souravghosh1831-May-09 20:12

souravghosh18

31-May-09 20:12

I am executing a stored procedure written in oracle from my web page.The procedure has 5 input variable and 2 output variable.But when i am executing the procedure it gives an error.

"ORA-06502: PL/SQL: numeric or value error: character string buffer too small"

i ma using following code,

cmdproc.CommandType = CommandType.StoredProcedure
cmdproc.CommandText = "avlmfg.PROC_TUBE_COIL_LINK"
Dim tub_batch As OracleParameter = cmdproc.Parameters.Add("ls_tube_batch", OracleDbType.Varchar2)
Dim tub_aufnr As OracleParameter = cmdproc.Parameters.Add("ls_aufnr", OracleDbType.Varchar2)
Dim tub_matnr As OracleParameter = cmdproc.Parameters.Add("ls_mill_matnr", OracleDbType.Varchar2)
Dim tub_nos As OracleParameter = cmdproc.Parameters.Add("ln_no_of_tubes", OracleDbType.Int64)
Dim tub_arbpl As OracleParameter = cmdproc.Parameters.Add("ls_arbpl", OracleDbType.Varchar2)
Dim tub_error_flag As OracleParameter = cmdproc.Parameters.Add("LS_ERROR_FLAG", OracleDbType.Varchar2)
Dim tub_data1 As OracleParameter = cmdproc.Parameters.Add("data1", OracleDbType.Varchar2)
tub_batch.Direction = ParameterDirection.Input
tub_aufnr.Direction = ParameterDirection.Input
tub_matnr.Direction = ParameterDirection.Input
tub_nos.Direction = ParameterDirection.Input
tub_arbpl.Direction = ParameterDirection.Input
tub_error_flag.Direction = ParameterDirection.Output
tub_data1.Direction = ParameterDirection.Output

tub_batch.Value = txtChargR1.Text.ToUpper()
tub_aufnr.Value = txtPO.Text.Trim
tub_matnr.Value = txtMatnr1.Text.Trim
tub_nos.Value = schqty
tub_arbpl.Value = arbpl
cmdproc.ExecuteNonQuery()

Thanx in advance,

Re: problem in executing stored procedure

Aman Bhullar6-Jun-09 1:50

Aman Bhullar

6-Jun-09 1:50

souravghosh18 wrote:
Dim tub_batch As OracleParameter = cmdproc.Parameters.Add("ls_tube_batch", OracleDbType.Varchar2)

Try adding the length of the varcher2 variable as

OracleParameter = cmdproc.Parameters.Add("ls_tube_batch", OracleDbType.Varchar2, 100)

100 will be the lenght of varchar you have defined in database

Regards
Aman Bhullar
www.arlivesupport.com[^]

Re: problem in executing stored procedure

Niladri_Biswas25-Jun-09 5:57

Niladri_Biswas

25-Jun-09 5:57

In some fields the parameter length mismatch is happening
Smile | :)

Niladri Biswas

CONTAINS not working in my full text search

Brendan Vogt31-May-09 5:13

Brendan Vogt

31-May-09 5:13

Hi,

I just added full text searching to a table, and the text in the ProductLongDescription column is:

On the go. For all purpose Industrial and Outdoor purposes. Half Hose. Available in Grey or Navy Blue.

When I use the following statement then the record above is returned:

SELECT ProductID_PK, ProductName, ProductLongDescription
FROM tblProduct
WHERE CONTAINS(ProductLongDescription, 'go');

When I use the following statement then nothing is returned, but you can see that "for" is in the sentence as well:

SELECT ProductID_PK, ProductName, ProductLongDescription
FROM tblProduct
WHERE CONTAINS(ProductLongDescription, 'for');

What am I doing wrong? Please can someone help me?

Thanks
Brendan

Re: CONTAINS not working in my full text search

Satish Pai31-May-09 19:49

Satish Pai

31-May-09 19:49

Hi Brendan

"a","the","an", "for".. etc are noise words. If you view the Messages can see a information

Informational: The full-text search condition contained noise word(s).

if u execute the same query searching for Outdoor then the query runs properly.

Thanks & Regards
Satish Pai B

line break not working in crystal reports ?

kindman_nb30-May-09 21:58

kindman_nb

30-May-09 21:58

I'm using vs2008 , I try to view multi line at crystalreport textobject as next

<br />
Dim s As String<br />
s = s &amp; "this is first line " &amp; Chr(13) + Chr(10)<br />
s = s &amp; "this is second line "<br />
Dim rpt As New CrystalReport1<br />
<br />
Dim txt As TextObject<br />
<br />
txt = rpt.Section2.ReportObjects("Text1")<br />
txt.Text = s<br />
<br />

but it isn't working ,
it print the text at single line ,, despite of when I export the report to doc file, I fount that it's printed at two lines ..

what can I do
Thanks

jooooo

Re: line break not working in crystal reports ?

Rajesh Anuhya1-Jun-09 0:03

Rajesh Anuhya

1-Jun-09 0:03

try this one.

s = s & "this is first line " & vbCrLf

Rajesh B --> A Poor Workman Blames His Tools <--

Help with SQL query

Joe_P30-May-09 10:30

30-May-09 10:30

declare @BatchHeader table
(bhid int, status int)
insert into @BatchHeader values (1, 10)
insert into @BatchHeader values (2, 10)
insert into @BatchHeader values (3, 10)
insert into @BatchHeader values (4, 10)
insert into @BatchHeader values (5, 15)
insert into @BatchHeader values (6, 15)
insert into @BatchHeader values (7, 15)

select * from @BatchHeader

declare @BatchHeaderReference table
(parentid int, child int)

insert into @BatchHeaderReference values (1,2)
insert into @BatchHeaderReference values (1,3)
insert into @BatchHeaderReference values (1,4)
insert into @BatchHeaderReference values (1,5)
insert into @BatchHeaderReference values (1,6)
insert into @BatchHeaderReference values (1,7)

select * from @BatchHeaderReference

@BatchHeaderReference.ParentId and Child are the FKs to @BatchHeader.bhid

The output is this:
@BatchHeader
bhid Status
1 10
2 10
3 10
4 10
5 15
6 15
7 15

@BatchHeaderReference
Parentid Child
1 2
1 3
1 4
1 5
1 6
1 7

I want to write a query to fetch unique @BatchHeader rows only if the parent and child have status = 10. So in this case the query shoudnt return any @BatchHeader rows (bcos bhid 5,6,7 have status 15). But if I update the status for bhid=5,6,7 to 10 then the query should return bhid=1.

I tried something like this but it returns bhid=1
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status

Please help.

Thanks,
Joe

Mycroft Holmes30-May-09 23:06

30-May-09 23:06

I had to go look at this properly IE in TSQL. @BatchHeaderReference joins ALL records to the header record 1, therefore header records 2-7 do not take part in the statement. no link to the @BatchHeaderReference table!

Joe_P wrote:
oin @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid

What database are you using, I have never seen readpast

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 4:11

31-May-09 4:11

I am using SQL Server as the database. I have copied and pasted the query as-is but for simplicity, I changed the tables to table variables, so you see the (readpast) in the query that fetches rows from a table variable.

I have to achieve this in a single SQL, so cant use any complex logic of additional table variables.

Thanks,
Joe

Mycroft Holmes31-May-09 11:10

31-May-09 11:10

You missed the point, the problem is in your join. All reference records join to hdr record 1 and are therefore valid.

parentid int (1) = bhid int (1) therefore status will always be 10

@BatchHeaderReference
Parentid Child
1	2
1	3
1	4
1	5
1	6
1	7

Is correct, status 15 never comes into it and the 2-7 are NOT FROM THE HDR table. I think your data structure is screwed, if your status was on the reference table then the filtering would work

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 11:23

31-May-09 11:23

I understand, but I cant have a status on the BatchHeaderReference table. This table is just to figureout the parent/child relationship and the status of these should be retained on the BatchHeader table only.

So only if all the header and child rows from HDR table are in status 10 then the query should return the id.

Thanks,
Joe

Mycroft Holmes31-May-09 11:36

31-May-09 11:36

Joe_P wrote:
Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status

One begins to see the light....
Change the joins to INNER JOIN and add the filter (=10) on BH2 join rather than the where clause

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 14:08

31-May-09 14:08

Thanks for the reply Mycroft Holmes, but no luck.

Tried this
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Inner Join @BatchHeader bh1 on bhr.ChildId = bh1.id and bh1.Status = 10
Inner Join @BatchHeader bh2 on bhr.ParentId = bh2.id and bh2.Status = 10

And
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Inner Join @BatchHeader bh1 on bhr.ChildId = bh1.id
Inner Join @BatchHeader bh2 on bhr.ParentId = bh2.id and bh2.Status = 10
Where
bh1.Status = 10

Mycroft Holmes31-May-09 17:24