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.


Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.


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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
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.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now