Solved

SQL Connection Handle via MS Visual FoxPro

Posted on 2002-04-29
7
356 Views
Last Modified: 2006-11-17
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
Comment
Question by:blackjack020900
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Expert Comment

by:CJ_S
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.

CJ
0
 

Author Comment

by:blackjack020900
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.

thx,
regards
0
 
LVL 22

Expert Comment

by:CJ_S
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.

CJ

0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

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

thx,
regards
0
 
LVL 22

Accepted Solution

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

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
 
LVL 54

Expert Comment

by:nico5038
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Expert Comment

by:Jgould
ID: 7548134
Question has been closed as per recommendation

JGould-EE Moderator
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
This is about my first experience with programming Arduino.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Progress

615 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