SQL Anywhere – Anvil of Time https://anvil-of-time.com Nature Forges Everything on the Anvil of Time Thu, 11 Mar 2021 00:21:00 +0000 en-US hourly 1 Update Columns in Database https://anvil-of-time.com/ms-sql-server/update-columns-in-database/ Sat, 29 Jan 2011 01:20:36 +0000 http://anvil-of-time.com/wordpress/?p=700 It’s pretty common to want to update a column (or several columns) in one table with the value(s) from another table. Here is how to do it:

UPDATE
    Table
SET
    Table.col1 = other_table.col1,
    Table.col2 = other_table.col2
FROM
    Table
INNER JOIN
    other_table
ON
    Table.id = other_table.id 
WHERE ...

The structure of this syntax is always something I get mixed up when I want to do this.

]]> PowerBuilder ‘Gotcha’ – Quoted Identifiers https://anvil-of-time.com/powerbuilder/powerbuilder-gotcha-quoted-identifiers/ Wed, 22 Sep 2010 01:20:39 +0000 http://anvil-of-time.com/wordpress/?p=386 Be careful when developing against any database which supports quoted identifiers (I know, who doesn’t)? Anyway it is real easy to forget and ‘turn off’ this option on your local db (or your development connection) and end up releasing a bug by a simple SQL alteration like this:

--Original SQL
SELECT 
partNo
, partDesc
FROM PartMaster 
WHERE categoryKey = :al_catkey
--Modified SQL
SELECT 
partNo
, partDesc
FROM PartMaster 
WHERE categoryKey = :al_catkey
AND partStatus = "A"

This works fine with quoted identifiers off but will generate an error when run on your customer’s database where this option is turned on (which is the default for many installations).

]]> PowerBuilder – Single user sign on verification for SQL Anywhere https://anvil-of-time.com/powerbuilder/powerbuilder-single-user-sign-on-verification/ Thu, 08 Jul 2010 01:17:10 +0000 http://anvil-of-time.com/wordpress/?p=189 This method is used with a SQL Anywhere database to ensure that any given user can only sign on the application once. It uses the connection_property function so make sure you either give the users permission to execute this or set up a separate connection which uses an id which has the permissions.

There are three Powerbuilder objects, one nvo and two datawindow objects, a stored procedure, and a table.

Table

CREATE TABLE "dba"."sec_userconnection" 
("connectionid" numeric(12,0) NOT NULL DEFAULT NULL
, "userid" varchar(20) NOT NULL DEFAULT NULL 
, PRIMARY KEY ("connectionid", "userid")) ;

Stored Procedure

create procedure DBA.Conn_number(out id integer)
begin
  select connection_property('Number')
end

Datawindow objects

$PBExportHeader$d_conn_number.srd
release 9;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes hidegrayline=no )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=144 color="536870912" )
table(column=(type=char(254) updatewhereclause=yes name=conn_number dbname="compute_0001" )
 procedure="1 execute dba.conn_number;0 id = :id" arguments=(("id", number)) )
text(band=detail alignment="1" text="Connection Property('number'):" border="0" color="33554432" x="37" y="4" height="64" width="809" html.valueishtml="0"  name=compute_0001_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=1 alignment="0" tabsequence=32766 border="0" color="33554432" x="864" y="4" height="76" width="4389" format="[general]" html.valueishtml="0"  name=conn_number visible="1" edit.limit=254 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" )
export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 )
import.xml()
export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )

This second datawindow object is based on the stored procedure sa_conn_info. Again, you must have permissions set up for the user to run this.

$PBExportHeader$d_sa_conn_info.srd
release 9;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes hidegrayline=no )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=2048 color="536870912" )
table(column=(type=long updatewhereclause=yes name=number dbname="number" )
 column=(type=char(255) updatewhereclause=yes name=name dbname="name" )
 column=(type=char(255) updatewhereclause=yes name=userid dbname="userid" )
 column=(type=long updatewhereclause=yes name=dbnumber dbname="dbnumber" )
 column=(type=char(255) updatewhereclause=yes name=lastreqtime dbname="lastreqtime" )
 column=(type=char(255) updatewhereclause=yes name=processtime dbname="processtime" )
 column=(type=long updatewhereclause=yes name=port dbname="port" )
 column=(type=char(255) updatewhereclause=yes name=reqtype dbname="reqtype" )
 column=(type=char(255) updatewhereclause=yes name=commlink dbname="commlink" )
 column=(type=char(255) updatewhereclause=yes name=nodeaddr dbname="nodeaddr" )
 column=(type=long updatewhereclause=yes name=lastidle dbname="lastidle" )
 column=(type=long updatewhereclause=yes name=currtasksw dbname="currtasksw" )
 column=(type=long updatewhereclause=yes name=blockedon dbname="blockedon" )
 column=(type=decimal(0) updatewhereclause=yes name=lockname dbname="lockname" )
 column=(type=long updatewhereclause=yes name=uncmtops dbname="uncmtops" )
 procedure="1 execute dbo.sa_conn_info;0 connidparm = :connidparm" arguments=(("connidparm", number)) )
text(band=detail alignment="1" text="Number:" border="0" color="33554432" x="37" y="4" height="64" width="352" html.valueishtml="0"  name=number_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=1 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="4" height="76" width="329" format="[general]" html.valueishtml="0"  name=number visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Name:" border="0" color="33554432" x="37" y="140" height="64" width="352" html.valueishtml="0"  name=name_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=2 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="140" height="76" width="4389" format="[general]" html.valueishtml="0"  name=name visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Userid:" border="0" color="33554432" x="37" y="276" height="64" width="352" html.valueishtml="0"  name=userid_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=3 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="276" height="76" width="4389" format="[general]" html.valueishtml="0"  name=userid visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Dbnumber:" border="0" color="33554432" x="37" y="412" height="64" width="352" html.valueishtml="0"  name=dbnumber_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=4 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="412" height="76" width="329" format="[general]" html.valueishtml="0"  name=dbnumber visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Lastreqtime:" border="0" color="33554432" x="37" y="548" height="64" width="352" html.valueishtml="0"  name=lastreqtime_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=5 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="548" height="76" width="4389" format="[general]" html.valueishtml="0"  name=lastreqtime visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Processtime:" border="0" color="33554432" x="37" y="684" height="64" width="352" html.valueishtml="0"  name=processtime_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=6 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="684" height="76" width="4389" format="[general]" html.valueishtml="0"  name=processtime visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Port:" border="0" color="33554432" x="37" y="820" height="64" width="352" html.valueishtml="0"  name=port_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=7 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="820" height="76" width="329" format="[general]" html.valueishtml="0"  name=port visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Reqtype:" border="0" color="33554432" x="37" y="956" height="64" width="352" html.valueishtml="0"  name=reqtype_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=8 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="956" height="76" width="4389" format="[general]" html.valueishtml="0"  name=reqtype visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Commlink:" border="0" color="33554432" x="37" y="1092" height="64" width="352" html.valueishtml="0"  name=commlink_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=9 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="1092" height="76" width="4389" format="[general]" html.valueishtml="0"  name=commlink visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Nodeaddr:" border="0" color="33554432" x="37" y="1228" height="64" width="352" html.valueishtml="0"  name=nodeaddr_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=10 alignment="0" tabsequence=32766 border="0" color="33554432" x="407" y="1228" height="76" width="4389" format="[general]" html.valueishtml="0"  name=nodeaddr visible="1" edit.limit=255 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Lastidle:" border="0" color="33554432" x="37" y="1364" height="64" width="352" html.valueishtml="0"  name=lastidle_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=11 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1364" height="76" width="329" format="[general]" html.valueishtml="0"  name=lastidle visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Currtasksw:" border="0" color="33554432" x="37" y="1500" height="64" width="352" html.valueishtml="0"  name=currtasksw_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=12 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1500" height="76" width="329" format="[general]" html.valueishtml="0"  name=currtasksw visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Blockedon:" border="0" color="33554432" x="37" y="1636" height="64" width="352" html.valueishtml="0"  name=blockedon_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=13 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1636" height="76" width="329" format="[general]" html.valueishtml="0"  name=blockedon visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Lockname:" border="0" color="33554432" x="37" y="1772" height="64" width="352" html.valueishtml="0"  name=lockname_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=14 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1772" height="76" width="329" format="[general]" html.valueishtml="0"  name=lockname visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
text(band=detail alignment="1" text="Uncmtops:" border="0" color="33554432" x="37" y="1908" height="64" width="352" html.valueishtml="0"  name=uncmtops_t visible="1"  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=15 alignment="1" tabsequence=32766 border="0" color="33554432" x="407" y="1908" height="76" width="329" format="[general]" html.valueishtml="0"  name=uncmtops visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes edit.imemode=0  font.face="Arial" font.height="-10" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" )
export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 )
import.xml()
export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )

This nvo is PFC based but doesn’t have to be.
nvo_dbconncheck

$PBExportHeader$nvo_dbconncheck.sru
forward
global type nvo_dbconncheck from n_base
end type
end forward

global type nvo_dbconncheck from n_base
end type
global nvo_dbconncheck nvo_dbconncheck

type variables
ulong	iul_connection
end variables

forward prototypes
public function boolean uf_checkconnection (string a_userid)
public subroutine uf_removeconnection ()
end prototypes

public function boolean uf_checkconnection (string a_userid);// this method looks for any connections for the current user.
// If there is a connection number for the user, check to see if 
// it is current.  
//
// If the connection is current, return false since  users are not 
// allowed to log on multiple times.
//
// If the connection is not current, update the row with the current
// connection number.
//
// If there is no connection number, insert the current user and connection
// number into the table.
//


boolean lb_return = TRUE
ulong lul_connection_old
ulong	lul_connection_new
long	ll_conrows, ll_connectionrow
datastore lds, lds1

IF IsNull(a_userid) OR (a_userid = '') THEN RETURN FALSE

lds = create datastore
lds.dataobject = 'd_sa_conn_info'
lds.settransobject(SQLCA)

lds1 = create datastore
lds1.dataobject = 'd_conn_number'
lds1.settransobject(SQLCA)
// is user already connected?
SELECT max(connectionid)
INTO :lul_connection_old
FROM sec_userconnection 
WHERE userid = :gv_userid;
IF IsNull(lul_connection_old) THEN lul_connection_old = 0
lb_return = TRUE
// current connection number
ll_connectionrow = lds1.retrieve(1)
IF ll_connectionrow > 0 THEN
	iul_connection = long(lds1.getitemstring(ll_connectionrow,'conn_number'))
	lb_return = TRUE
ELSE
	// can't get current connection, exit
	Messagebox('Access Error','Unable to determine database connection number!')
	lb_return = FALSE

END IF
IF lb_return THEN
	// check if users other connection number is valid (still connected)
	IF (lul_connection_old > 0) THEN
		// connection valid, user attempting second log on
		ll_conrows = lds.retrieve(lul_connection_old)
		IF (ll_conrows > 0) THEN
			Messagebox('Access Error','Multiple logons are not permitted!')
			lb_return = FALSE
		ELSE
			// old connection not valid (left over from some fatal error)
			// update with current connection number
			Update sec_userconnection
			set connectionid = :iul_connection
			WHERE connectionid = :lul_connection_old
			using sqlca;
			commit;
			lb_return = TRUE
		END IF
	ELSE
		// no previous connections so create one
		// insert record with connection number and user id
		insert into sec_userconnection
		select :iul_connection, :gv_userid;
		lb_return = TRUE
	END IF
END IF

IF IsValid(lds) THEN DESTROY lds
IF IsValid(lds1) THEN DESTROY lds1
RETURN lb_return

end function

public subroutine uf_removeconnection ();// delete users connection number from db
// so they are not seen as being logged on more than once.

delete from sec_userconnection
where	connectionid = :iul_connection
USING SQLCA;

commit;
end subroutine

event constructor;call super::constructor;// This object used to validate that a user has only one connection to the database
// when they launch the application.
end event

on nvo_dbconncheck.create
call super::create
end on

on nvo_dbconncheck.destroy
call super::destroy
end on

Create an instance of the nvo on the application object (again this is PFC based) and create in in the constructor event. In the pfc_close event on the application object insert the following and override the ancestor:

IF ISVALID(inv_conncheck) THEN 
	inv_conncheck.uf_removeconnection()
	DESTROY inv_conncheck
END IF
super::EVENT pfc_close()

In the event where the user logon is validated add the following:

IF (gnv_app.inv_conncheck.uf_checkconnection(gv_userid) = FALSE) THEN
// gv_userid is the user id for the application and database
	close(parent)
	return
END IF
]]>