Database Discussion Boards

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 | :)

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.

Thanks for the read Smile | :)

I are troll Smile | :)

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++;
                           }
                       }
                   }

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

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

Never underestimate the power of human stupidity
RAH

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

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,

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[^]

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

Niladri Biswas

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

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

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

try this one.

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

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

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

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

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

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

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

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

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

Try this

Select * 
From @BatchHeaderReference bhr
	INNER Join @BatchHeader bh1 on bhr.Child = bh1.bhid
WHERE bhr.child IN (SELECT bhid FROM @BatchHeader WHERE status = 10)

Never underestimate the power of human stupidity
RAH

The way I read this problem all of the children must have the status of 10. If the parent has any children without the status ten then it doesn't come back.

This makes it a bear because you have to do something to match all children with all children who have a status of 10 and only return the parent if the children all come back as status 10.

So I'd use a counting mechanism.

select distinct x.parentid 
from (
select 
bhr.* , 
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status, 
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt, 
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount 
from @BatchHeaderReference bhr
) x
 where x.cnt=x.truecount

I'm more of an oracle guy but the above will work.

Going from the inside out, which is usually the best way to deal with these problems, you create a count of all children. Then a count of all 'true' children- those with a status of 10. Then you put all of that inside of a wrapper which we call X.
From there we treat X as another table.

If you need to also check to see if the parent has a status of 10 then you just change it to this.

select distinct x.parentid  
from (
select 
bhr.* , 
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status, 
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt, 
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount, 
 (select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.parentid) pstatus
from @BatchHeaderReference bhr  
  
) x
 where x.cnt=x.truecount 
 and x.pstatus=10

Here you get the parent status, calling it pstatus, and then just check it at the last stage.

edit: the reason MAX is used is to stop certain errors from creeping in if you have the same value show up but multiple times. If you expect different values for status against a given id then you'd have to take another approach because this would be incorrect.

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

If I have understood your problem correctly, then try the following query

SELECT BHID 'PARENTID' FROM @BatchHeader BHR1

WHERE BHR1.STATUS = 10

UNION


SELECT PARENTID  FROM @BatchHeaderReference BHR

INNER JOIN @BatchHeader BH 

ON BH.BHID = BHR.CHILD 

AND BH.STATUS = 10

As per the current scenario, the output will be

PARENTID
--------
1
2
3
4

If you update 5 and 7 of @BatchHeader to 10, the output will be

PARENTID
--------
1
2
3
4
5
7

Hope this helps
Smile | :)

Niladri Biswas

Hi I used to use the function SCOPE_IDENTITY() to get the inserted identity, now I need to get the inserted GUID any idea how to do so
Thanks

Mohammad Al Hoss

Development To Me Is A Pleasure more than a Job