Question

SQLConnect/SQLEXEC What is missing??

Asked by: jaymz69

I have it set up and have been using this for a month now on all sorts of prgs.

but all of a sudden my SQLExec(x,x,x) will not have any data (in my cursor)
it get a connection > 0 so I know I connect.
and my DNS has not changed.

Not sure where else to debug or check...

Any ideas?
Thanks

Local lcDNS
lcDNS = "xxxxxx"
Local lcUser
lcUser="xxxxx"
Local lcPWD
lcPWD="xxxxxx"
 
*SQL Handle
Local lnHandle
 
DO case
 
CASE ldToday = 2	&& if Monday date will need to be date()-3
 
 
LOCAL ldDate
ldDate = DATE()-3
 
OTHERWISE 			&& Itherwise all other days will be date()-1
ldDate = DATE()-1
 
ENDCASE
 
 
*-- Pull the data right through SQL Connect
lnHandle = SQLConnect(lcDNS, lcUser, lcPWD)
 
If lnHandle > 0 && success
 
*-- Fetch data from OOHEAD
	SQLExec(lnHandle,"select * from oohead where (ohviac='A' or ohviac='7' or ohviac='4')", "cOOhead")
*-- Fetch data from OOLINE
	SQLExec(lnHandle,"select * from ooline where olinvu='T'", "cOOline")
*-- Fetch data from CUSTMAST
	SQLExec(lnHandle,"select * from custmast", "cCustmast")
*-- Fetch data from ITEMMAST
	SQLExec(lnHandle,"select * from itemmast", "cItemmast")

                                  
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:

Select allOpen in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-10-09 at 07:57:05ID24799466
Topic

FoxPro Database

Participating Experts
3
Points
500
Comments
17

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. SQLExec
    Can I call another SQLExec if the first SQLExec return 0(still executing SQL) ? If yes, how can I know the result of the first SQLExec ? Andrew
  2. SQLEXEC problem
    My task is to connect to Ms.Sql 7.0 and run a simple query. Its weird because I am able to connect, and also sqlprepare. But when I isue SQLEXEC, I get the following error: 'SQL statement parameter is required for non-prepared SQLEXEC() calls.' It seems like sqlexec doent...
  3. sqlexec
    Can anyone tell me what is wrong with this line of code... = SQLEXEC(gnConnHandle, "UPDATE webtransact (itowengrav, itname, itbrand, itmodel, itdescrip, itdescrip2, farmtype, farmaction, farmgauge, farmfinish, farmbarrel, jewtype, jewmetal, jewkarat, jewweight, jewgende...
  4. sqlexec
    I'm preforming a web search on a sql database...the searchs i do currently only make an "exact match" match. basicly i want to use te set exact off command (search for "a" in a name field and have the sql server return all of the names containing the lette...
  5. odbc problem (sqlexec, sqlconnect)
    i want to insert information in my odbc table. What's wrong with this code? STORE SQLCONNECT('MSSQL_VAM') TO MyConnection SQLSETPROP(MyConnection, 'Transactions', 2) STORE SQLEXEC(MyConnection, "INSERT INTO reception (transport) VALUES (v_transport)") to test SQL...
  6. SQLEXEC() & SQLCONNECT() in FoxPro 6
    I am using a procedure with an SQL statement to populate a grid. The SQL statement looks to various Combo Boxes to establish what info should be on the grid. The table(s) that I am retrieving info from are FoxPro tables within the project's data environment. I would like ...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: jaymz69Posted on 2009-10-09 at 08:01:29ID: 25535713

It is like it is not connecting to the Library/DNS.

My remote views work...

 

by: pcelbaPosted on 2009-10-09 at 08:12:57ID: 25535837

You have to check for errors after each SQLEXEC (the code is sufficient for debug purposes not for real environment):

Local lcDNS
lcDNS = "xxxxxx"
Local lcUser
lcUser="xxxxx"
Local lcPWD
lcPWD="xxxxxx"
 
*SQL Handle
Local lnHandle
 
DO case
 
CASE ldToday = 2	&& if Monday date will need to be date()-3
 
 
LOCAL ldDate
ldDate = DATE()-3
 
OTHERWISE 			&& Itherwise all other days will be date()-1
ldDate = DATE()-1
 
ENDCASE
 
 
*-- Pull the data right through SQL Connect
lnHandle = SQLConnect(lcDNS, lcUser, lcPWD)
 
IF lnHandle < 0 
  WAIT WINDOW "Unable to connect"
ENDIF
 
LOCAL lnResult, laErr[1]
 
If lnHandle > 0 && success
 
*-- Fetch data from OOHEAD
lnResult = SQLExec(lnHandle,"select * from oohead where (ohviac='A' or ohviac='7' or ohviac='4')", "cOOhead")
IF lnResult < 0
  =AERROR(laErr)
  WAIT WINDOW "ERROR in select * from oohead"
  DISPLAY MEMORY LIKE laErr
ENDIF
*-- Fetch data from OOLINE
lnResult = SQLExec(lnHandle,"select * from ooline where olinvu='T'", "cOOline")
IF lnResult < 0
  =AERROR(laErr)
  WAIT WINDOW "ERROR in select * from ooline"
  DISPLAY MEMORY LIKE laErr
ENDIF
*-- Fetch data from CUSTMAST
lnResult = SQLExec(lnHandle,"select * from custmast", "cCustmast")
IF lnResult < 0
  =AERROR(laErr)
  WAIT WINDOW "ERROR in select * from custmast"
  DISPLAY MEMORY LIKE laErr
ENDIF
*-- Fetch data from ITEMMAST
lnResult = SQLExec(lnHandle,"select * from itemmast", "cItemmast")
IF lnResult < 0
  =AERROR(laErr)
  WAIT WINDOW "ERROR in select * from itemmast"
  DISPLAY MEMORY LIKE laErr
ENDIF
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:

Select allOpen in new window

 

by: pcelbaPosted on 2009-10-09 at 08:15:38ID: 25535865

Also even if it is defined in DNS you should select proper database on SQL:

lnResult = SQLEXEC(lnHandle, "USE YourDatabase")
IF lnResult < 0
  ...

 

by: jaymz69Posted on 2009-10-09 at 08:20:41ID: 25535913

nice I see the error. it keeps going to the Default Lib in the odbc. it doesn't go to the Lib List now like it used to...?

 

by: jaymz69Posted on 2009-10-09 at 08:22:33ID: 25535924

I declare it as a local lnDNS="GSxxx"

but something has changes somewhere. it only gets to the main Lib. QGP

 

by: tusharkanvindePosted on 2009-10-09 at 08:26:38ID: 25535970

You are not checking the value returned by SQLEXEC. If it is <0 then there is an error that has to be checked with AERROR() function

 

by: pcelbaPosted on 2009-10-09 at 08:32:44ID: 25536025

DNS definition is just a Registry entry and its change is very easy... So you should always check if you are querying right data after connection is established.

 

by: jaymz69Posted on 2009-10-09 at 08:47:09ID: 25536148

so what is the best then? I delcare all in variables ::: lnHandle = SQLConnect(lcDNS, lcUser, lcPWD)

then do the sqlexec() so your saying to do what else to verify the right data base? I see it goes the the main but somehow doesn't get to the lib in the AS400? my windows has the ODBC settings ok too.

 

by: pcelbaPosted on 2009-10-09 at 08:56:29ID: 25536228

It depends what is unstable in your configuration. If you are not always connected to the right database then you have to open the database explicitly by:

lnResult = SQLEXEC(lnHandle, "USE YourDatabase")

If you are not always connected to the right server then you have to check the server name after the connection:

lnResult = SQLEXEC(lnHandle, "SELECT @@ServerName", "cServer")  && Valid for SQL Server only

etc.

Maybe it would be better for you to build SQL connection string to a variable and use

lnHandle = SQLSTRINGCONNECT(lcConnString)

then you are always connected to to whatever you need without DNS definition. (http://www.connectionstrings.com/ contains connection string examples.)

 

by: jaymz69Posted on 2009-10-09 at 11:19:25ID: 25537431

I am not sure. nothing seems to work now?



I did no changes to my system...

 

by: jaymz69Posted on 2009-10-09 at 11:22:54ID: 25537457

I can make a remote view OK

but he sqlconnect() is > 0
then the sqlexec() goes to -1

 

by: Olaf_DoschkePosted on 2009-10-09 at 12:15:38ID: 25537951

Wll, pcelba showed you, how to get error info, when "sqlexec() goes to -1". What is displayed by DISPLAY MEMORY LIKE laErr after AERROR(laErr)?

Bye, Olaf.

 

by: jaymz69Posted on 2009-10-09 at 12:23:11ID: 25538020

This

I can't seem to figure anything out why this just happened a few days ago. no changes anywhere in the network either.. I know it can connect it just < 0 when the sqlexec() is done.

 

by: jaymz69Posted on 2009-10-09 at 12:34:36ID: 25538117

it shoes file OOHEAD not in QGPL.

OOHEAD is the table I need but it reside in the GSFL in the QGPL

my DNS in windows is setup as always too.

just not sure why it points to the QGPL when it needs to look in the GSFL. And I even have it in the lnDNS="GSFL"

 

by: pcelbaPosted on 2009-10-09 at 14:22:51ID: 25538985

So, this is definitely not FoxPro problem and probably not DNS problem (please check the DSN definition again). Somebody could change DB2 settings or access rights.

You have to present your select command and error message in DB2 zone (http://www.experts-exchange.com/Software/Database/DB2).

I've been checking solutions for this error message and they are not clear in exact solution, like e.g. this one:
"I had this problem on our development iSeries when I first got here.  The
library QSYS2 was not in the system part of the library list..........I have no
idea how or why, it's the way I inherited the system from the person I
replaced.  I changed the sys val QSYSLIBL to include QSYS2 and the problem went
away."  -- I would try same thing for GSFL but I have no knowledge of AS400.

How do you select the library in DB2? What do you mean by lnDNS="GSFL"? If it is just DSN name assignment than you have to look inside this DSN definition because the DSN name itself has no relation to your library.

DB2 expert would help better, of course.

 

by: jaymz69Posted on 2009-10-09 at 15:28:23ID: 25539396

OMG!

I change the defualt Lib (which is what it has been for years) to the other for one last resort and it worked!

I don't know why it work always before pointing to the main one and now it stopped...??

 

by: jaymz69Posted on 2009-10-09 at 15:28:55ID: 31639298

Thanks for all the tips

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...