Label+MySQL (Newbie question)

[Yosua]

Hi all,
I'm stuck at my label printing.
I have 2 situations here.

1. If I remove those comment mark, the app will send "Data not found" even when I entered existed correct product id.

2. If I add those comment mark(which is current situation), the app will show me a label with empty data, and close itself in less than a second.

In my analysis, which is much likely to be wrong
The problem is in the code that should grab MySQL data. But I've test the query and it's ok.

So can anyone help me to show me show where I did wrong?

 

[Nifrabar]

Hi Yosua,
Possibly more help can be given if you post some code about what your doing.
-Bart

 

[JRB-Bldr]

If I remove those comment mark...
I add those comment mark...

Remove/Add comment marks from what?

Might it be possible for you to include the code you are referring to?

One method that I might typically use with something like this might be:

* --- Open VFP Database which has Connection Defined to 'Backend' Server ---
OPEN DATABASE MyDBC
SET DATABASE TO MyDBC

* --- Establish Connection To 'Backend' Server ---
nConnectionHandle = SQLCONNECT("BkEndSrvr")

* --- Create SQL Query String ---
cSQLCommand = "SELECT Fld1, Fld2";
  + " FROM BkEndTable";
  + " WHERE ChkFld1 = 'ThisValue'"

* --- Execute 'Backend' Query ---
* --- If Successful, put record set into VFP Cursor 'Result' ---
nRet = SQLEXEC(nConnectionHandle, cSQLCommand, 'Result')

IF nRet = 1
   SELECT Result
   <do whatever>
   * --- Print Label(s) ---
   LABEL FORM MyLabel NOCONSOLE TO PRINT
ENDIF

* --- Close 'Connection' To 'Backend' ---
=SQLDISCONNECT(0)

Good Luck,
JRB-Bldr

 

[Yosua]

Oops sorry guys, I forgot to paste this

http://pastebin.com/aNp9QJH6

 

[Mike Lewis]

Yosua,

Could you please include your code in this thread. It will be easier for us to figure things out.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Yosua]

Hi Mike, I've put the code in

http://pastebin.com/aNp9QJH6

 

[Dan Freeman]

You're clearly getting an error on the IF EXISTS() thing, which makes no sense at all anyway. Take it out. Run the query and deal with the results.

After you fix that, you can start debugging.

I suspect your CREATE CURSOR command is using the wrong cursor name. You're doing INSERT INTO a cursor you haven't created, and then running LABEL FORM on a different alias entirely.

 

[Yosua]

Thanks Dan, I'll try to fix it

 

[Olaf Doschke]

The code you run uncommented contains a nonvalid query:
select id_product tb_product where id_product=?pcode

FROM is missing.

You should always do as the code you commented, and check the rertun value of SQLEXEC(), if it is not >0 this can mean an error (0 can also mean it is still executing, if connecting asynchronous).

And AERROR() then would tell you the sql is wrong and the FROM claus eis missing.

Bye, Olaf.

 

[JRB-Bldr]

I've put the code...

If you make it difficult for us to help you by putting your code somewhere elsewhere we are less likely to want to help you out.

Make it EASY for us to help you by not making us have to GO ELSEWHERE to look it over.

Instead we want you to put the code (just the specific part that is problematic) directly into your question that you are posting so that we can read it easily and comment on it.

Good Luck,
JRB-Bldr

 

[Yosua]

Sorry jrbbldr,
Here is the code

PUBLIC con
constring="DRIVER={MySQL ODBC 3.51 Driver};" ;
+ "SERVER=localhost;" ;
+ "USER=root;" ;
+ "PASSWORD=pass;" ;
+ "DATABASE=dbname"

con = SQLSTRINGCONNECT(constring)

SET PATH TO C:\APP\REPORTS
pcode=ALLTRIM(thisform.pgfproduct.pg_detail.txt_productcode.Value)
pqty=ALLTRIM(thisform.pgfproduct.pg_detail.txt_quantity.Value)

&&curcheck = SQLEXEC(con,"IF EXISTS(select * from tb_product WHERE id_product =?pcode)")
&&IF curcheck > 0
CREATE CURSOR BcProduct (id_product C(16))
SQLEXEC(con,"select id_product from tb_product where id_product=?pcode)","BcProduct")
SELECT BcProduct
SCAN
FOR lnI = 1 TO pqty
INSERT INTO BcLabel ;
VALUES(BcProduct.id_product)
ENDFOR
ENDSCAN
LABEL FORM label_idproduct.lbx preview
&&ELSE
&&MESSAGEBOX('Data not found, ',64,'Data not found')
&&ENDIF

 

[JRB-Bldr]

My suggestion would be to build the ENTIRE SQL Query command string outside of the SQLEXEC() command and populate the variable BEFORE attempting to execute the query.

So instead of:

SQLEXEC(con,"select id_product from tb_product where id_product=?pcode)","BcProduct")

You would have something like:

pcode=ALLTRIM(thisform.pgfproduct.pg_detail.txt_productcode.Value)

* --- Create SQL Query Command String ---
cSQLCommand = "select id_product";
    + " from tb_product ";
    + " where id_product=" + pcode

* --- Execute Command via Connection ---
nRet = SQLEXEC(con,cSQLCommand,"BcProduct")

* --- Check If Good Execution (nRet = 1) ---
IF nRet = 1
   SELECT BcProduct
   <do whatever>
ENDIF

Also you did not need to CREATE CURSOR BcProduct since the SQLEXEC() will do that for you with the results of your query (if successful).

A last, more general, suggestion...
Always manually run the intended SQL Query Command string into the data source without using VFP at all. That will allow you to check the syntax used in the SQL string.
Remember that the syntax within a SQLEXEC() must match the syntax of the SQLEXEC()'s data source 'language' (in your case MySQL). That syntax might not be the same as VFP's SQL syntax.

Good Luck,
JRB-Bldr

 

[Yosua]

Many thanks JRB-Bldr, I'll remember that

 

[JRB-Bldr]

one example of: That SQL Query syntax might not be the same as VFP's SQL syntax. is the command ALLTRIM().

In many (most??) other 'alien' database languages, it does not exist - so an attempt to execute it within a SQLEXEC() would fail.

Good Luck,
JRB-Bldr