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.


PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
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…

770 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