Active link table arguments without dialog box

[dordije]

Hi all.

Sorry for long explanation of my problem, but I can't explain it shorter. I have started to use Alpha Five V9 Platinum a month ago.

Suppose I have SLQ table named ARTICLES, say in FIREBIRD database. It is a table in which I intend to save articles for several firms, in which every firm has its own list of articles. Simplified, this table have next structure:

ARTICLEID Numeric This is ID for article row
FIRMID Numeric This is ID of firm which are articles
ARTICLENAME Character This is article for FIRMID

I decided to make application with data of several firms in one database, and I can,t change that because it is important for my users.

In the begining of application I have a form for loging in user and then another form with browse for selecting firm. After that form with browse I have value for FIRMID of selected firm, which I put in GLOBAL variable glFIRMID. Next I open third form with many buttons for working with selected firm's data. One of them is intended for opening form for entering articles for selected firm.

After that I created Active Link Table based on ARTICLES table. During creation process I formed SELECT statement: SELECT * FROM ARTICLES WHERE FIRMID = :argFIRMID. Also, Alpha formed INSERT statement: INSERT INTO ARTICLES (ARTICLEID,FIRMID,ARTICLENAME) VALUES (:new.ARTICLEID,:new.FIRMID,:new.ARTICLENAME).

When I click on mentioned button I get dialog box prompting me for value for argument argFIRMID. Well, as I already have that value in glFIRMID variable, I would like not to get that prompt but to transfer my global variable value to SELECT statement as value for argument. I read some messages and texts about SQL::Arguments but did not succeded. I tried several combinations but without result. Now I do not know how to do that (what code to write) and where (in what event of what object) to put that code.

Same thing is with INSERT statement. Can I write that statement with :glFIRMID instead :new.FIRMID ? How to pass glFIRMID to INSERT statement ?

This is big step for me but I am totally stoped, I can't go further. I will have many other tables with same problem.

Thank you in advance.

Dordije.

[MikeC]

Re: Active link table arguments without dialog box

Hi,
I have not used Active linked tables but imagine the same tactic is used as with Passive linked tables which I have used....you basically have to add the argument with script instead of adding it at runtime as you are doing. Check out this link from the help file and it should be what you need I think...

http://support.alphasoftware.com/alp...nts__Add().htm

[dordije]

Re: Active link table arguments without dialog box

Thanks Mike,

I visited text you pointed me to, but I still need to do thing with active linked table, in real-time.

Meanwhile, I tried opening my form (which is based on active linked table ARTICLES), with command:

f = form.view("frmARTICLES","","","","",args)

I found this in some other thread, and I think it will solve me the first problem with SELECT statement.

Still I am searching to find solution for passing global variable value to INSERT statement. How can I put some value to :new.FIELDX argument of INSERT statement ?

Thanks, Dordije.

[jaryder]

Re: Active link table arguments without dialog box

Dordije.

Here is a function I wrote to change the password for a login ID where the ID and password are stored in an active SQL table. I do a select to find the ID and then do an update. Pay attention to the dim vArgs and vArgs.add command.

FUNCTION ChangePassword AS V ( )
	
	'Change password
	DIM SHARED vLogin as C = ""
	DIM SHARED vOldPwd as C = ""
	DIM SHARED vNewPwd1 as C = ""
	DIM SHARED vNewPwd2 as C = ""
	DIM SHARED vResult as C
	DIM SHARED vOKflag as L = .F.
	
	vResult=ui_dlg_box("Change Password",<<%dlg%
{Windowstyle=Gradient Horizontal Middle}
{font=arial,10}
{region}
Login:| [.16vLogin!okchk];
Old Password:| [%p%.16vOldPwd!okchk];
New Password:| [%p%.16vNewPwd1!okchk];
Reenter New:| [%p%.16vNewPwd2!okchk];
{endregion};
{line=1,0};
{region}
<*15&OK!OK?vOKflag> <15&Cancel!CANCEL>
{endregion};
%dlg%,<<%code%
	IF a_dlg_button = "okchk" THEN
		a_dlg_button = ""
		vOKflag = (vLogin!="" .and. vOldPwd!="" .and. vNewPwd1!="" .and. vNewPwd2!="")
	END IF
%code%)
	
	IF vResult = "CANCEL" THEN
		end
	END IF
	
	dim vConn as SQL::Connection
	dim vArgs as SQL::Arguments
	dim vSql as c
	
	'open an sql connection
	IF .not. Vconn.Open("::Name::MySqlServer") THEN
		ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + vConn.CallResult.text)
		end
	END IF
	
	vSql=<<%sql%
Select emplogin, emppassword 
from employee  
where emplogin = :login 
%sql%
	
	'add variable arguments
	vArgs.add("login",vLogin)
	
	'run query
	IF .not. vConn.execute (vSql,vArgs) THEN
		ui_msg_box("Error","Could not execute SQL for a database. Error reported was: " + crlf() + vConn.CallResult.text)
	END IF
	
	'check for valid login and password
	IF vConn.ResultSet.GoToRow(1) THEN
		IF vConn.ResultSet.data("login") <> vLogin .or. decrypt_string(vConn.ResultSet.data("password"),"XYZZY") <> vOldPwd THEN
			ui_msg_box("Change Password Message","Login failed.  Password not changed.",UI_ATTENTION_SYMBOL+UI_OK)
			vConn.close()
			end
		END IF
	ELSE
		ui_msg_box("Change Password Message","Login failed.  Password not changed.",UI_ATTENTION_SYMBOL+UI_OK)
		vConn.close()
		end
	END IF
	
	'check for match in new passwords
	IF vNewPwd1 <> vNewPwd2 THEN
		ui_msg_box("Change Password Message","New passwords do not match.",UI_ATTENTION_SYMBOL+UI_OK)
		vPptr.close()
		end
	END IF
	
	'encript password
	vNewpwd1=encrypt_string(alltrim(vNewpwd2),"XYZZY")
	
	'time to update password
	vSql=<<%sql%
UPDATE employee 
SET emppassword = :newpassword  
WHERE emplogin = :login
%sql%
	
	'add variable arguments
	vArgs.add("newpassword",vNewPwd1)
	
	IF .not. vConn.Execute(vSql,vArgs)
		ui_msg_box("Error","Record was not updated. Error reported was: " + crlf(2) + vConn.CallResult.text,UI_STOP_SYMBOL)
	ELSE
		IF Vconn.AffectedRows() < 1 THEN
			ui_msg_box("Error","Record was not updated." ,UI_STOP_SYMBOL)
		END IF
	END IF
	
	'Now, close the connection
	vConn.close()
	
END FUNCTION

[dordije]

Re: Active link table arguments without dialog box

Jeff,

thanks for sharing your code. You used Xdialog-style for working with tables. In this moment I am not good with that way of making dialogs. So I decided to use A5 designer to create forms and browses, based on active link tables. Your code will teach me to do things in your way.

I made form frmARTICLES based on active link table ARTICLES, with SELECT and INSERT statements as I described (created by A5; I only added WHERE clause, during creation process of that form).

Yesterday i put this code in button for opening form frmARTICLES:

OPTION STRICT
DIM mArgs As SQL::Arguments
ON ERROR GOTO Error_Handler
mArgs.Add("argFIRMID",glFIRMID)
FORM.view("frmARTICLES","","","","",mArgs)
END
Error_Handler:
script_play("Error_Handler_01")

It seems to me that this will work what I want: to open A5 designer generated form frmARTICLES with data that satisfy WHERE FIRMID = :argFIRMID clause. I am not quite sure about it because I need more time to test it. If this will work, then I only need solution for INSERT statament.

But if this will not work, I must make forms (dialogs) using Xdialog-style mechanisam.

Thanks, Dordije.

[dordije]

Re: Active link table arguments without dialog box

I positively realized that this command:

FORM.view("frmARTICLES","","","","",mArgs)

did the job, but only for SELECT statement for a form based on Active link table ARTICLES. SELECT statement is:

SELECT statement: SELECT * FROM ARTICLES WHERE FIRMID = :argFIRMID

It accepts argument from mArgs and WHERE clause in SELECT statement works well.

I think that A5 lacks deeper information about Active link tables and its whole mechanisam.

I still do not know how to touch arguments in INSERT statement. I am talking about forms created by A5 forms designer and based on Active link table (not for xDialog created forms). When I Edit Active table Link definition , A5 permits me to edit SELECT, UPDATE and INSERT statement. In INSERT statement A5 puts its arguments in form :new.FIELDNAME. For example, A5 created for me statement:

INSERT INTO ARTICLES (ARTICLEID,FIRMID,ARTICLENAME) VALUES (:new.ARTICLEID,:new.FIRMID,:new.ARTICLENAME)

Suppose I have value for FIRMID in global variable glFIRMID. I do not have that field on form, because I catched this value somwhere earlier in application. Is there are any way I can pass this value to INSERT statement when working with form based on ARTICLES table ?

Thanks,

Dordije.

[Tim Kiebert]

Re: Active link table arguments without dialog box

Dordije,
I am just starting to use SQL so i was interested in your question re using a global variable in the insert statement of the Active link definition. After numerous attempts I have come to the conclusion that this is not possible.

However, it looks like you have the destination field in the active link table so you can get the glFIRMID variable into the FIRMID field by using field rules. (you could do it with a form level event as well but I prefer field rules for this type of action.)

As you have found, (I read in one of your posts), you cannot use a global variable in the default value field rule. (you can for a normal dbf table but not an active link table. I don't know why. Maybe it is a bug.) But we won't let that stop us. :)

In either the OnEnterRecord or CanSaveRecord record events place this code.

t=table.current()
dim global glFIRMID as C [COLOR=Blue] 'could also declare this variable on the 'big V' button while the table is in edit structure mode[/COLOR]
t.FIRMID = glFIRMID

As is implied the OnEnter will only trigger once and the CanSave will fire on every save so you can pick your poison.
The CanSave is used instead of the OnSave because then the record is still in enter mode and table pointer is still on the record where we want the data. If the OnSave is used you would need to open the table again find the correct record and put the record in change mode.

When the record is commited the SQL table will have FIRMID inserted along with everything else.

Hope this helps. I am just learning this side of Alpha as well so no guarantees that this is the best way. It most certainly is not the only way.

[dordije]

Re: Active link table arguments without dialog box

Tim,

what you proposed is BINGO ! Great fo me and thank you ! That really works good !! I used this in CanSave event of embedded browse:

DIM t As P
t=table.current()
t.FIRMID = glFIRMID

Also, I without any problems used global variable glFIRMID dimmed early in Autoexec script. I did not needed to declare it on the form I worked with.

Your conclusion: "you cannot use a global variable in the default value field rule. (you can for a normal dbf table but not an active link table. I don't know why. Maybe it is a bug.)" really deserves some explanation of A5 employee members who participate in this message board. I still think that they must give us some more text about active link table mechanism.

Thanks again !

Dordije.