Application is still running or a crash occured - Database Discussion Boards

As far as I know, there is only one way to do this--MySQL 4 supports embedding. You would have to embed the MySQL server in your app. It sounds like a pain to me, but I haven't looked at it except to know that it exists. Wink | ;-)

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

Hi,

Thanks for the tip ! So MSDE is a database engine that you can use in your desktop application ? Is it VC++ 6 compliant ? Where can I download it ? I can't find it on MSDN (http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp).

Regards,
JM. Molina

Web: http://goa.ifrance.com

There is a managed MySql data provider at Sourceforge.net. It probably performs better than using odbc. Check it out

Hi try this site if you miss a connection string !!
www.connectionstrings.com Smile | :)

I have this code which reads in a single record from my table..
Dim conSQL As New SqlConnection(strConn)
currentrow = Me.DataGrid1.CurrentRowIndex
currentcol = Me.DataGrid1.Text
Me.DataGrid1.Select(currentrow)
'Define and get the Orders Master
Dim cmdSQL As New SqlCommand("SELECT * FROM Orders WHERE OrderID ='" & (CType(DataGrid1(currentrow, 0), String)) & "'", conSQL)
Dim Rdr As SqlDataReader
conSQL.Open()
Rdr = cmdSQL.ExecuteReader()
Rdr.Read()
TextBox1.Text = (CType(DataGrid1(currentrow, 0), String))
ioOrderID.Text = (Rdr("OrderID")).ToString
ioCustomerID.Text = (Rdr("CustomerID")).ToString
conSQL.Close()
(more code and more output -- and a change of CustomerID takes place)....

I need to UPDATE that Customerid back to my Orders file now..

How do I do this...

Gollnick

Hey, need to know if there is a way to query MS Access, if any columns in a given table are Auto-Increment, maybe using the OleDb Schema or any way at all. My only idea was to check each column in the table if it had a data type of Int64, which is a long in Access, which is what Auto-Increment uses, but this is not a very resonable way to do so.
Thanks in advance for any help.

I write my ODBC-driver which should work in ms access. But... Here's the log of the driver with my comments:
access loads driver
SQLAllocEnv
SQLSetEnvAttr: Attr = 200 - access sets ODBC version 3
SQLAllocConnect
SQLGetInfo: InfoType = 77 - gets ODBC version, wich driver supports
SQLSetConnectOption
SQLGetConnectAttr: Attribute = 30002 - don't know what is it by now Frown | :(

SQLSetConnectAttr: Attribute = 30002
SQLDriverConnect - connecting to the database server, all's ok
SQLGetFunctions: fFunction = 999
SQLGetInfo: InfoType = 23
SQLGetInfo: InfoType = 24
SQLGetInfo: InfoType = 9
SQLGetInfo: InfoType = 6 - access asks the name of driver dll and then refuses to work farther, error -7778 no special message Cry | :((

(
SQLDisconnect
SQLFreeConnect
SQLFreeEnv

May be someone knows what can it be or at least the way to understand what is the problem?

konst

Hello,

Application
-----------
I've created a multi-tier application. Its architecture is:
On client machine --> the client application is an ActiveX EXE component (VBasic).
On server machine --> some COM+ server applications and a SQLServer database.

Use Case
--------
CLIENT1
--> asks SQLServer database for an object (= a row in objects table).
The state of this object is stored in a storage file.
--> if the object can be check out, it means nobody else have checked out the object,
the server will set the objects.checkedoutby = ClientID and will copy the stg file
in a shared folder on server machine.
CLIENT2
--> tries to check out the same object
--> the server must know (??? - MY QUESTION IS ABOUT THIS POINT) if the CLIENT1 application
is still running or a crash has occured and CLIENT1 was not able to check in the object.

Remarks
-------
1) CLIENT2 will receive a copy of the storage file if CLIENT1 is still running
2) CLIENT2 will have the possibility to restore the storage file from
an older version if a crash occured in the CLIENT1 application.
3) Many clients can log in using the same account (user name and password)
4) There can be more than one instances of the application on the same client machine

My solution
-----------
My solution is to create an instance of an "watchdog" ActiveX EXE on the client machine
when the client application is started. This "watchdog", from time to time (5 minutes by example),
will modify the value of a date field named NotificationDate in database.
When the client application shuts down, the "watchdog" will stop updating that field.
The stored procedure responsible for check out will use the value of NotificationDate field
to decide whether the CLIENT1 application that use this object is still running or a crash has occured.

Questions
---------
1) Is there a pattern for this problem?
2) What about my solution?

10x,
Ovidiu

I have used a different method successfull in the past.

The database table has the following columns tacked on the end:
LeaseUserId Varchar(10)
LeaseExpiryTime Datetime

Whenever a user wants to do an update, I set the LeaseExpiryTime to a time that is five minutes in the future. The update stored procedure is designed to raise an error if the LeaseUserId is not the current user, or if the LeaseExpiryTime has passed (cos the user doesn't "own" the lease any more). The client may explicitely extend the lease (if the user is taking a long time entering their information).

This mechanism is similar to the method that most server operating systems use to "lock" files.

Hope this is helpful.
Andy

Thanks Andy for your suggestion.

Notes:
1. The clients read the storage file in order to create some SELECT-SQL statements for retrieving data from SQL Server database - only read, no update. The SELECT-SQL statements are passed to a COM+ component, it execute the statement and send back to client a disconnected recordset.
2. ONLY the storage file is updated. When the user press <save>, the storage file from DataBase is updated.
3. In a real scenario, an object (a row of table objects) can be used for a long period of time (many hours); the user save the storage file from time to time.

10x,
Ovidiu

i'm using a stored procedure in sql server 2000 to insert records to a tabel in a database its code is :

ALTER PROCEDURE NewLicense
@LicenseOwner VARCHAR(20),
@StartDate DATETIME OUTPUT,
@EndDate DateTime OUTPUT,
@LicenseID bigint OUTPUT

As INSERT INTO LicenseInfo (LicenseOwner) VALUES (@LicenseOwner)

SET @LicenseID = SCOPE_IDENTITY()
SET @StartDate = GETDATE()
SET @EndDate = GETDATE()

the problem is that it returns me corrct values in the output parameters but
it put NULL values in the tabel in the database , I want to know where is the problem?

Alpha

There's nothing wrong with your stored procedure syntax.

I have a few questions:
- How are you calling the stored procedure?
- How is your table (LicenseInfo) defined?
- Does the table have a trigger / constraint that might change the value or prevent the value from being inserted?

You could try to run a trace to see what what actually happens.

first:
I'm calling the procedure from a c# code using SqlCommand object

Second :
the tabel design is the same of the design of the parameters in the procedure

Third:
i didn't put any default values for the StartDate and EndDate when Designing the tabel
the question now is :
does the SET Command modify in the tabel as it modifies in the parameter value?

No, the set command will only modify the variables. The data in the table will not be modified.

<br />
INSERT INTO LicenseInfo (LicenseOwner) VALUES (@LicenseOwner)<br />
<br />
SET @LicenseID = SCOPE_IDENTITY()<br />
SET @StartDate = GETDATE()<br />
SET @EndDate = GETDATE()<br />

In the above code, the only value that will get set in the new row in the table is the LicenseOwner. The other values in the table will get the default values (or NULL if no default is set).

To update the table values, you need to pass them into the table. Now, I'm assuming that LicenseID is an identity field, so that will be automatically set. This is how I would implement the procedure:

<br />
declare @MyDate datetime<br />
SET @MyDate = GETDATE() -- this will allow us to have one unique date instead of calling getdate() twice <br />
SET @StartDate = @MyDate<br />
SET @EndDate = @MyDate<br />
<br />
INSERT INTO LicenseInfo (LicenseOwner, StartDate, EndDate) <br />
VALUES (@LicenseOwner, @StartDate, @EndDate)<br />
<br />
SET @LicenseID = @@identity<br />

Hope this helps.

Thanks ,
I see thar this is the optimal solution for the problem

I am experimenting with MSDE and I've found that one long running query on one table causes other (unrelated, different tables, different machines querying) queries to hang waiting for the first query to finish. Is this a "feature" of msde or is there some settings that control how time slices are divvied up for more balance?

(Note: there is *nothing* in common between the two queries except the MSDE itself, they are different databases queried from different machines)

It is not clear from you post if the queries execute against different databases. If they don't you may like to consider the following (if, indeed you haven't already).

Running two consecutive queries will cause some slowdown, due to the server having to perform additional work. I would make sure that your long running query is not obtaining any locks that may prevent the other query from running. If your queries perform any joins you may find that the joins cause locks.

I also remember reading somewhere that SQL Server/MSDE will escalate locks. Perhaps you are getting page locks that are causing the second query to block, as it has data on the same page.

Have you tried moving the database(s) to a SQL Server (not MSDE) to see if you still get the same problem?

With regards to the performance of MSDE, it will slow down if you have more than '5 concurrent batch workloads'. have a look at the performance section in this page for further information.
http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

I hope this helps.

Regards

Mark Smithson

Yup, different databases entirely. That's what I found wierd. It's a simple thing, two queries on two different databases from two different computers.

I'll have a look at that info, thanks.

"Things are more like they are now than they ever were before."
-- Dwight Eisenhower

What do your queries look like? If you are using "Select * Into #TempTable" then I seem to remember that the system catelog tables in the Master database get locked for the duration of the query (unfortunately all of my tuning books are at work).

Also, what isolation level are you running the queries at (Commited Read, Repeatable Read, etc.)?

Regards
Andy

Hello, thanks for the tips, but discovered it was an index problem (as in missing a critical index).

MSDE / SQL server sure is dependant on those indexes. The equivalent problem with the exact same sized table and same query in Access would still not have degraded that far. Of course SQL server is much faster than Access once the index is there, but it's interesting how dependant it is on them for performance and how badly performance degrades if a critical one is missing.

"Things are more like they are now than they ever were before."
-- Dwight Eisenhower

Greetings,

I have never used triggers in SQL, and I think they could help me with a project I am working on.
So here is my question:

1.)I have 2 testing tables setup with the folowing information to just get an understanding...
(ie) MyTableA, MyTableB

MyTableA Fields
---------------
Id (Auto-number primary key).
ItemTrackNumA (used for tracking the item in table2)
TestValue (used for storing a value).

MyTableB Fields
===============
Id (Auto-number primary key)
ItemTrackNumB (used for tracking items)
LastValue (used for storing last value used)

2.) No every time I insert a value into MyTableA I want it to either add a new record in MyTableB (if
ItemTrackB is not found in the table MyTableB),or adjust the LastValue in MyTableB if ItemTrackB is
the same as ItemTrackA.

(ie) INSERT INTO MyTableA(ItemTrackNumA, TestValue)
VALUES (3, 100)

Would then perform a lookup in MyTableB and if the ItemTrackB was equal(or found) to ItemTrackA
it would just replace LastValue in MyTableB with the TestValue from MyTableA.

In this case LastValue would not have the 100 in it.

And if the ItemTrackB wasn't found than a new record would just be inserted.

I am thinking triggers would work great for this but I have so little experiance with SQL triggers that
any suggestions would be appreciated. Smile | :)

Thanks in advance!!

It sounds like you are hoping to inhibit the insert in TableA depending upon whether some data exists in TableB. I don't believe this can be done with triggers. Typically you can define the trigger to be pre and/or post insert, but even in the pre-insert situation I don't think you can stop the insert from occurring.

Chris Meech

"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.

"Microsoft hasn't ever enforced its patents. Apparently they keep them for defensive reasons only. Or, they could be waiting 'til they have a critical mass of patents, enforce them all at once and win the game of Risk that they're playing with the world." Chris Sells Feb 18, 2003.

Hello Chris,

That is a good point about the triggers, but in SQL server 2000 (7.0, ect) there is an available
call "INSTEAD OF", which seems to allow the ability to not have to do the insert but the problem I am
having now is accessing the params.

(ie) A simple section of code that I am checking the value to see if it is already in the table.

CREATE TRIGGER MyTrigger ON MyTableA
INSTEAD OF INSERT
AS
IF (SELECT COUNT(*)FROM MyTableA
WHERE MyTableA.Number1 = inserted.Number1) = 0
BEGIN
[...Do something...]
END
ELSE
[...Insert the new item into the database using INSERT INTO]

But the problem is "inserted.Number1" keeps saying it is undefined, and I am not sure how to get access
to the stuff that is to be inserted. I pulled the "inserted" from SQL Books online help, and I tried to
follow what they are doing, but no luck. Frown | :(

Not sure if this will help but in Oracle, you have pre-defined two variables called 'new' and 'old'. The use of them is like so

create or replace trigger transaction_create_date
  before insert on transaction_table
  for each row

begin

  :new.create_date        :=  sysdate;
  :new.modification_date  :=  :new.create_date;

...

Chris Meech

"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.

"Microsoft hasn't ever enforced its patents. Apparently they keep them for defensive reasons only. Or, they could be waiting 'til they have a critical mass of patents, enforce them all at once and win the game of Risk that they're playing with the world." Chris Sells Feb 18, 2003.

That is a cool feature of oracle! Thanks for the tip! Smile | :)

I did finally get it working by doing the following.
I can now check to see if any of the records have that number before it is inserted.

(ie)
CREATE TRIGGER My1stTrigger ON MyTableA
INSTEAD OF INSERT
AS
IF (SELECT COUNT(*) FROM MyTableA WHERE MyTableA.Number1 = (SELECT Number1 FROM inserted)) = 0
BEGIN
INSERT INTO MyTableA SELECT Id, Number1 FROM inserted
END
ELSE
[... Do something ...]