Highlighted
Absent Member.
Absent Member.
1051 views

OpenESQL issues

Jump to solution

Hi,

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 PASS=adbooker.pw)
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
  end-exec.

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

  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.
AGAIN.
  exec sql fetch mycursor
        into :vatId, :startDate, :vatRate
  end-exec.
  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.

  STOP RUN.

  

 

 

  

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
2 Solutions

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: OpenESQL issues

Jump to solution

You are not committing your changes to the database. You need to issue a

EXEC SQL COMMIT WORK END-EXEC

statement in order to make them permanent.

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: OpenESQL issues

Jump to solution

You could turn on the directive SQL(autocommit) so that it will perform a commit after each statement.

I prefer using an explicit connect statement in my program so that I can have more control over the connect and error handling. Also if you ever need to disconnect or use more than one connection you would have to do this explicitly. It is really personal preference though.

View solution in original post

0 Likes
4 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: OpenESQL issues

Jump to solution

You are not committing your changes to the database. You need to issue a

EXEC SQL COMMIT WORK END-EXEC

statement in order to make them permanent.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: OpenESQL issues

Jump to solution

ah ok thanks Chris. The default is to rollback then.

ALSO... would you suggest using explicit connect rather than the implicit set statement?

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: OpenESQL issues

Jump to solution

You could turn on the directive SQL(autocommit) so that it will perform a commit after each statement.

I prefer using an explicit connect statement in my program so that I can have more control over the connect and error handling. Also if you ever need to disconnect or use more than one connection you would have to do this explicitly. It is really personal preference though.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: OpenESQL issues

Jump to solution

Thanks Chris, I'll have to have a think / look into all that. I need to do transaction rollback anyway in the live system. Generally though i-o is so reliable and with low numbers of users I haven't worried about it unduly. Thanks again. Linden

Linden Rowland - IT Consultant
Owner/Developer, www.SchoolReportWriter.com
IT Consultant to Tindle Newspaper Group

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.