Getting SQLCONNECT to work?

[jortego]

I am new to FoxPro and still trying to some of the basics.

I am trying to connect to a FoxPro table from within a FoxPro application (VFP 6). I am doing this so that I can use the SQLEXEC() function to execute an SQL String that was built with a bunch of Logic statements.

STORE SQLCONNECT('db_data') TO gnConnHandle
SQLEXEC(gnConnHandle, str_sql, 'MyCursor')
SQLDISCONNECT(gnConnHandle)

//db_data is my generic text FoxPro database.
//str_sql is my Characeter String Variable with my SQL statement.

This worked once perfectly. However, when I re-opened the form that was giving this command, I would get an error stating "Connection handle is invalid". I modified the SQLCONNECT statement to have the full path for the connection including the .dbc at the end. This worked well until I closed and re-opened the form. Then I got the same error as above.

Any Ideas as to why this I am getting this result?

 

[satyenshanker]

Try giving the login and password along with the DSN name
STORE SQLCONNECT('db_data', 'sa', "&quot TO gnConnHandle


Satyen

 

[jortego]

I made the change and got the same result. It worked at first. What I did figure out is that the code will keep working until I make any modification to the procedure that I am calling. The above suggestion was working just fine until I added a comment line. I deleted the line, but the code would no longer work. I even tried rewriting the code to the earlier version but I continued to get the Connection Handle error.

So, I am still in the same position.

 

[satyenshanker]

Does any other SQL statement work?

Satyen

 

[jortego]

If I put an SQL statement like "SELECT * From...etc." in it works fine. I am only connecting to Foxpro tables within the same application.

 

[satyenshanker]

Sorry, I was off on a vacation so I could not get back to you in time. Can you post the SQL statement that you are trying.

Satyen

 

[KirkKelly]

*Program: ctConnect
*Returns: Int ConnHandle
*Parameters tcServerName
public gcWinDir,gcSysDir
Local lnConnHandle

= SQLSetProp(0, "displogin", 3)
lcDSNLess=[DRIVER={SQL SERVER};SERVER=]+ALLTRIM(tcServerName)+[;UID=ctore;pwd=passwordhere;Network=DBMSSOCN;database=CTORE;]

lnConnHandle=SQLSTRINGCONNECT(lcDSNless)

if lnConnHandle < 0
=SQLERROR()
else
= SQLSETPROP(gnConnHandle, 'BatchMode', .T.)
endif
return lnConnHandle

endif
= SQLSETPROP(gnConnHandle, 'BatchMode', .T.)

 

[daleoclarke]

Hey jortego. Try this.

SQLCONNECT() is expecting a ODBC DSN as its parameter, not a file name.

1. Create a ODBC DSN (Control Panel - Data Sources) pointing to your &quot;db_data&quot; database

2. Supply that DSN name to the SQLCONNECT(&quot;dsn_name&quot command.
So instead of &quot;STORE SQLCONNECT('db_data') TO gnConnHandle&quot;

type &quot;STORE SQLCONNECT(&quot;dsn_name&quot TO gnConnHandle&quot;

That should solve your problem.

 

[jortego]

Here are several replies to different postings on my question:

satyenshanker

Here is the code:

*This code is executed when a Button is pressed on the form.

do Populate_Speci_find_list with Type_ID_Var, Pat_ID_Var

THISFORM.Freez_Location_Grid.recordsource = ''
THISFORM.Freez_Location_Grid.recordsource = &quot;MyCursor&quot;

- - - - - - - - -
*This is the procedure that it is executing found in a .prg file under the same project. The tables referenced in my str_sql variable are located in the db_data database. If I put the SQL statement in the code without the SQLEXEC() command, it works fine. But I intend to embend some more complex logic into this code and I need it to be an appendable string variable.

PROCEDURE Populate_Speci_find_list

PARAMETERS Type_ID_Var, Pat_ID_Var

LOCAL str_sql
LOCAL cWhere_str

str_sql = &quot;SELECT * FROM speci_data!lab_frz&quot; ;+
&quot;RIGHT OUTER JOIN speci_data!lb_inv_stg &quot; +;
&quot;LEFT OUTER JOIN speci_data!lab_speci &quot; +;
&quot;ON Lab_speci.oid = Lb_inv_stg.speci_oid &quot; +;
&quot;ON Lab_frz.oid = Lb_inv_stg.frz_oid &quot; +;
&quot;WHERE Lab_speci.pt_oid = Pat_ID_Var &quot; +;
&quot;AND Lab_speci.speci_id = Type_ID_Var &quot; +;
&quot;ORDER BY Lab_speci.oid &quot;

STORE SQLCONNECT('db_data') TO nConnHandle
= SQLEXEC(nConnHandle, str_sql, 'MyCursor')
= SQLDISCONNECT(nConnHandle)

- - - - -
KirkKelly

My connection handle is comming back as a -1. With an Error number of 3. According to help, the error is &quot;Character. The text of the OLE error message.&quot; I am not sure what that means.

- - - - -
daleoclarke


I tried the solution of making a DSN through the connection designer as well as just through the ODBC DSN. The connection designer tells me that the connection is made, but when I run the code, I get the &quot;Invalid Handler&quot; error. I tried removing the .dbc file from my project (.pjx file). That yeilded the same results. I am convinced that I am missing some small setting, command or syntax, but I have no idea what that could be.

...or is it possible that my previous attempts have corrupted the .dbc file?