SQLCODE in Cobol

[carlazza]

Hi all,

I remember that in Cobol there was the SQLCODE variable which returned the result of a DB instruction

0-> everything ok
100-> not found
etc ...


Is there an equivalent thing in HMG too?

Thanks, Carlo.

[dragancesu]

Every database system get error code
What do you want?

[carlazza]

How can I test if an insert/update operation was successful?

[Claudio Ricardo]

Hello ... I use some functions that I create to automate the process and simplify the code, there are 4 in total:
One, "SQL_StartConn ()" upon entering the program establishes the connection to the database.
One function for SELECT "SQL_Q (cQuery)" returns a two-dimensional array with the data,
or empty if there is no data or it failed. And another for INSERT, DELETE, Etc. "SQL_E (cQuery)"
And one last "SQL_EndConn ()" to close the connection when exiting the program.
If for some reason the connection is lost, they try to reestablish it.
Each one with its respective error messages that you can translate into Italian.
They also return .T. if all good or .F. if it failed.

[serge_girard]

Carlazza,

SQLCODE like Cobol on mainframe... that would be nice...!

We have another solution, I use this:

Code: Select all

cQuery1a  := " SELECT NR, PLAATS, LAND "
cQuery1a  += " FROM GEN_PLAATS_LND "
cQuery1a  += " WHERE LAND  > '' "
cQuery1a  += " AND  PLAATS > '' "
cQuery1a  += " ORDER BY 3,2 "
cSQLa		:= cQuery1a
cQuery1a	:= dbo:Query( cQuery1a )
IF cQuery1a:NetErr()					 
   MSGINFO(cSQLa + cQuery1a:ERROR() )		 
   RETURN
ENDIF

cQuery1	:= " DELETE FROM NEW_VERSION "   
cQuery1	+= " WHERE FILE = 'BETOZPDF' "  
cQuery1	+= " AND   PERSON_ID = '" + xNR2    + "' "  
cSQL		:= cQuery1
cQuery1	:= dbo:Query( cQuery1 )
IF cQuery1:NetErr()
   Write_Trace_RED( PROCNAME(),cSQL, cQuery1:ErrOR())
   RETURN
ENDIF   

Minimal and you have to investigate the content of NetErr() to know what is the problem. Also to know number of affected rows after an update is missing, as far as I know.

But overaal SQL works great in HMG!

Serge

[carlazza]

Hello ... I use some functions that I create to automate the process and simplify the code, there are 4 in total:
One, "SQL_StartConn ()" upon entering the program establishes the connection to the database.
One function for SELECT "SQL_Q (cQuery)" returns a two-dimensional array with the data,
or empty if there is no data or it failed. And another for INSERT, DELETE, Etc. "SQL_E (cQuery)"
And one last "SQL_EndConn ()" to close the connection when exiting the program.
If for some reason the connection is lost, they try to reestablish it.
Each one with its respective error messages that you can translate into Italian.
They also return .T. if all good or .F. if it failed.

Thanks a lot Claudio, I will take a look

[carlazza]

Carlazza,

SQLCODE like Cobol on mainframe... that would be nice...!

We have another solution, I use this:

Code: Select all

cQuery1a  := " SELECT NR, PLAATS, LAND "
cQuery1a  += " FROM GEN_PLAATS_LND "
cQuery1a  += " WHERE LAND  > '' "
cQuery1a  += " AND  PLAATS > '' "
cQuery1a  += " ORDER BY 3,2 "
cSQLa		:= cQuery1a
cQuery1a	:= dbo:Query( cQuery1a )
IF cQuery1a:NetErr()					 
   MSGINFO(cSQLa + cQuery1a:ERROR() )		 
   RETURN
ENDIF

cQuery1	:= " DELETE FROM NEW_VERSION "   
cQuery1	+= " WHERE FILE = 'BETOZPDF' "  
cQuery1	+= " AND   PERSON_ID = '" + xNR2    + "' "  
cSQL		:= cQuery1
cQuery1	:= dbo:Query( cQuery1 )
IF cQuery1:NetErr()
   Write_Trace_RED( PROCNAME(),cSQL, cQuery1:ErrOR())
   RETURN
ENDIF   

Minimal and you have to investigate the content of NetErr() to know what is the problem. Also to know number of affected rows after an update is missing, as far as I know.

But overaal SQL works great in HMG!

Serge

Great. I will try it
Tks

[franco]

I have never been able to sql to work. I am using hmg.3.4.4. I think I need to add something to my compiler.
Here is what I would like to do. I can do this in VISUAL FOXPRO but there seems to be no sql in HMG.

Code: Select all

#include <hmg.ch>
Local cQueryla, sQla //I did this in fox SELECT NAME,CITY,PROVINCE FROM CUSTOMER WHERE CITY > ' ' INTO TABLE 'TEMP.DBF'
cQuery1a  := " SELECT NAME "   // I have table CUSTOMER with fields NAME,CITY,PROVINCE
cQuery1a  += " FROM CUSTOMER "
cQuery1a  += " WHERE CITY  > '' "
cQuery1a  += " AND  PROVINCE > '' "
cQuery1a  += " ORDER BY NAME "
cQuery1a  += " INTO TABLE 'TEMP.DBF' "
cSQLa		:= cQuery1a
cQuery1a	:= dbo:Query( cQuery1a )
If file('TEMP.DBF')
	USE TEMP                           // If I USE CUSTOMER this works but I can not USE SQL TO CREATE TEMP
	set device to printer
	set printer to "Temp1.txt"      //Creates a text file.
	list NAME, CITY, PROVINCE to printer
	set device to screen
	CLOSE TEMP
endif

*cQuery1a  := " SELECT NR, PLAATS, LAND "
*cQuery1a  += " FROM GEN_PLAATS_LND "
*cQuery1a  += " WHERE LAND  > '' "
*cQuery1a  += " AND  PLAATS > '' "
*cQuery1a  += " ORDER BY 3,2 "
*cSQLa		:= cQuery1a
*cQuery1a	:= dbo:Query( cQuery1a )
*IF cQuery1a:NetErr()					 
*   MSGINFO(cSQLa + cQuery1a:ERROR() )		 
*   RETURN
*ENDIF
RETURN

What I do now and works ok is.

Code: Select all

 
use CUSTOMER NEW SHARED
index on NAME TO TEMP FOR LEN(ALLTRIM(CITY)) > 0
** Then do something
close CUSTOMER
TEMP := 'TEMP.DBF'
erase (temp)

I would like to learn how to use sql.
Any thoughts.
Thanks, Franco

[serge_girard]

Franco,

Plenty of demo's in this forum.
First of all you will need a database and XAMPP and maybe some internet site.
How far are you?
Maybe also start new topic...

Serge

[dragancesu]

@franco look viewtopic.php?f=5&t=5107&hilit=myhmg&start=10

this is my early works on this forum, software and manual for work with MySQL