Ask Your Question
0

How to create return a identication string using the DB UserID

asked 2015-07-22 12:45:12 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I have a database with users. The users could be teachers, but they could as well be students, so they could do inscriptions on classes. Relation Diagram here:

image description

When I'm creating the class a form I face a problem. How can I get the user name in order to get the correct teacherID? I would like to associate the teacher to the class.

Thank you

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-07-22 14:22:55 +0200

doug gravatar image

updated 2015-07-22 15:14:53 +0200

Base has "User Administration" functionality under "Tools" in the menu, which is active only when the database is "split," meaning it is not an internal HSQLDB. The functionality allows different users to log in through the same LO database front-end. You can query the back-end for the current user by a variety of methods. HSQLDB provides USER() and CURRENT_USER functions, the latter being standard across platforms, but again LO supplies this functionality only when split (apparently-- that is the only situation when it works for me).

There is not a way to set a form's default value to a function. Thus, you would use a macro to set the value of a blank control using the back-end's CURRENT_USER as follows:

REM  *****  BASIC  *****

Sub UpUsr
priForm = ThisComponent.DrawPage.Forms.getByName("MainForm")

txbx1 = priForm.getByName("txbx1")

Conn = priForm.ActiveConnection
SQL = Conn.CreateStatement()
SQLStr = "SELECT `TeacherID` FROM `Teacher` WHERE `UserID`=CURRENT_USER"
results = SQL.executeQuery(SQLStr)
results.first
usr = results.getString(1)

If txbx1.String = "" Then 
  txbx1.setString(usr)
  txbx1.Commit
End If

End Sub

The portion in quotes is the query that returns the TeacherID name from the database where the UserID matches the CURRENT_USER value for the database, in my case, doug@localhost. The Teachers table will have one UserID for each database credential, and a TeacherID associated with that credential.

Activate the macro from an appropriate Form event, such as Before Record Action and it will update the text box named txbx1 located on the form named MainForm (see Form Navigator).

(if this answers your question, please accept the answer by clicking the check (image description) to the left)

edit flag offensive delete link more
0

answered 2015-07-22 19:56:42 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I think that I wasn't totally clear. When I'm writting the classes form, I just want to assign the correct Teachers Id to the Classes. However. the name and the other teacher's data is stored on users table. How can I fill the teachersID in the Classes Form quering the usersID name? Assuming the user already has a teacher ID. I can create a list of teachersID, but there are only numbers, I need to relate the TeachersID with UsersID to get the Users name.

edit flag offensive delete link more

Comments

What you describe is accomplished by the listbox or combo box control. The name to be displayed is in the first column for a listbox, the bound column is a subsequent column. The Teachers table would the be source for the list, or a query reorganizing the contents of the table.

doug gravatar imagedoug ( 2015-07-23 01:56:47 +0200 )edit

I've already tried this solution. However in the Teachers table I've only have the usersId not the usersId name. Do I need a listbox with a query?

marcoE gravatar imagemarcoE ( 2015-07-23 11:13:56 +0200 )edit

Listbox would be for data entry, the data source can be stored query, table, view, or ad-hoc SQL. If you already have a unique piece of data for teachers, which is not clear from the diagram, then you could pull in teacher names or user ids with a JOIN query. You also could use a JOIN query as the record source for your list.

doug gravatar imagedoug ( 2015-07-23 13:24:34 +0200 )edit

Thank you ! Could you give an join query example? I know how to do simple queries, and I've already done some at my project, but I'm kind of noob with Obase =)

marcoE gravatar imagemarcoE ( 2015-07-23 14:22:35 +0200 )edit

Ok, there are two basic ways. The simpler syntax is : SELECT "a"."UserID", "b"."UserName" FROM "Tbl1" AS "a" JOIN "Tbl2" AS "b" ON ("a"."UserID"="b"."UserID"). After the SELECT are the columns to display from the two tables that you have glued together and assigned the alias' "a" and "b". To say which column from which table you use the alias plus a . and the column name. Also look up LEFT JOIN and 'subquery' for other examples. Use double-quote or backtick to surround names.

doug gravatar imagedoug ( 2015-07-23 18:42:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-22 12:45:12 +0200

Seen: 132 times

Last updated: Jul 22 '15