Re: Grrrr -- Oracle OLE DB Driver - Database Discussion Boards

Nguyen Vu Cuong7-Apr-04 15:50

7-Apr-04 15:50

My problem has 2 workflows:
WF1:=>I'm OK<=With an active connection, the user can drag and drop fields in a table and I'm succesfully generate an SQL string.
WF2:=>I'm now in trouble<= I let the user type the SQL string in a richtextbox. And now I must display all the tables, the relation ships, the fields etc... specified in the SQL string typed by the user. Would you please give me some helps? I really need an effective algorithm to parse this SQL string. Thank you in advance

Re: Select SQL String

Steven Campbell13-Apr-04 9:17

Steven Campbell

13-Apr-04 9:17

I have written a basic SQL parser before, but it was parsing generated SQL, which was fairly tightly under my control anyway. I would not want to do it for SQL that the user typed! SQL can be very complex, with nested queries and multiple ways of writing the same query. Most typed queries will be simple though, so maybe you can get by with something basic, that works in 90% of cases.

Alternate solution 1: I would leverage what I could from SQL Server. In SQL Query Analyzer, you can look at the query plan for a particular query. This shows some of what you are interested in. Perhaps you could find out how that is done, and use a similar technique.

Alternate solution 2: SQL Enterprise and SQL Query analyzer both have a query designer tool that does something similar to what you want. It may be that there is an underlying API that you could utilize.

Alternate solution 3: Create a temporary view from the SQL, and then use various techniques to analyze that view. sp_depends will tell you the tables, sp_help will tell you the result structure. Not sure how you would tell the relationships though.

Re: Select SQL String

Nguyen Vu Cuong13-Apr-04 15:30

Nguyen Vu Cuong

13-Apr-04 15:30

Would you please tell me more about the Solution 2? It will be better if I can utilize some API, but which API? Can you show me an example using these API? And would you mind send me your project that you've written? Thank you.

Row lock versus Page lock in SQL 2000.

mittalpa7-Apr-04 8:32

mittalpa

7-Apr-04 8:32

Hi We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.

Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.

At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.

So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.

Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :

Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.

How can I resolve this issue? How can I get lock on wanted rows instead of entire page?

Please advice. Thanks a bunch.

Pankaj

Follow your goals, Means will follow you ---Gandhi---

Re: Row lock versus Page lock in SQL 2000.

Mike Dimmick8-Apr-04 2:32

Mike Dimmick

8-Apr-04 2:32

You can use WITH ( ROWLOCK ) after the table name in the FROM clause. However, you should consider whether you should be holding your transactions open for user interaction. It's usually better to select the results and release the locks, then perform comparisons to determine if the row has changed when the user wants to update. It's up to you what you do if another user has changed a row since this user started editing.

Stability. What an interesting concept. -- Chris Maunder

Re: Row lock versus Page lock in SQL 2000.

-Dr_X-15-Apr-04 16:51

-Dr_X-

15-Apr-04 16:51

I would recommend not using locks. What I do is use a time stamp column. During the update stored procedure, I do a check to make sure the date time stamp is equal to the actual value in the table for that record. If the time is the same, I update the column to the current date time prior to the actual update. If not, I raise an error, then requery the table information for the last change user and date time.

Again, I would not recommend do any type of locks.

Just my 2¢,

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Grrrr -- Oracle OLE DB Driver

Steve S7-Apr-04 5:27

Steve S

7-Apr-04 5:27

This might help someone else out; in any event, I wanted to get this off my chest.

Some s/w I've worked many, many hours on uses the ATL OLEDB classes to communicate with a database, which is specified by a connection string. When we use the SQLOLEDB provider against MSSQLServer, we have no problems (well, not with the database!). However, using OraOLEDB.ORACLE against Oracle 9.2 gives some really bizarre behaviour.

Normally, if you want an updateable rowset, you just specify (DBPROP_IRowsetChange, true) and (DBPROP_UPDATEABILITY, flags).
Open your rowset, call SetData() to update, and all goes well.

However, with Oracle, if your rowset contains blobs (long data), then you also need to specify (DBPROP_IRowsetUpdate,true), which *should* give you a deferred update rowset, meaning that you need to call SetData() and Update() to update the row. Now (a) you don't need to call Update(), just SetData works! and (b) this doesn't work for SQL Server Frown | :(

All in all, a sorry little mess.
Thankfully, it's possible to determine from a CSession object whether we're connecting to an ORACLE database or not, and thanks to the ability produce a template, the rowset property setup and updating of the data can also be hidden from the 'casual developer'. But Eek! | :eek:

isn't OLE DB intended to take care of this crud for us? Come back ODBC, all is forgiven....

Steve S

My, that feels better. What will feel even better, of course, is leaving this job on 30th April - now if only I had somewhere to go....

Re: Grrrr -- Oracle OLE DB Driver

RichardGrimmer19-Apr-04 5:19

RichardGrimmer

19-Apr-04 5:19

Ahhhhhh....Many Oracle problems you still have grasshopper....

Ah well - 30th not long to go....still nothing in pipeline?

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Default field value

Shenthil7-Apr-04 1:47

Shenthil

7-Apr-04 1:47

I am using ADO recordsets in my C++ program. I am adding a new record by calling AddNew method of recordset object. Now, I dont' want to override any default values set for a particular column while adding the records. How can find whether default values are set for a column, and if set, how can I get the default values. what are the methods available for that?

Thanks in advance

Shenthil

Create a Database Softwre

Ajit Jagannath6-Apr-04 5:27

Ajit Jagannath

6-Apr-04 5:27

Hi,

As part of a Project I have to create a Database Software, with MS Access as the Back end and use VB for the front end. I have to go right from the basics. Is there any good tutorials or documents available on the net that I could use. I would really appreciate if someone would pass it on.

Regards,
Ajit
"Insanity is my Birth Right!!!!"

Re: Create a Database Softwre

Hesham Amin6-Apr-04 21:35

Hesham Amin

6-Apr-04 21:35

this :
http://www.codeproject.com/useritems/databinding.asp[^]
is a very beginner article.
you can start with it
also check www.vbcode.com[^]

DAO Access and system.mdw

cr976-Apr-04 3:56

cr97

6-Apr-04 3:56

How can i open an access-file (database.mdb) with DAO using a special systemdb/workgroupfile (database_sys.mdw) ???

if i use ODBC i can defien a systemdb in de ODBC-connect-string ... but it doesn't work with DAO !!!

thx a lot

cr97

Re: DAO Access and system.mdw

Mike Ellison6-Apr-04 5:45

Mike Ellison

6-Apr-04 5:45

Hi cr97. Take a look at the following article on MSDN; the sample code you want is under the heading "Opening a Database with User-Level Security".

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate_topic4.asp[^]

Re: DAO Access and system.mdw

cr976-Apr-04 21:47

cr97

6-Apr-04 21:47

thanx ...

But how can i do that in C++ ???

i found this ...

void CDaoWorkspace::Create(LPCTSTR lpszName, LPCTSTR lpszUserName, LPCTSTR lpszPassword)
{
  ...

	// Get the DAODBEngine interface and initialize if necessary
	_AFX_DAO_STATE* pDaoState = AfxGetDaoState();
	if (pDaoState->m_pDAODBEngine == NULL)
		InitializeEngine();
  ...
}

and i know that DAODBEngine has the method ...

pDaoState->m_pDAODBEngine->put_SystemDB(THIS_BSTR SystemDBPath);

Can i call this like it is to change the SystemDB ???
And i can't use this AfxGetDaoState() ... it's unknown ...

mhg cr97

Re: DAO Access and system.mdw

Mike Ellison7-Apr-04 5:28

Mike Ellison

7-Apr-04 5:28

Would this article help?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vclib/html/_mfcnotes_tn054.asp[^]

Re: DAO Access and system.mdw

cr977-Apr-04 21:15

cr97

7-Apr-04 21:15

Yes i think it would help !!!

I found this article yesterday ...

thank you !!!

cr97

Multiple table select queries in mySQL

docdeath6-Apr-04 2:38

docdeath

6-Apr-04 2:38

Could someone help me construct an SQL query which will conditionally select records from multiple tables which share the same schema, without joining every compatible record onto the end of each (creating a total mess) like "select * from <table1>, <table2> where 1" does. Placing the name of the table as a field for each would be a bonus (Im using mySQL)

The database I have is quite unusual as it contains a lot of binary-serialised data which has been categorised into a lot of different tables (for instance one table for records beginning with A, B, C etc), for (I expect) high-performance and easier management. If there is any other tweak you can think of that'll allow better querying on a collection of similar tables, your input would be welcome.

How to join on multiple records?

matthias s.6-Apr-04 1:27

matthias s.

6-Apr-04 1:27

Hello,

I've got a table called samples. This table has three fields called PicID1, PicID2 and PicID3 for simplicity. My second table is called Pics. Pics has an ID field (key) and a PicName field. The select query I'd like to write selects all records from the Samples table and the PicName value for each of the PicIDs (1,2 and 3):

[Samples]
ID|PicID1|PicID2|PicID3
1|345|664|733

[Pics]
ID|PicName
..
345|Himalaya
664|Sahara
733|New York

The result should return a recordset containing all records from the samples table and "instead of the numeric PicID*" the Pics.PicName.

Can this be done in one query? If so, how?

Thanks in advance,

Matthias

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

Re: How to join on multiple records?

Mike Dimmick6-Apr-04 1:42

Mike Dimmick

6-Apr-04 1:42

SELECT Samples.ID, P1.PicName AS PicName1, P2.PicName AS PicName2, P3.PicName AS PicName3
FROM
   Samples
INNER JOIN
   Pics P1 ON Samples.PicID1 = P1.ID
INNER JOIN
   Pics P2 ON Samples.PicID2 = P2.ID
INNER JOIN
   Pics P3 ON Samples.PicID3 = P3.ID
-- insert WHERE clause here

Basically, all you have to do is to alias the repeated table name.

Stability. What an interesting concept. -- Chris Maunder

Re: How to join on multiple records?

matthias s.6-Apr-04 2:05

matthias s.

6-Apr-04 2:05

Thanks, that's exactly what I was looking for!

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

Re: How to join on multiple records?

Hesham Amin6-Apr-04 1:49

Hesham Amin

6-Apr-04 1:49

hi use subqueries.

Select Samples.ID
,(Select Pics.PicName where ID=Samples.PicID1) as FirstPic
,(Select Pics.PicName where ID=Samples.PicID2) as SecondPic 
,(Select Pics.PicName where ID=Samples.PicID3) as ThirdPic

but i prefere that you re-design the database and create a link table that holds the SampleId and the PicID

Table:Sample_Pics
SampleID | PicID
1 |1
1 |2
1 |3
2 |4
------------------
etc.
this will allow you to have a variable number of pics per sample not just 3.

Re: How to join on multiple records?

matthias s.6-Apr-04 2:07

matthias s.

6-Apr-04 2:07

I've been thinking about this, but if fact, there will never ever be a situation when there is more then three Smile | :)

Thanks anyway,

Matthias

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

SQL Server Procedure Output

SamAmman5-Apr-04 23:06

SamAmman

5-Apr-04 23:06

Hello everybody ...
Well, I have a problem in something with SQL Server 2000 ...
I want to write a procedure in stored procedure in SQL Server 2000, this stored procedure should output a variable holding the number of records in the table.

ex
select count(*) from tbl
where tblAccount = 2828;

I want this procedure to output the number of records retrieved, and i want to get it from ASP page. Actually i used a command object in ASP to execute my procedure, and with a recordset.

rs.open cmd

but how can i get the output from this procedure???? and how can i write like this procedure in SQL Server 2000 ????

Best Regards
Sam

Sam2004

Re: SQL Server Procedure Output

Mike Dimmick6-Apr-04 1:26

Mike Dimmick

6-Apr-04 1:26

If you've used SELECT and opened a Recordset, the results will be in the Recordset object. The recordset has the concept of the current row - the Value property of the Field objects in the Recordset's Fields collection will contain the retrieved value of that field in the current row.

Straight after opening, if any rows were retrieved, the Recordset object's current row will be the first row returned. If no rows were retrieved, the EOF property will be False.

Since you haven't given the field a name in the result set, you will only be able to access it by position, i.e. as rs.Fields(0). To give it a name, use

SELECT COUNT(*) AS RowCount
FROM tbl
WHERE tblAccount = 2828;

As for the procedure, the easiest way is to send the SQL explicitly to the server, by setting the command object's CommandText property. This isn't the best performing method, though; look up the documentation on stored procedures and parameters.

Stability. What an interesting concept. -- Chris Maunder

File space used in SQL2000

Hesham Amin5-Apr-04 1:34

Hesham Amin

5-Apr-04 1:34

Hi how to know the sapce used in the data file or log files ?
the sp_spaceused stored proc gets the space used by a database or object.But what i need is the space used by in each data or log file.
how to get this info ?

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