SP - Efficient "Find" method - Database Discussion Boards

Detecting overlapping date ranges - best strategy - help?

Paul Evans1-Aug-04 5:38

1-Aug-04 5:38

Hi there,
I have a situation where a user may want to search against stored date ranges and single dates... while using either a single date or a date range. A document has content values associated with it.

The data is stored within a Postgresql database (although this is more a strategic thing so for the purpose of this discussion that doesn't really matter) and used by a c# application. In
the content table amongst other things it includes 2 date fields - a start date field (that doubles as the single date storage) and an end date field.

ORs are very hard for databases to do on large tables, so I am trying my best to avoid using an OR - instead joining the same table multiple times and using AND. Of course multiple joins are pretty hard for it too - just doesn't seem to be as hard.

I'm also trying to avoid using temporary tables where possible.

Searching for a single date within a range is pretty easy without resorting to either OR or temporary tables.

Note below isn't the actual code, just a simplified mockup.

Searching a single date against a stored range goes something like: (1990-01-01 being the search term)

select docid from document d, contentvalue v1 where
docid.cvid = v1.cvid AND
v1.type = 'daterange' AND
v1.startdate >= '1990-01-01' AND
v1.enddate <= '1990-01-01';

... a range against a single date goes ...
(1990-01-01 - 2000-01-01 being search term)
select docid from document d, contentvalue v1 , contentvalue v2
where
docid.cvid = v1.cvid AND
v1.type = 'date' AND
v1.startdate >= '1990-01-01'
v1.cvid = v2.cvid AND
v2.type = 'date' AND
v2.startdate <= '2000-01-01';

Right now though I'm stuck trying to figure out a decent way of comparing 2 overlapping ranges without using temporary tables or an OR.

If anyone has any alternate strategies for the whole thing, or for decent date-range against date-range storage within a database, and / or any links to any resources on the web I'd be very thankful.

/**********************************
Paul Evans, Dorset, UK.
**********************************/

hai

Asha Rams31-Jul-04 22:32

Asha Rams

31-Jul-04 22:32

How do we bind a ado edit control to a combobox to get the
second recordset field data

BTrieve 6.15

Mega131-Jul-04 18:05

Mega1

31-Jul-04 18:05

Anyone know of a way to connect to a btrieve 6.15 database using VB?

Re: BTrieve 6.15

Mekong River1-Aug-04 4:07

Mekong River

1-Aug-04 4:07

Try to read the information from Here[^] and http://www.connectionstrings.com/[^]. It might give you some idea.

Roath Kanel
APO-CEDC
Save Children Norway-Cambodia Office

Synchronizing tables

winpiglet31-Jul-04 10:43

winpiglet

31-Jul-04 10:43

Hi.

I need to synchronize the local tables of my Access application with the tables that are deployed on the Web Server. My question is: Is this possible in MS Access or Do I need to use a different language to accomplish this synchronization module? Thank you very much for your insights... Confused | :confused:

is there any Win32 function help access SQL server?

fu031-Jul-04 1:47

fu0

31-Jul-04 1:47

i want to write a win32 DLL which can access SQL2000 database.
is there any win32 function existing?

Or i have to use MFC/.NET functions ?

Re: is there any Win32 function help access SQL server?

Michael P Butler31-Jul-04 22:14

Michael P Butler

31-Jul-04 22:14

Both ADO and ODBC can be used from a Win32 dll. Plus there is the SqlLib that comes with the SQL Server developer tools.

Have a read on MSDN for full details.

Michael

CP Blog [^]

SP - Efficient "Find" method

UB30-Jul-04 21:58

30-Jul-04 21:58

I want another efficient way to re-write this query.
Note : @empid, @batchid and @name are not always passed to the SP

CREATE PROCEDURE FindEmployee
		@empid          INTEGER         = NULL,
		@batchid        INTEGER         = NULL,	
		@name           VARCHAR(30)     = '%'
AS

	SELECT * FROM Employees
	WHERE  @empid LIKE COALESCE(CONVERT(varchar(3), @empid), '%')
	AND    @batchid LIKE COALESCE(CONVERT(varchar(3), @batchid), '%')
	AND    name LIKE ('%' + @name + '%'

1. If @empid and @batchid were not provided, is there a way to remove thoese columns from the where clause conditionally without writing a seperate query

2. What are the otherways to improve the performance of this kind of a query

Thanks

UB

You may stop this individual, but you can't stop us all... after all, we're all alike. +++Mentor+++

Re: SP - Efficient "Find" method

Daniel Turini31-Jul-04 11:05

Daniel Turini

31-Jul-04 11:05

As a general rule, don't use functions or the LIKE operator on your table columns or your query won't use any indexes.
The easiest way of speeding this query is by replacing this (I assume there's a typo and there is no '@' outside the COALESCE):

WHERE  empid LIKE COALESCE(CONVERT(varchar(3), @empid), '%')
AND    batchid LIKE COALESCE(CONVERT(varchar(3), @batchid), '%')

for something like this:

WHERE  empid BETWEEN @firstEmpid AND @lastEmpid 
AND    batchid  BETWEEN @firstBatchid AND @lastBatchid

And do a bit of math to calculate the first and last ids.
If this filter returns a small result set, then the slowest part (show below) of the query won't need to run against a lot of records and the query will be way faster.

AND    name LIKE ('%' + @name + '%'

Yes, even I am blogging now!

SQL query to generate random numbers

Imtiaz Murtaza29-Jul-04 23:45

Imtiaz Murtaza

29-Jul-04 23:45

I create a new table. The table consists of only one column of type varchar(7).

Now i want to fill 50 random values in this table. Each value should be a seven digit number. e.g the final shape of table should be something like:

Number
======
2216789
2134567
1287905
..
..

Can anyone tell me the SQL query to generate random numbers of seven digits and enter in a table ?? It is better if numbers entered are unique.

Imtiaz

Re: SQL query to generate random numbers

Steven Campbell30-Jul-04 7:55

Steven Campbell

30-Jul-04 7:55

Try:
select cast(rand() * 10000000 as integer)

my blog

Re: SQL query to generate random numbers

Grimolfr30-Jul-04 8:22

Grimolfr

30-Jul-04 8:22

Assuming we're talking about SQL Server, this will work for a single random number, but not for generating 50 random numbers simultaneously.

From BOL:

"Repetitive invocations of RAND() in a single query will produce the same value."

If you tried a single insert statement to insert 50 numbers simultaneously, they'd all be the same number.

Try this:

DECLARE @Rand TABLE (Number int PRIMARY KEY)

WHILE (SELECT COUNT(*) FROM @Rand) < 50
BEGIN
	INSERT INTO
			@Rand
			(
			Number
			)
	SELECT
			FLOOR(RAND() * 8999999) + 1000000
	WHERE
			NOT EXISTS (SELECT * FROM @Rand WHERE Number = FLOOR(RAND() * 8999999) + 1000000)
END

SELECT * FROM @Rand

You'll notice, however, that when you do the select at the end, even though the 50 numbers are indeed random, they'll still most likely be in numerical order. If you want them randomly ordered, you should add an identity column to the table variable, and make it the primary key.

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Beginner - OLE DB - bind IRowSet to grid control

poptcat29-Jul-04 21:46

poptcat

29-Jul-04 21:46

Hi. I try to connect to a Microsoft SQL database using Ole Db - IRowSet interface. This works, but I can't bind it to a grid control (I use Microsoft Datagrid control).
I have:
IRowset* pRowset;
DDX_Control(pDX, IDC_DATAGRID1, m_grid);
........................................
I try to bind by
m_grid.SetRefDataSource((LPUNKNOWN)pRowset);
but it raises an exception : Type mismatch.
Well, I tried - I suppose . Could someone help me?
Gabi

Relation between tables

RoyRose7829-Jul-04 20:17

RoyRose78

29-Jul-04 20:17

Hello everyone,
I want to know how can I refresh a detail table that connects to a master table.
I have a relation between the tables, but I don't use datagrid for the master table (I have controls on the form (VB.NET) that represent the master table columns and datagrid that suppose to get the child rows of the detail table) but when I move the cursor's position, the detail datagrid won't seem to refresh itself.

How can I use the relation when I dont use datagrids..?

thank, Roy

Relation between tables

Sendilkumar.M30-Jul-04 19:13

Sendilkumar.M

30-Jul-04 19:13

Hi

You please make clear that you are usring Database Table or DataTable.

SenDIL

Re: Relation between tables

RoyRose7830-Jul-04 22:15

RoyRose78

30-Jul-04 22:15

Hey,
I use database tables.
The database is MSAccess.

Please recommend on connection to Ms Acess Database

DotNet29-Jul-04 15:16

DotNet

29-Jul-04 15:16

Hie...
Please recommend the site which related to Ms Access database.
I had enough of connection to Ms SQL Database.
I visit tons and tons of connection of SQL.
Is there any Access connection out there?
Hope to hear from you soon.
Thanks.

Regards,
Aaron

Re: Please recommend on connection to Ms Acess Database

EdbertP29-Jul-04 16:52

EdbertP

29-Jul-04 16:52

If you mean connection string, you can find it here http://www.connectionstrings.com/[^]

User access to SQL Server database

Colin Angus Mackay29-Jul-04 6:35

Colin Angus Mackay

29-Jul-04 6:35

I'm in the process of writing a stored procedure that copies information from one database to another. One of the pre-copy checks at the start of the stored procedure is to make sure that the logged in user has the right to create the tables and copy the data in the destination database.

How can I tell if the logged in user can access another database?

Also, I don't want it to raise an error while doing this. I currently have the following:

CREATE TABLE #temp (name sysname);
SET @stmt = 'USE ['+@destination_database+']; SELECT name from sysusers WHERE name='''+USER+'''';
INSERT #temp EXEC(@stmt);

My idea was to see if the #temp table contained any records, but obviously if the user has no permission to use the database this isn't going to work.

Any ideas?

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

Re: User access to SQL Server database

Grimolfr29-Jul-04 7:56

Grimolfr

29-Jul-04 7:56

From BOL:

"SCHEMATA
Contains one row for each database that has permissions for the current user. The INFORMATION_SCHEMA.SCHEMATA view is based on the sysdatabases, sysconfigures, and syscharsets system tables.

To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name."

The following SQL query should give you a list of all databases on the server that the current user has permissions in. Use a WHERE clause and possibly a COUNT(*) aggregate if you just want to know if the user has access to one particular database.

SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Re: User access to SQL Server database

Colin Angus Mackay29-Jul-04 22:37

Colin Angus Mackay

29-Jul-04 22:37

That doesn't work. Actually I find that the INFORMATION_SCHEMA is quite eccentric in what it returns (see end note).

When I run the statement you gave, SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA, I get a list of all the databases. This is not useful as it still list the database that I cannot access.

If I SELECT * FROM INFORMATION_SCHEMA.SCHEMATA it tells me that the schema owner is the same as the user logged in for a database that that user cannot access.

If I then issue a USE [p1_p2_destination_database] command I get the following:
916: Server user 'fails_2' is not a valid user in database 'p1_p2_destination_database'.

'fails_2' is the user that is not supposed to be able to access that particular database (These are part of the tests I am running to ensure the stored procedure that I am writing behaves correctly for a given situation). All 'fails_2' has in the server is access in the 'master' and 'p1_p2_source_database', it also owns a table in 'p1_p2_source_database'. It is correct that it issues an error if I try to USE the 'p1_p2_destination_database' but I want to trap that situation before it arises.

I don't know if this helps explain the situation more clearly.

As an aside the code for INFORMATION_SCHEMATA is:

--Identifies schmata owned by current users, databases current users has permissions in
create view INFORMATION_SCHEMA.SCHEMATA
 as
select
	db.name						as CATALOG_NAME
	,USER_NAME()				as SCHEMA_NAME
	,USER_NAME()				as SCHEMA_OWNER
	,convert(sysname, NULL)		as DEFAULT_CHARACTER_SET_CATALOG
	,convert(sysname, NULL)		as DEFAULT_CHARACTER_SET_SCHEMA
	,a_cha.name					as DEFAULT_CHARACTER_SET_NAME
FROM
	master.dbo.sysdatabases 		db,
	master.dbo.syscharsets		a_cha --charset/1001, not sortorder.
WHERE
	a_cha.type = 1001 --- type is charset
	AND a_cha.id = convert(tinyint, DatabasePropertyEx(db.name, 'sqlcharset'))

I don't see anywhere in this where filters anything by permission. If anything just returneing USER_NAME() for the SCHEMA_NAME and SCHEMA_OWNER is just pointless.

ODBC API's code examples?

PrashantJ28-Jul-04 21:54

PrashantJ

28-Jul-04 21:54

Hi,

I am writing an VC++ application which access SQL server/Oracle/MS-Access/excel etc using ODBC drivers.

I have some basic knowledge of ODBC API's like SQLOpen, SQLGetSchema etc but no clue of how to implement and get the data from the above mentioned databases into my VC++ application.

Can anybody help me out sending some links, code examples etc.

Thanks,
Prashant

xml to excel Help!

michanne128-Jul-04 12:07

michanne1

28-Jul-04 12:07

I have an xml datastream and would like to know the syntax to map the contents to a specific excel worksheet and xml map. This is to automate 30+ complex reports in excel (must be excel - and no iis available to make my job much easier!). I anyone has a better solution I welcome it.

This is my syntax so far:

Dim objConn As ADODB.Connection
Dim objCMD As ADODB.Command
Dim objResultStream As ADODB.Stream
Dim xmpCustomMap As XmlMap

On Error GoTo Handle_Error
Set xmpCustomMap = ActiveWorkbook.XmlMaps("triangle_Map")

Set objConn = New ADODB.Connection
objConn.Open "Provider=SQLOLEDB;server=mydb;Database=Development;" & _
"Integrated Security=SSPI"

Set objCMD = New ADODB.Command
Set objCMD.ActiveConnection = objConn

't-SQL DIALECT
objCMD.Dialect = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"

objCMD.CommandText = "Exec dbo.proc_xml 1394"

objCMD.CommandType = adCmdText

Set objResultStream = New ADODB.Stream
objResultStream.Open
objCMD.Properties("Output stream") = objResultStream
objCMD.Properties("XML Root") = "Root"

objCMD.Execute , , adExecuteStream

Debug.Print objResultStream.ReadText(adReadAll)

Create System Database form C# using ADOX

ulrich_v28-Jul-04 0:37

ulrich_v

28-Jul-04 0:37

I want to create a secure Access-Database from C#.
I want to create a Database and a Systemdatabase - I suppose this works only in the same time.

Why does the following code not work?

private void button1_Click(object sender, System.EventArgs e)<br />
{<br />
ADOX.Catalog catalog = new ADOX.Catalog();<br />
catalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\\new.mdb;Jet OLEDB:System Database=e:\\new.mdw;Jet OLEDB:Create System Database=true");<br />
}

- it creates the e:\new.mdb but fails to create the e:\new.mdw.

I use Visual Studio 2003 and I have tried this
- on WXP with installed Access2003 and
- on W2K with installed Access97 and Access2000

All with the same result. Dead | X|

Need help!!!

yanh10027-Jul-04 14:12

yanh100

27-Jul-04 14:12

I met a trouble to create a customized report against MS-Server OLAP in crystal report. The report what I want could not be described clearly here. If anyone of you is interested in it, I'd like to send you a sample of it by your email.

BTW, anyone knows how to attach a file on this board?

Thanks,

Yanh100
D'Oh! | :doh:

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.