Difference between revisions of "SQLEXEC()"

From Lianjapedia
Jump to: navigation, search
 
(11 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 +
[[Category:Database Stored Procedures]]
 
==Purpose==
 
==Purpose==
−
Send an SQL statement to a data source
+
Send an SQL statement to an ODBC data source
  
 
==Syntax==
 
==Syntax==
Line 6: Line 7:
  
 
==See Also==
 
==See Also==
−
[[SQLCANCEL()]], [[SQLCOLUMNS()]], [[SQLCOMMIT()]], [[SQLCONNECT()]], [[SQLDISCONNECT()]], [[SQLERROR()]], [[SQLGETPROP()]], [[SQLMORERESULTS()]], [[SQLPREPARE()]], [[SQLROLLBACK()]], [[SQLSETPROP()]], [[SQLSTRINGCONNECT()]], [[SQLTABLES()]]
+
[[SQLCANCEL()]], [[SQLCOLUMNS()]], [[SQLCOMMIT()]], [[SQLCONNECT()]], [[SQLDISCONNECT()]], [[SQLERROR()]], [[SQLGETPROP()]], [[SQLMORERESULTS()]], [[SQLPARAMS()]], [[SQLPREPARE()]], [[SQLROLLBACK()]], [[SQLSETPROP()]], [[SQLSTRINGCONNECT()]], [[SQLTABLES()]], [[SQLTRANSACTION()]]
  
 
==Description==
 
==Description==
 
The SQLEXEC() function is used to send an SQL statement to the specified data source.
 
The SQLEXEC() function is used to send an SQL statement to the specified data source.
  
−
The SQLEXEC() function operates on the data source specified by <nStatementHandle>.  The [[SQLERROR()]] function can be used to return the error message if the SQL statement fails.
+
The SQLEXEC() function operates on the ODBC connection specified by <nStatementHandle>.  The [[SQLERROR()]] function can be used to return the error message if the SQL statement fails.
  
−
{| class="wikitable"
+
{| class="wikitable" width="100%"
−
!Keywords||Description
+
!width="30%"|Keywords||width="70%"|Description
 
|-
 
|-
−
|nStatementHandle||The workarea in which the gateway data source is open.
+
|nStatementHandle||Statement handle to the connection
 
|-
 
|-
−
|cSQLCommand||The SQL statement to be passed to the data source.  The cSQLCommand can be omitted if the SQL statement has already been set up using SQLPREPARE().  
+
|valign="top"|cSQLCommand||The SQL statement to be passed to the data source.  The cSQLCommand can be omitted if the SQL statement has already been set up using SQLPREPARE().  
 
|-
 
|-
−
|cCursorName||The name of the temporary table to use.  If cCursorName is not specified, the default name SQLRESULT is used.  If the SQLEXEC() is running a pre-prepared statement, the cCursorName is taken from the SQLPREPARE() setting.
+
|valign="top"|cCursorName||The name of the temporary table to use.  If cCursorName is not specified, the default name SQLRESULT is used.  If the SQLEXEC() is running a pre-prepared statement, the cCursorName is taken from the SQLPREPARE() setting.
 
|-
 
|-
 
|}
 
|}
Line 26: Line 27:
 
====Return values:====
 
====Return values:====
  
−
{| class="wikitable"
+
{| class="wikitable" width="100%"
−
!Return Value||Description
+
!width="30%"|Return Value||width="70%"|Description
 
|-
 
|-
 
|<n>||Number of results sets if more than 1
 
|<n>||Number of results sets if more than 1
Line 41: Line 42:
 
==Example==
 
==Example==
 
<code lang="recital">
 
<code lang="recital">
−
nStatHand=SQLCONNECT("awhr")
+
nStatHand = sqlconnect("awhr")
 
if nStatHand < 1
 
if nStatHand < 1
 
messagebox("Cannot make connection", 16, "SQL Connect Error")
 
messagebox("Cannot make connection", 16, "SQL Connect Error")
 
else
 
else
 
messagebox("Connection made", 48, "SQL Connect Message")
 
messagebox("Connection made", 48, "SQL Connect Message")
−
SQLEXEC(nStatHand,"create table doc1 (ACCOUNT_NO char(5), lastname char(25), balance dec(10,2))")
+
sqlexec(nStatHand,"create table doc1 (account_no char(5), lastname char(25), balance dec(10,2))")
 
messagebox("Table created")
 
messagebox("Table created")
 
m_accno =  "00699"
 
m_accno =  "00699"
 
m_lastname = "Smith"
 
m_lastname = "Smith"
 
m_balance = 12345.67
 
m_balance = 12345.67
−
SQLEXEC(nStatHand,"INSERT INTO dbo.doc1 (ACCOUNT_NO,lastname,balance) VALUES ('&m_accno','&m_lastname',&m_balance)")
+
sqlexec(nStatHand,"insert into dbo.doc1 (account_no,lastname,balance) VALUES ('&m_accno','&m_lastname',&m_balance)")
−
nRET=SQLEXEC(nStatHand,"SELECT * from dbo.doc1","mycursor")
+
messagebox("Record inserted")
−
if nRET < 1
+
nRet = sqlexec(nStatHand,"select * from dbo.doc1","mycursor")
 +
if nRet = -1
 
messagebox(sqlerror(nstatHand))
 
messagebox(sqlerror(nstatHand))
 
else
 
else
Line 59: Line 61:
 
list
 
list
 
endif
 
endif
−
SQLDISCONNECT(nStatHand)
+
// Using prepared statement
 +
sqlprepare(nStatHand,"select * from dbo.doc1 where account_no = ?m_accno","mycursor")
 +
nRet = sqlexec(nStatHand)
 +
if nRet < 1
 +
messagebox(sqlerror(nstatHand))
 +
else
 +
messagebox("Record selected using prepared statement")
 +
list
 +
endif
 +
sqldisconnect(nStatHand)
 
endif
 
endif
 
</code>
 
</code>
Line 66: Line 77:
 
[[Category:Functions]]
 
[[Category:Functions]]
 
[[Category:SQL]]
 
[[Category:SQL]]
−
[[Category:Remote Data Connectivity Functions]]
 
 
[[Category:ODBC Functions]]
 
[[Category:ODBC Functions]]
 
[[Category:SQL Functions]]
 
[[Category:SQL Functions]]

Latest revision as of 06:22, 15 December 2017

Purpose

Send an SQL statement to an ODBC data source

Syntax

SQLEXEC(<nStatementHandle> [, <cSQLCommand> [, <cCursorName>]])

See Also

SQLCANCEL(), SQLCOLUMNS(), SQLCOMMIT(), SQLCONNECT(), SQLDISCONNECT(), SQLERROR(), SQLGETPROP(), SQLMORERESULTS(), SQLPARAMS(), SQLPREPARE(), SQLROLLBACK(), SQLSETPROP(), SQLSTRINGCONNECT(), SQLTABLES(), SQLTRANSACTION()

Description

The SQLEXEC() function is used to send an SQL statement to the specified data source.

The SQLEXEC() function operates on the ODBC connection specified by <nStatementHandle>. The SQLERROR() function can be used to return the error message if the SQL statement fails.

Keywords Description
nStatementHandle Statement handle to the connection
cSQLCommand The SQL statement to be passed to the data source. The cSQLCommand can be omitted if the SQL statement has already been set up using SQLPREPARE().
cCursorName The name of the temporary table to use. If cCursorName is not specified, the default name SQLRESULT is used. If the SQLEXEC() is running a pre-prepared statement, the cCursorName is taken from the SQLPREPARE() setting.

Return values:

Return Value Description
<n> Number of results sets if more than 1
0 SQLEXEC() is still executing
1 SQLEXEC() finished executing
-1 Connection error

Example

nStatHand = sqlconnect("awhr")
if nStatHand < 1
	messagebox("Cannot make connection", 16, "SQL Connect Error")
else
	messagebox("Connection made", 48, "SQL Connect Message")
	sqlexec(nStatHand,"create table doc1 (account_no char(5), lastname char(25), balance dec(10,2))")
	messagebox("Table created")
	m_accno =  "00699"
	m_lastname = "Smith"
	m_balance = 12345.67
	sqlexec(nStatHand,"insert into dbo.doc1 (account_no,lastname,balance) VALUES ('&m_accno','&m_lastname',&m_balance)")
	messagebox("Record inserted")
	nRet = sqlexec(nStatHand,"select * from dbo.doc1","mycursor")
	if nRet = -1
		messagebox(sqlerror(nstatHand))
	else
		messagebox("Record selected")
		list
	endif
	// Using prepared statement
	sqlprepare(nStatHand,"select * from dbo.doc1 where account_no = ?m_accno","mycursor")
	nRet = sqlexec(nStatHand)
	if nRet < 1
		messagebox(sqlerror(nstatHand))
	else
		messagebox("Record selected using prepared statement")
		list
	endif
	sqldisconnect(nStatHand)
endif
Retrieved from "https://www.lianja.com/doc/index.php?title=SQLEXEC()&oldid=19541"