SQLite and SQLCode

Can you show me what the syntax looks like for your calls to SQLite?

I am not familiar with this product although the description states that it is accessed via callable C library which means that it can certainly be called from COBOL.

Thanks.

Sample: SQLite in Visual Cobol with SQLite DLL.

          declare dt = new System.Data.DataTable().

          declare cnn = new SQLiteConnection("data source=db\\Contas-Financeiro.s3db")

          invoke cnn::Open()

          declare mCmd = new SQLiteCommand(cnn)

          move string::Format("SELECT EXISTS(SELECT * FROM sqlite_master WHERE name ='contas' and type='table');") to mCmd::CommandText

          declare reader = mCmd::ExecuteReader()

          invoke dt::Load(reader)

          invoke reader::Close()

          if dt::Rows[0][0]::ToString() equal "0"

              declare command = new SQLiteCommand(cnn)

              move "CREATE TABLE [contas] (

     -               [id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,

     -               [fornecedor] NVARCHAR(50)  NOT NULL,

     -               [numero_titulo] INTEGER  NOT NULL,

     -               [numero_parcela] INTEGER  NOT NULL,

     -               [data_emissao] DATE  NOT NULL,

     -               [data_vencimento] DATE  NOT NULL,

     -               [data_pagamento] DATE  NULL,

     -               [valor_conta] FLOAT  NOT NULL,

     -               [valor_pagamento] FLOAT  NULL,

     -               [forma_pagamento] NVARCHAR(100)  NULL

     -               )" to command::CommandText

              invoke command::ExecuteNonQuery()

          end-if.

          invoke cnn::Close().

So you are using an ADO.NET data provider for SqlLite.
You can trap errors in managed code by wrapping your statements in try...catch blocks.

Example:

          try
             set myConnection to new SqlConnection(myConnectString)
             invoke myConnection::Open
          catch ex as type Exception
             display ex::Message
          end-try

If any error occurs then the statements between the catch and the end-try will be executed.
If no error occurs then they will not.

Please see the documentation for the Try statement here.

Have you tried to use this ADO.NET provider with OpenESQL?

If it worked (I have never tested it), then it would allow you to use standard embedded SQL statements within your COBOL so you would not have to code the OO calls directly.

Just a thought...

== Have you tried to use this ADO.NET provider with OpenESQL?

No, how do this?

=== If it worked (I have never tested it), then it would allow you to use standard embedded SQL statements within your COBOL so you would not have to code the OO calls directly.

Do you say using EXEC SQL ... END-EXEC?

What is the best to work for ADO, with EXEC SQL, or some other type? to work with databases in VC 2012?

How you interface to the ADO.NET APi's is really a matter of personal preference.

If you are comfortable coding using object-oriented COBOL and accessing the ADO.NET classes directly then this is certainly a viable solution.

If however, you are accustomed to using procedural COBOL with embedded SQL statements using EXEC SQL ...END EXEC or if you have existing applications that already use EXEC SQL then you might want to look at using OpenESQL with the DBMAN=ADO directive.

We have tested the DBMAN=ADO with ADO.NET providers for SQL Server, Oracle, DB2 and others but I have never tried with the SQLLite provider.

Open up the ADO.NET Connection Editor from Visual Studio IDE-->Tools-->Micro Focus COBOL and look under the providers tab.

Do you see the SQLLite Provider listed?

This OpenESQL ADO.NET support is documented here:

You might also want to take a look at the new recordings that were just posted out on Youtube that cover topics such as OpenESQL with Visual COBOL..

In particular you might find this one useful:

Visual COBOL In a Nutshell: Using ADO.NET with Visual COBOL.

Thanks.