SQL Connection Handle via MS Visual FoxPro

Posted on 2002-04-29
Last Modified: 2006-11-17
How can I check, if a connection handle is valid.

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.

Question by:blackjack020900
LVL 22

Expert Comment

ID: 6977282
I suppose you have the connection open for too long, and thus closes.

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


Author Comment

ID: 6977356
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.

LVL 22

Expert Comment

ID: 6977378
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.


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.


Author Comment

ID: 6977448
How can I create a component like this?

LVL 22

Accepted Solution

CJ_S earned 300 total points
ID: 6977573
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.

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...

LVL 54

Expert Comment

ID: 7515066
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.



Expert Comment

ID: 7548134
Question has been closed as per recommendation

JGould-EE Moderator

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question