|
k i guess i'll try that thanks for the advice
|
|
|
|
|
Hi there,
I've got a table which acts as a repository for searchable text data (lets call it MyInfoTable). Through a User Interface (Web) I'd like to let the user allow searching the Content field (type text) of MyInfoTable. The user can add various terms which are then used to build the query.
A couple of questions arise:
1. How can I build a injection-safe query which makes use of the WHERE clause and adds all the search terms into it. I'd like to do it through a stored proc but how can I specify a collection of params (one for each search term) to pass into it?
2. Is there a best-practise regarding searches on midium to large datasets? Things I have to take care of, etc.?
Sorry, if this questions are quite dumb, but I've never seen a fulltext search implemented and just trying to get started... 
Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
Hi, I think this may help you - I created this stored procedure for use in Northwind:
CREATE PROCEDURE SearchProducts (
@ProductName NVARCHAR(40) = null,
@UnitPrice MONEY = null,
@UnitsInStock SMALLINT = null
)
AS
SELECT *
FROM Products
WHERE (IsNull(@ProductName, ProductName) = ProductName) AND
(IsNull(@UnitPrice, UnitPrice) = UnitPrice) AND
(IsNull(@UnitsInStock, UnitsInStock) = UnitsInStock)
GO
Basically make sure you insert a null for all the parameters that you dont use.
|
|
|
|
|
The easiest way is to use Boolean logic in the where clause.
CREATE PROCEDURE ....
@LastName varchar(20),
@Department int
...
AS
SELECT @LastName = '%' + @LastName + '%'
SELECT ...
FROM ...
WHERE (@LastName Is Null
OR table.LastName LIKE @LastName)
AND (@DepartmentID Is Null
OR @DepartmentID = table.DepartmentID)
...
... This should get you started with the concept. Either the parameter is Null or the paramenter must represent a column value.
Michael
|
|
|
|
|
I have this one question in regards to configuration file - what if I need to configure for MULTIPLE DATASOURCE?
Example:
<nhibernate>
<add
key="hibernate.connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"
/>
<add
key="hibernate.dialect"
value="NHibernate.Dialect.MsSql2000Dialect"
/>
<add
key="hibernate.connection.driver_class"
value="NHibernate.Driver.SqlClientDriver"
/>
<add
key="hibernate.connection.connection_string"
value="Server=localhost;initial catalog=MyDataSrc01;uid=SomeUser; pwd=xxx;"
/>
<add
key="hibernate.connection.connection_string"
value="Server=localhost;initial catalog=MyDataSrc02;uid=SomeUser; pwd=xxx;"
/>
</nhibernate>
This obviously doesn't work. Any suggestion? Thanks in advance.
Norman Fung
|
|
|
|
|
I got total pages difference between Print-Preview and Layout-Preview
I've set paper size.
in Layout-Priview the report generate 2 pages, but in print-preview the report generate 10 pages, it seems print-preview going wrong.
any idea ?
email : sukasukabo@hotmail.com
|
|
|
|
|
not sure what you meant sayign "Print-Preview and Layout-Preview". I had the same problems with pages# when tried to render to html. i were googling but no success then I've resolved that by addind the "page n of m". i parse a resulting html content using the regex "page \d+ of \d+". found max(m) and then removed page # from the html
|
|
|
|
|
what is the syntax of the query which gets the member properties asscociated with every level which are then used as predicted attributes.
that's because i am building a decision tree on data coming out from cube.
If I write this Query
select{[Customers].[Name].members,[Customers].[Country].members,[Customers].[City].members}on columns,
{[Time].[Year].members}on rows
from sales
he gives me on columns all the names of customers and their cities and their countries and inside each cell their measures related to every year with some empty cells.
I want to get the result as 2 dimensional table to build the tree and can calculate the greatest gain to get the root and then trace the path from the root to reach the leafs which hold the classes or the categories predicted.
Please anyone who is interested in olap cubes and data mining algorthims help me.
|
|
|
|
|
Hello,
Is there a way to select a row from a table based on a minimum? Or what is the correct syntax?
This query returns two rows when I only need a row with the min column... 
select min(col1ID), col2ID, col3 from tbl where col2ID = 'xxxx' group by col2ID, col3
Thanks,
jay
|
|
|
|
|
Jaydeanster wrote:
This query returns two rows when I only need a row with the min column...
select min(col1ID), col2ID, col3 from tbl where col2ID = 'xxxx' group by col2ID, col3
Your GROUP BY clause causes more than one row to be returned. I am guessing that you want the row with the MIN(col1ID) with the other columns too. So, assuming that each col1ID value is unique then the following should work:
SELECT m.col1ID, t.col2ID, t.col3
FROM tbl AS t
INNER JOIN (SELECT MIN(col1ID) as col1ID
FROM tbl
WHERE col2ID = 'xxxx') AS m ON m.col1ID = t.col1ID
WHERE col2ID = 'xxxx'
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
is it possible to work in rdl with an external dataset without the datasource mentioned alongwith it ?
Prithiv thirugnanasambandam Vasudevan
|
|
|
|
|
looks like you can't. read my previous post it seems i've described how to solve that
|
|
|
|
|
Hi,
I've got a stored procedure which takes an int (@TopLimit) as a parameter. I'd later like to use this parameter to make a top selection, like this:
SELECT TOP @TopLimit NewsText, LanguageID FROM vl_News
But I get an Error on this one saying the syntax is wrong. What am I missing here?
Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
You cannot use a variable with the TOP statement. Bu you can try the folowing:
<br />
Set RouwCount @TopLimit<br />
SELECT NewsText, LanguageID FROM vl_News<br />
Wout Louwers
|
|
|
|
|
I sticked to Wout's solution. Thank you both!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
matthias s. wrote:
SELECT TOP @TopLimit NewsText, LanguageID FROM vl_News
This is not permitted in SQL Server 2000, (it is available in SQL Server 2005).
Your Stored Procedure will need to build some dynamic SQL and execute it.
e.g.
EXEC('SELECT TOP '+CAST(@TopLimit AS VARCHAR(10))+' NewsText, LanguageID FROM vl_News');
Using Dynamic SQL like this has some security implications. For example, at the boundary of the EXEC the security is re-evaluated. Normally, you can grant execute permission to a stored procedure but not need to grant select/update/insert/delete permission to the tables the stored procedure uses because the security is evaluated when you start the stored procedure and it is not re-evaluated for each of the tables within it. With dynamic SQL execution the security is re-evaluated so you must have SELECT permission on the table in the EXEC statement.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
|
I am trying to create a stream object to insert data into a text column in SQL Server, but am not too sure how to go about it.
How would I create an IStorage object or gain access to an IStorage interface so I can use the CreateStream function?
Matt Harvey
|
|
|
|
|
See IStream - Compound File Implementation[^].
Presumably you have your data either already in memory or in a disk file. If the former, you probably want to use CreateStreamOnHGlobal to wrap memory with an object that implements IStream. If the latter, see StgCreateDocfile with STGM_CONVERT. Once you've created the storage, you should be able to call IStorage::OpenStream on the newly converted "Contents" stream. As long as you don't call IStorage::Commit, the original file will not be affected.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
The data is in memory. This is the process I am using:
1. Execute "SELECT column FROM table" command and obtain the IRowset object.
2. Get IRowSetChange and IRowsetUpdate objects
3. Set bindings and CreateAccessor
4. Call IRowset->GetNextRows() and IRowset->GetData() to obtain row accessor.
5. Use CreateStreamOnHGlobal() to create Stream.
6. Call IRowsetChange->SetData, then IRowsetUpdate->Update
This process works ok if I bind as a simple DBTYPE_STR, but when I try to do the update using a DBTYPE_IUNKNOWN object of type IID_ISequentialStream, then update does not insert the stream object I created.
This may have something to do with it. In the bindings I specify, the only DBOBJECT flags and iid that work for the CreateAccessor call are these:
dbObject->dwFlags = STGM_READ;
dbObject->iid = IID_ISequentialStream;
|
|
|
|
|
I have just realised what I have missed out here. I did a QueryInterface on the IStream to get an ISequentialStream pointer and now using that the update works.
|
|
|
|
|
i'm just trying to insert some values into a table, but i got this error which says:
Operator & not defined for types System.Data.SqlTypes.SqlMoney
This is the line which gives the error:
cmd = New SqlCeCommand("INSERT INTO stock (name,price,desp) VALUES ('" & name & "', '" & newprice & "' ,'" & desp & "'')", connectionstring)
all other parts of the code work fine, which is why i'm only posting this small bit. price in my database is defined as type money, and newprice is of type SqlMoney too. Can someone help? thanks!
|
|
|
|
|
You should not create SQL Statements like this if at all possible because it is vulnerable to a SQL Injection Attack.
SQL Injection Attacks and Some Tips on How to Prevent Them[^]
Some Microsoft documentation that might help:
SqlCeCommand.Parameters[^]
I've left the explanantion for your error until the end of this message as I wanted to get across the point about security your applications against attack.
The reason for your error is that SqlMoney is not a string and the & operator is used for concatenating strings together. This is an inefficient way to concatenate strings, you should consider using String.Concat() or String.Format(), the latter being more likly to understand how to insert a SqlMoney object into the string without you having to write any code to convert it yourself.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i know that SqlMoney is not of a string type, but then again i'm not trying to insert a string into the query. The data type i specified in my database is of type Money, which is why i'm using SqlMoney in my code. Right now i'm thinking of ways on how to insert the value in, but its giving me errors so far.......thanks for the reply anyway.
|
|
|
|
|
gundamhamtaro wrote:
i know that SqlMoney is not of a string type, but then again i'm not trying to insert a string into the query
You are trying to concatenate the SqlString object into a string. The SQL query is a string, therefore anything in the query must be converted to a string so it can be submitted to the database.
gundamhamtaro wrote:
Right now i'm thinking of ways on how to insert the value in, but its giving me errors so far
Did you read my post? Have you tried parameterised queries like I suggested? It isn't just for the security considerations. It makes converting data types to SQL much easier also.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
