Solved

check a Connection Handle with MS Visual FoxPro

Posted on 2002-04-29
7
3,770 Views
Last Modified: 2007-12-19
How can I check, if a DB connection handle is valid?

I got the error 1466 in the following syntax line:

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

My programm crashs in this line, if the handle is not valid.
0
Comment
Question by:blackjack020900
7 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6979755
? may be this

Visual FoxPro Reference  

Connection handle is invalid (Error 1466)
The connection handle supplied is invalid.

Use SQLCONNECT( ) or SQLSTRINGCONNECT( ) to generate a valid connection handle.

about sqlconnect:

Microsoft Visual FoxPro Language Reference  

SQLCONNECT( ) Function
Establishes a connection to a data source.

SQLCONNECT([DataSourceName, cUserID, cPassword | cConnectionName])
Return Values
Numeric

Parameters
DataSourceName
Specifies the name of a data source as defined in your Odbc.ini file.
cUserID
Specifies a user identifier used to log on to the data source.
cPassword
Specifies the password to the data source.
cConnectionName
Specifies a named connection created with CREATE CONNECTION.
Remarks
SQLCONNECT( ) returns a positive non-zero numeric handle if you successfully connect to the data source. You should store this handle in a memory variable and use the variable in subsequent function calls that require a connection handle. SQLCONNECT( ) returns  2 if the connection cannot be made.

If SQLCONNECT( ) is issued without any of its additional arguments, the Select Connection or Data Source dialog box can be displayed, allowing you to choose a data source.

Note   The ODBC login dialog must be disabled to support SQL pass through with Microsoft Transaction Server. Use SQLSETPROP(cConnectionHandle, 'DispLogin', 3) to disable the ODBC login dialog (cConnectionHandle is the connection handle returned by SQLCONNECT). The ODBC login dialog can also be disabled in the Connection Designer.
Example
The following example assumes an ODBC data source called MyFoxSQLNT is available, and the user ID for the data source is "sa." SQLCONNECT( ) is issued, and its return value is stored to a variable named gnConnHandle.

If you successfully connect to the data source, SQLCONNECT( ) returns a positive number, a dialog is displayed and SQLDISCONNECT( ) is used to disconnect from the data source.

If you cannot connect to the data source, SQLCONNECT( ) returns a negative number and a message is displayed.

STORE SQLCONNECT('MyFoxSQLNT', 'sa') TO gnConnHandle
IF gnConnHandle <= 0
   = MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
   = MESSAGEBOX('Connection made', 48, 'SQL Connect Message)
   = SQLDISCONNECT(gnConnHandle)
ENDIF
See Also
AERROR( ) | CREATE CONNECTION | SQLDISCONNECT( ) | SQLGETPROP( ) | SQLEXEC( ) | SQLSETPROP( ) | SQLSTRINGCONNECT( )


about sqlstringconnect:

Microsoft Visual FoxPro Language Reference  

SQLSTRINGCONNECT( ) Function
Establishes a connection to a data source through a connection string.

SQLSTRINGCONNECT([cConnectString])
Return Values
Numeric

Parameters
cConnectString
Specifies the data source connection string required by some ODBC drivers. Visual FoxPro passes the connection string to the ODBC driver. For more information about data source connection strings, see your ODBC driver documentation.
If SQLSTRINGCONNECT( ) is issued without cConnectString, the SQL Data Sources dialog box is displayed, making it possible for you to choose a data source.

Remarks
SQLSTRINGCONNECT( ) returns a positive non-zero numeric handle if you successfully connect to the data source. You should store this handle in a memory variable and use the variable in subsequent function calls that require a connection handle.

Example
The following example assumes an ODBC data source called MyFoxSQLNT is available, and the user ID for the data source is "sa," and the password is "FOXPRO." SQLSTRINGCONNECT( ) is issued, and its return value is stored to a variable named gnConnHandle.

If you successfully connect to the data source, SQLSTRINGCONNECT( ) returns a positive number, a dialog is displayed and SQLDISCONNECT( ) is used to disconnect from the data source.

If you cannot connect to the data source, SQLSTRINGCONNECT( ) returns a negative number and a message is displayed.

STORE SQLSTRINGCONNECT('dsn=MyFoxSQLNT;uid=sa;pwd=FOXPRO')
   TO gnConnHandle
IF gnConnHandle < 0
   = MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
ELSE
   = MESSAGEBOX('Connection made', 48, 'SQL Connect Message')
   = SQLDISCONNECT(gnConnHandle)
ENDIF
The following examples show how you can use the SQLStringConnect command without a Data Source Name (DSN).

lcDSNLess="driver = SQL Server;server=your_server;uid=sa;pwd=sa_pw"
-or-

lcDSNLess="driver = {SQL Server};server=your_server;uid=sa;pwd=sa_pw"
-or-

lcDSNLess="DRIVER = {SQL Server};" ;
+ "SERVER=your_server;" ;
+ "UID=your_server_userid;" ;
+ "PWD=your_server_pw;" ;
+ "DATABASE=PUBS;" ;
+ "WSID=your machine name or userid;" ;
+ "APP=MicroX(R) Sample App"

lnConnHandle=sqlstringconnect(m.lcDSNLess)
See Also
AERROR( ) | SQLCONNECT( ) | SQLDISCONNECT( )


foxpro errornumbers with description you will find at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fox7help/html/tocerror_messages_by_number.asp

hope this helps

meikl ;-)
0
 

Author Comment

by:blackjack020900
ID: 6979776
Thanks, but I have another problem.

e.g.
The user start the application (Multi- Tier- Application) and connect to the DB with her own handle.

- The Database Connection exists and works

If the user e.g. going to lunch for one hour and klick a button after lunch, the connection handle is invalid.

Now, I will check, if the handle is valid. If the handle is invalid, then I will take a new connect.

e.g
if nHandle = invalid
  connect to db
endif

regards
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6979790
well, don't know much about the foxpro language,
but you could try to retrieve the connection-propertys with
sqlgetprop(nhandle)
if this fails do a reconnect and try again
sqlgetprop(newhandle)
if this works go ahead otherwise throw an error

meikl ;-)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:blackjack020900
ID: 6979804
sorry, i have test it any days ago. Is the handle invalid, the SQLGETPROB function crashs too.

The problem is, I can't use the ON ERROR function!!!
-the application is developed with AFP (Active- FoxPro- Pages). The AFP use the ON ERROR function intern.

thanks a lot
regards
0
 

Expert Comment

by:lame_programmer
ID: 7005794
maybe you could set connectiontimeout to 0 and then you won't have this problem anymore..
good luck
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7265708

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:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7296580
Per recommendation, points NOT refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

9 Experts available now in Live!

Get 1:1 Help Now