SQLEXEC for INSERT at MySQL

[paispatin]

Dear all,

Is that something I have to do for using SQLEXEC() for insert record at MySQL from form text?

I have a input from form.text:
xdate="2004-06-28"
xcode="ABC1"

Than, I have to save xdate & xcode at MySQL with :

NumRows = SQLEXEC(myConnection, "INSERT INTO mydata (date,code) WITH ('"+xdate+"','"+xcode+"')","cursor1")


But, the problem is sometime I can save that value to MySQL but sometime the value not save at MySQL. So is that something I have to do that I miss while using INSERT at SQLEXEC?

Because I think if I got wrong at SQLEXEC statement, it will not work from begining, right?

I also already try this program at 2 different computer, and the MySQL also install at localhost, but I still get the same problem.

Maybe someone could give reason for my problem. I use VFP 8

Thank you in advance.

NumRows = SQLEXEC(myConnection, "select mbarang.kdbrg,mbarang.namabrg,mjenis.namajenis,;

 

[Mike Lewis]

Paipatin,

Your code looks OK at first glance (apart from that fact that you don't need the "cursor1" parameter, but that won't affect the result).

Are you sure that the command is not working? The reason I ask is that you are returning the result of SQLEXEC() into a variable called NumRows. If you think that SQLEXEC() returns the number of rows, you are mistaken. It returns the number of result sets, which will always be zero in this case.

If there is a problem with the command, SQLEXEC() will return a negative number. You can find what the problem is as follows:

NumRows = SQLEXEC(myConnection, "INSERT INTO mydata (date,code) WITH ('"+xdate+"','"+xcode+"')","cursor1")
IF NumRows < 0
AERROR(ErrorArray)
MESSAGEBOX(ErrorArray(2))
ENDIF

Perhaps you could try that and report back.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[paispatin]

Mike, yes you right, I don't even know where is the problem. But now I will try your statement to find what make it wrong. I will write again the result for it to ask for more.

Thank you in advance

 

[tatin]

I hope someone else can benefit from this. You have to use MySQL's SQL syntax correctly. And you need to use the ? to pass variables from application side to Server in SQL Pass Through.

m.xDate = xDate
m.xCode = xCode
NumRows = SQLEXEC(myConnection, "INSERT INTO mydata (date,code) VALUES(?m.xDate,?m.xCode)","cursor1")
IF NumRows < 0
  AERROR(ErrorArray)
  MESSAGEBOX(ErrorArray(2))
ENDIF

 

[Mike Lewis]

Tatin,

You have to use MySQL's SQL syntax correctly

Yes, that's generally true with SQL pass-through. The whole point is that the SQL is executed on the server, so the syntax must be appropriate for the specific back end.

you need to use the ? to pass variables

Also true, although your original approach -- constructing the SQL and inserting the values into the command programmatically -- would achieve the same aim.

Glad you've got it working -- even though it a full year has elapsed.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)