Connect with MySQL

Hi Giorgos,

You can use Embedded SQL statements and AcuSQL or you can use Acu4GL for ODBC

to work with your ACUCOBOL-GT application and MySQL.

You will find more info about the products here:

www.microfocus.com/.../index.aspx

You can contact me at: dominique.sacre@microfocus.com

Regards,

Dominique

We do have the AcuSQL but i still cannot connect to my remote MySQL server.

i use:

EXEC SQL CONNECT TO "minisys" END-EXEC

but i get the error:

SQL Error: SQLCODE -1

SQLSTATE 00000

Have you tried to use a product like sql*plus to execute the same SQL command?

Not an expert in MySQL, but I am guessing that there is some MySQL client software on the machine where the runtime resides? Or is the MySQL connection made through defining an ODBC DSN? If an ODBC DSN, please share you runtime configuration file entries.

i don't use acusql but in a Workshop i heard that there are sometimes Problems with "

i use adodb for db-connections

when i look to the sample code from the Workshop:

          if userid not = spaces

             EXEC SQL CONNECT TO :servername

                  as C1

                  USER :userid USING :passwd

             END-EXEC

          else

             EXEC SQL CONNECT TO :servername as C1

             END-EXEC

          end-if

and the sample in the acucobol Manual:

(55)   display "Making connection".

(56)   EXEC SQL CONNECT TO :dsn-name as C1

(57)      END-EXEC.

(58)   display "Connection made".

what is runtime configuration file entries? i only know Data Source Configuration

Like really did anyone used that AcuSQL?

Have you tried our sample programs ...

C:\\Program Files (x86)\\Micro Focus\\Acucbl9xx\\AcuGT\\sample\\acusql\\mysql .. the create.sqb would be a good place to start

i do but i get an error of -1 when i try to connect.

have you tested to get an more detailed error-msg?

SQL Server Environments

In Microsoft SQL Server environments, the external variable F-ERRMSG is set to the full text of the error message returned to accommodate error messages longer than

70 characters.

F-ERRMSG is an external variable of USAGE POINTER that must be manipulated in order to see its contents. The following code demonstrates how to see the contents

of the error message string:

01 f-errmsg usage pointer external.

01 my-errmsg pic x(512).

....

0000-START.

EXEC SQL WHENEVER SQLERROR GO TO ERROR-EXIT END-EXEC.

....

ERROR-EXIT.

EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.

DISPLAY "SQL ERROR: SQLCODE " SQLCODE

DISPLAY " SQLSTATE " SQLSTATE

DISPLAY SQLERRMC

call "c$memcpy" using my-errmsg, by value f-errmsg, 512.

display my-errmsg

ACCEPT OMITTED

STOP RUN.

where

f-errmsg is an external variable that contains the full text of the error message.

my-errmsg is the full text of the error message, which in this case can contain up to 512 bytes.

See Book 3, Reference Manual, of the ACUCOBOL-GT documentation set for information on C$MEMCPY and USAGE POINTER

but i use AcuSQL for MySQL database.

this is from the AcuSQL User's Guide 2.9!

I found this in wikipedia. is that true?

Systems that do not support Embedded SQL

Microsoft SQL Server

Embedded SQL for C has been deprecated as of Microsoft SQL Server 2008 although earlier versions of the product support it.

MySQL

MySQL does not support ESQL

Don't think so:

in the Microfocus Documentation is the Info for AcuSQL:

AcuSQL

AcuSQL® is an add-on tool that supports embedded SQL (ESQL) COBOL programs. It gives COBOL applications access to Microsoft SQL, IBM DB2, MySQL and ISO/ANSI SQL92 compliant data sources.

and i think they have it tested before it was released :)

i see that too. But why i cannot connect i dont know! i use the sample program but i cannot connect and get -1

thats why i posted the sample from the MicroFocus Manual to get a more detailed error msg.

Is the mysql Server configured that a external Client can connect?

Is the mysql Server restricted for ip xy to connect?

Perhaps the error is the mySQL-Server configuration/Limitation?

Alternate you can modify a adodb example to connect to the mysql-DB, but i have only a example here for MS SQL:

       identification division.
       program-id.      adodb.
      *************************************************************
      *                                                           *
      *    Test DB Zugriff über MS ADO                            *
      *                                                           *
      *************************************************************
       author.          David Neidinger.
       date-written.
       environment division.
       configuration section.
       special-names.
           decimal-point is comma
           copy "adodb.def".
                .
       input-output section.
      ***********************************************************
       file-control.

       data division.
       file section.
      ***********************************************************
       working-storage section.
       77  adoConnection          handle of Connection.
       77  adoRecordset           handle of Recordset.
       77  adoFields              handle of Fields.
       77  adoConnStr             pic x(256).
       77  adoConnState           pic s9(9).

       77  h-1                    pic x(255).
       77  h-2                    pic x(255).
       77  h-3                    pic x(255).
       77  h-4                    pic x(255).

       78  newline                               value x"0A".
      ***********************************************************
       procedure division.
       main section.
      ***  Connection String definieren
           string "Driver={SQL Server};"
                  "Server=B21SQL0725001\\VARIAL;"
                  "Database=varial;"
                  "Uid=xx;";         | Benutzername
                  "Pwd=xxxxxxxx"     | Passwort
                  delimited by size into adoConnStr.

      ***  Instanz für AdoDB erzeugen
           create Connection of ADODB
                  handle     in adoConnection.

      ***  Instanz fürs RecordSet erzeugen
           create Recordset  of ADODB
                  handle     in adoRecordset.

      ***  Verbindung aufbauen
           modify  adoConnection @Open(adoConnStr).

      ***  Verbindungs-Status abfragen
       main-state.
           inquire adoConnection State in adoConnState.

           evaluate adoConnState
              when adStateOpen
                   continue
              when adStateClosed
                   display message box "State Closed"
                   go to   main-finish
              when adStateConnecting
                   display message box "State Connecting"
                   go to   main-state
              when adStateExecuting
                   display message box "State Executing"
                   go to   main-finish
              when adStateFetching
                   display message box "State Fetching"
                   go to   main-finish
           end-evaluate.

      ***  Daten über Recordset abfragen
      *    modify  adoRecordset MaxRecords = 15. | Anzahl Datensätze limitieren 0 = Default = Alles
           modify  adoRecordset @Open(By NAME Source
                   "Select * FROM [vwe_user].[PUBPERSACCBANK_V] where CO
      -            "MPANYCODE = '101' and CODE > '100' and ISCUSTOMER =
      -            "'F'"
                   BY NAME ActiveConnection adoConnection).

      ***  zum ersten Datensatz springen
           modify  adoRecordset MoveFirst().
           inquire adoRecordset EOF in adoConnState.

      ***  Loop
           perform until adoConnState  = -1
              inquire adoRecordset
                      fields::item("COMPANYCODE")::value      in h-1
                      fields::item("ISCUSTOMER")::value       in h-2
                      fields::item("B_BANKNAME")::value       in h-3
                      fields::item("B_SWIFT")::value          in h-4

              display h-1 at 0101
              display h-2 at 0201
              display h-3 at 0301
              display h-4 at 0401

              modify  adoRecordset MoveNext()
              inquire adoRecordset EOF in adoConnState
           end-perform.

      ***  Close Connections
           modify adoRecordset  @Close().
           modify adoConnection @Close().

       main-finish.
      ***  Instanzen/Handles zerstören.
           destroy adoRecordset.
           destroy adoConnection.

      ***  Programm beenden
           accept omitted tab.
           stop run.

       main-ende.
           exit.
       main-e.
      ***********************************************************

This is totally diferent approach of what i am trying here. AcuSQL suppose to let you embedd SQL commands inside your code.

Your initial connect statement did not contain a user-name or password ... when you place the username and password into a program like create.sqb, execute wrun32 -le my-errs create.acu what shows up in the my-errs file? My other suggestion is to use a tool like SQL*PLUS which would allow you to issue a connect statement in a command prompt to verify you can access the MYSQL database from your PC.

Is minisys the Data Source Name for your ODBC connection to MySQL? Your initial connect statement did not contain a user-name or password ... when creating a System DSN, typically Microsoft establishes the username as system and the password as manager, but both can be changed. Part of the code neidingd has does a connect with username & passord or connect as C1 ..when you place the username and password into a program like create.sqb, execute wrun32 -le my-errs create.acu what shows up in the my-errs file? My other suggestion is to use a tool like SQL*PLUS which would allow you to issue a connect statement in a command prompt to verify you can access the MYSQL database from your PC.