OpenESQL issues


I'm getting odd behaviour testing OpenESQL (VC2.3.2, VS2015, Windows10). I've previously only used native MF files but have used MySQL extensively with PHP online.

My tests act as though MF DEBUG runs in it's own world. It doesn't actually update my MySQL database?

I have a 3-col 'vatrates' table. Cols: id, startDate and vatRate. It has one row of data.

My prog below inserts two more rows. No error is reported, Hence 3 rows.

Next I run select/fetch - this gets the three rows, the old row and two new ones.

Lastly the prog deletes a row - this seems to work and if I re-step through the select/fetch it returns two rows.

HOWEVER, at any point during the above and after stop run... if i view the table in MySQL Workbench I only see the original row. If I re-run my prog it starts again as though only the original one row is there.

Any ideas what is going on here? Program follows below....

Thanks, Linden


$SET sql(INIT DB=adbooker
working-storage section.
exec sql include sqlca end-exec.
01 mfsqlmessagetext pic x(1000).
01 sql-statement pic x(1000).

01 startDate pic x(8).
01 vatRate pic 99v99.
01 vatId pic 9(9).
procedure division.

  exec sql
    whenever sqlerror perform show-error

  exec sql
    insert into vatrates
        (startDate, vatrate)
        ('2009-08-18', 13.17)

  move "insert into vatrates (startDate, vatRate)"
        & " values ('2008-01-02', 88.77)" to sql-statement.
  exec sql execute immediate :sql-statement end-exec.

  move "select id, DATE_FORMAT(startDate,'%Y%m%d'), vatRate"
        & " from vatrates order by startDate desc"
      to sql-statement.
  exec sql prepare mysql from :sql-statement end-exec.
  exec sql declare mycursor cursor for mysql end-exec.
  exec sql open mycursor end-exec.
  exec sql fetch mycursor
        into :vatId, :startDate, :vatRate
  if sqlcode = zero
     go to AGAIN.

  exec sql close mycursor end-exec.

  move "delete from vatrates where id = ?" to sql-statement.
  exec sql prepare mysql from :sql-statement end-exec.
  exec sql execute mysql using :vatId end-exec.






Parents Reply Children
No Data