• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

SQL Connection Handle via MS Visual FoxPro

How can I check, if a connection handle is valid.

e.g.
I would like to send a SQL- Query to the database with the following syntax:

sqlexec(nHandle, "Select * from table", Cursor)

Sometimes I got the error no. 1466 in the line which is mentioned above.

regards
0
blackjack020900
Asked:
blackjack020900
1 Solution
 
CJ_SCommented:
I suppose you have the connection open for too long, and thus closes.

Try to do an SQLConnect or SQLStringConnect just before calling sqlexec.

CJ
0
 
blackjack020900Author Commented:
You're right with your comment, but I can not open a new DB conectivity for every SQL- Statement and user. This is a multi- tier- application with 2500 users. One user executes similary 80 or more SQL- Statements per minute.
This means 80 or more open DB connections per user.

Your suggestion is not workable for us.

thx,
regards
0
 
CJ_SCommented:
Ultimately you would want only 1 connection to the database to be open. I suggest you create a component, hang it in Microsoft Transaction Server and let other users instantiate that object. That way you will have only one database connection (inside the component).

Next to that is that MTS can take care of your database connections.

If every client you have has a seperate connection to the database you do not have a good multi-tier application.

Personally I thinkt he above will solve your problem.

CJ

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
blackjack020900Author Commented:
How can I create a component like this?

thx,
regards
0
 
CJ_SCommented:
Most will be done automatically for you.

For example you create an ActiveX DLL within Visual Basic. That component contaisn functions which does all your database functionalities.

Like:
Public Function CreateCustomer(ByVal sCustomername as string) as boolean
   sSQL = "Insert into mytable (CustomerName) VALUES('" & Replace(sCustomerName, "'", "''") & "')"
   Conn.Execute sSQL
   CreateCustomer = true
End Function

Once you have all your function(s)(ality) set up you compile the DLL. You register it within Microsoft Transaction Server and export that package. That package should then be run on all your client machines (I recommend a batch for so many users).

Then within Foxpro you have to create the component like you normally do (if you are familiar with it). I'm not too familiar with Foxpro but with visual basic you'd write: Set x = getObject("", "THENAMEOFTHEPROJECT.THENAMEOFTHECOMPONENT").

Now you should be done...

of course the above is a very simple example, but it does do what you want to achieve. When you start programming you'd better have a design AND know how many users will be accessing your product at the same time.

If the above sounds too much of a problem to you (ie. you cannot handle it) then I have no idea what else you can do to soklve your problem but to do an SQLConnect just before you actually fire a statement.

Best option in your case might be to connect, fire all your statements after each other and then disconnect. it is the safest...

CJ
0
 
nico5038Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: CJ_S
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
JgouldCommented:
Question has been closed as per recommendation

JGould-EE Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now