New topic: Noob RB and Databases - Boolean Fields
<http://forums.realsoftware.com/viewtopic.php?t=30596> Page 1 of 2 [ 17 posts ] Go to page 1, 2 Next Previous topic | Next topic Author Message flyingmd Post subject: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 6:44 am Joined: Thu Oct 22, 2009 5:53 am Posts: 6 I am an RB newbie and this is my first post so I apologize in advance if I am not providing enough information or asking an in appropriate question. I am working on a database application the main interface is a window with several tabs for filling in patient information. I worked through the database example and actually got the darn thing working... I started out with only a few fields in the database and one table in order to get everything working first. I used jay jennings video tutorial and the RB sample database project. I have the first few fields working. They are VARCHAR fields which are grabbed from textedit fields. There is a listbox and detail screen. I am able to select a record in the listbox, have it show up in the detail form and can edit and save to the database. I can create new records and delete records. Ok, so here is my problem and I have searched this forum and several other resources and cant figure out how to do the following: I am trying to added a bunch of boolean fields. I am using a groupbox with 2 radiobuttons. The groupbox is named gropubox4 (for the time being) and the radiobuttons are radiobutton3 and radiobutton4. If the user selects radiobutton3 then I want to store the value true in the database but have it show as "STD-CPR" in the listbox. If the user selects radiobutton4 then I want to store the value true in the database but have it show as "IAC-CPR" in the listbox. Here is the code that I am using to generate the recordset (I have not put in the error checking yet) and populate the listbox. //code to populate the list with whatever data is in database dim dr as new DatabaseRecord dim sql as string sql = "select Lastname,Firstname,MiddleInit, CPRType from patient" dim rs as RecordSet = IACCPRDB.SQLSelect(sql) dim s as String while not rs.EOF lboxPatient.AddRow(rs.Field("Lastname").StringValue) lboxPatient.Cell(lboxPatient.LastIndex, 1) =rs.Field("Firstname").StringValue lboxPatient.Cell(lboxPatient.LastIndex, 2) =rs.Field("MiddleInit").StringValue If rs.field("CPRType").BooleanValue=false then lboxPatient.Cell(lboxPatient.LastIndex, 3) = "STD-CPR" elseif rs.Field("CPRType").BooleanValue=true then lboxPatient.Cell(lboxPatient.LastIndex, 3) = "IAC-CPR" End If rs.MoveNext wend rs.Close No matter what I select when I close out the app and reopen, the listbox is populated with all STD-CPR in that column. I have seperate methods for addrecord and editrecord. addrecord method //add patient //add to Database dim dr as new DatabaseRecord dr.Column("LastName") = trim(tfLastName.text) dr.Column("FirstName") = trim(tfFirstName.Text) dr.Column("MiddleInit") = trim(tfMiddleInit.Text) dr.BooleanColumn("CPRType")=RadioButton3.Value DIM s as string if RadioButton4.Value = true then s= "IAC-CPR" Elseif s= "STD-CPR" then end if IACCPRDB.InsertRecord "Patient", dr //insert the record dr into table patient IACCPRDB.Commit //add to list lboxPatient.AddRow(trim(tfLastName.text)) lboxPatient.Cell(lboxPatient.LastIndex, 1) = trim(tfFirstName.Text) lboxPatient.Cell(lboxPatient.LastIndex, 2) = trim(tfMiddleInit.Text) lboxPatient.Cell(lboxPatient.LastIndex,3)=S //tidy up tfLastName.Text ="" tfFirstName.Text ="" tfMiddleInit.Text = "" GroupBox2.Enabled = False updaterow method dim dr as new DatabaseRecord dim sql as string sql = "SELECT Lastname, Firstname, MiddleInit, CPRType FROM patient WHERE Lastname = '"+ lboxPatient.Cell(lboxPatient.Listindex,0) + "' and Firstname = '"+ lboxPatient.Cell(lboxPatient.ListIndex,1) + "' and MiddleInit = '" + lboxPatient.Cell(lboxPatient.ListIndex,2) + "' and CPRType = '" + lboxPatient.Cell(lboxPatient.ListIndex,3) +" ' " dim rs as RecordSet = IACCPRDB.SQLSelect(sql) rs.Edit rs.Field("Lastname").StringValue=trim(tfLastName.Text) rs.Field("Firstname").StringValue=trim(tfFirstName.Text) rs.Field("MiddleInit").StringValue=trim(tfMiddleInit.Text) if lboxPatient.Cell(lboxPatient.Listindex,3)="STD-CPR" then rs.Field("CPRType").BooleanValue=false else rs.Field("CPRType").BooleanValue=true end if rs.update IACCPRDB.commit rs.Close lboxPatient.Cell(lboxPatient.listindex,0)=trim(tfLastName.Text) lboxPatient.Cell(lboxPatient.listindex,1) =trim(tfFirstName.Text) lboxPatient.Cell(lboxPatient.listindex,2)=trim(tfMiddleInit.Text) Can anybody see what I am doing wrong? Again, I apologize in advance if I did not present this in the right format. This is my first post. Also, I have a bunch of checkboxs which I want to store in the database as booleans and haven't even begun to approach those yet. I am gonna have the same problem. Thanks in advance for any help. Reading through this forum has been invaluable for helping me get started with RealBasic. Top flyingmd Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 7:03 am Joined: Thu Oct 22, 2009 5:53 am Posts: 6 just a correction to my original post. I realized I wrote "true" twice in my description of the issue. That isn't the case nor the problem. If a user selects radiobutton3 then I want to store "true" in the database and show "STD-CPR" in the listbox. If a user selects radiobutton4 then I want to store FALSE in the database and show "IAC-CPR" in the listbox. Thanks Top DaveS Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 8:30 am Joined: Sun Aug 05, 2007 10:46 am Posts: 1677 Location: San Diego, CA Hope your application meets HIPPA audit requirements _________________ Dave Sisemore MacPro, OSX 10.5.8 RB2009r2 Top flyingmd Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 8:38 am Joined: Thu Oct 22, 2009 5:53 am Posts: 6 Appreciate that advice. I am painfully aware of HIPPA requirements being a physician in practice and understand that it applies here as well. The app will only be used for the collection of data in connection with a research study. Only the data collection team will have access to it. This helps. Would be a whole other ball of wax if this was going to be put into commercial production. Fortunately/Unfortunately, my programming skills aren't ready for prime time just yet. Ahhh, but one day........ (ain't giving up my day job just yet. LOL) Top cebe Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 9:02 am Joined: Fri May 16, 2008 8:00 am Posts: 30 Location: Germany I hope you both mean "HIPAA". Top DaveS Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 9:07 am Joined: Sun Aug 05, 2007 10:46 am Posts: 1677 Location: San Diego, CA yeah... that one Health Insurance Portability and Accountability Act Health Information Patient Privacy Act is what it should be _________________ Dave Sisemore MacPro, OSX 10.5.8 RB2009r2 Top flyingmd Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 9:25 am Joined: Thu Oct 22, 2009 5:53 am Posts: 6 yes - but if don't get help with my original pleas it won't matter. This is starting to get off topic. Any help with the coding question? Top DaveS Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 9:32 am Joined: Sun Aug 05, 2007 10:46 am Posts: 1677 Location: San Diego, CA Sorry.. but I can't really tell what your issue is? to populate a checkbox from a field... use the BOOLEANVALUE property of the FIELDS property _________________ Dave Sisemore MacPro, OSX 10.5.8 RB2009r2 Top simonjohn Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 9:37 am Joined: Sat Apr 19, 2008 12:44 pm Posts: 231 Location: Dorset, UK The RealSQLDatabase is a SQLite Database, SQLite does not have a Boolean datatype. So, the results you get trying to use the RB BooleanValue are very unpredictable. You CAN create a SQLite table with a Boolean datatype (or any datatype that exists or does not exist!) but SQLite will just ignore you! ( I have a little bit of info on SQLite Data Types in my SQLite Tutorial athttp://qisql.com/qisql_sqlite_datatypes.html ) The best way to deal with Booleans in a SQLite database is to populate the columns with integer value 0 or 1 _________________ Simon Larkin QiSQL Database Solutions Website : http://www.qisql.com Mac 10.5.8, Imac 2.4ghz, 4gb, RB 2009 R4 Top DaveS Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 9:45 am Joined: Sun Aug 05, 2007 10:46 am Posts: 1677 Location: San Diego, CA Sorry... but I disagree.... I created a table with BOOLEAN datatypes.... and it works just fine..... and it populates Checkboxes and reads from Checkboxes...... _________________ Dave Sisemore MacPro, OSX 10.5.8 RB2009r2 Top flyingmd Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 10:14 am Joined: Thu Oct 22, 2009 5:53 am Posts: 6 I was thinking of just changing the field to a string variable and only allowing 2 variables. But from my understanding of SQLite is that it does accept booleans. Either way, the issue is the code to get from the radiobutton or checkbox into the database and listbox. Thats the issue I am having. I have not found any good examples and have searched this forum and every RB book I have. When talking about databases, the books all use text fields (editfield.text or textfield.text) as example code. Top DaveS Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 10:28 am Joined: Sun Aug 05, 2007 10:46 am Posts: 1677 Location: San Diego, CA here are some snippets from a medical program I am writing Code: track_bp=(rs.field("Track_BP").BooleanValue) // track_bp is a boolean variable cb(0).Value=(rs.field("Track_BP").BooleanValue) // cb(0) is a checkbox // Inserting a Record if DB_Select("SELECT max(person_id) as id_num from USERS",rs) then next_id=rs.idxField(1).value+1 // Create a new database record for the person rec = New DatabaseRecord rec.IntegerColumn("Person_ID") = next_id rec.Column("Name") =Put_Safe(name) rec.Column("Gender") = gender rec.Column("birthday")=birthday rec.BooleanColumn("Track_BP")=track_bp rec.BooleanColumn("Track_Glucose")=track_glucose rec.BooleanColumn("Track_Cho")=track_cho rec.BooleanColumn("Track_Weight")=track_weight // Insert record into the database DB.InsertRecord "Users", rec if DB.Error then DB_Error false,"Insert New User" next_id=-1 end if end if // Updating a Record SQL= _ "UPDATE users "+_ "SET name ='"+name+"',"+_ " gender='"+gender+"',"+_ " birthday='"+birthday+"',"+_ "Track_BP="+str(flag(0))+","+_ "Track_Glucose="+str(flag(1))+","+_ "Track_Cho="+str(flag(2))+","+_ "Track_Weight="+str(flag(3))+_ " WHERE person_id="+str(dialog_setup) if not DB_Execute(SQL) then Dialog_Setup=-1 Hope this helps _________________ Dave Sisemore MacPro, OSX 10.5.8 RB2009r2 Top flyingmd Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 10:40 am Joined: Thu Oct 22, 2009 5:53 am Posts: 6 Dave, Thank you that is perfect. How do I reflect those boolean fields back i n the listbox. For instance, I have a listbox that allows the user to select a patient. Besides their name (which I have figured out) there is a column which tells what type of 2 resuscitation methods the patient received, either STD-CPR or IAC-CPR. The user selects a radiobutton, one of two and then the value (right now either true or false) is stored in the database, but I can't get the listbox to reflect which type of CPR. I suspect I am missing something in my populate listbox method. When I re open the database the column for CPR is all filled in with STD-CPR. Any ideas? Thanks in advance Top Phil M Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 11:13 am Joined: Fri Sep 30, 2005 12:18 pm Posts: 190 Sounds like you are always saving STD-CPR... check your code there. Radio buttons should be in a group box (which you said it was), and its easier to control their behavior when you make them into a Control array. Then you can do a select case by control index... Code:// in the Action for radio button control array Select Case index Case 0 // STD-CPR // save db field with 0 or False Case 1 // IAC-CPR // save db field with 1 or True End Select Code:// in your LoadPatientRecord( ) method If ( dbBooleanValue ) Then CPRradioButton( 1 ).Value = True Else CPRradioButton( 0 ).Value = True End If Top timhare Post subject: Re: Noob RB and Databases - Boolean FieldsPosted: Thu Oct 22, 2009 11:44 am Joined: Fri Jan 06, 2006 3:21 pm Posts: 6608 Location: Portland, OR USA I don't know if this is the problem, but this line is wrong Quote:sql = "SELECT Lastname, Firstname, MiddleInit, CPRType FROM patient WHERE Lastname = '"+ lboxPatient.Cell(lboxPatient.Listindex,0) + "' and Firstname = '"+ lboxPatient.Cell(lboxPatient.ListIndex,1) + "' and MiddleInit = '" + lboxPatient.Cell(lboxPatient.ListIndex,2) + "' and CPRType = '" + lboxPatient.Cell(lboxPatient.ListIndex,3) +" ' " The select should fail because you are testing CPRType against the string STD-CPR or IAC-CPR, not the boolean value stored in the database. You should just leave that part off of your select. BTW, that is a major issue when dealing with boolean columns in sqlite. As Simonjohn noted, sqlite doesn't have a true boolean data type, so whatever gets stored in the column is taken verbatim. You can't use TRUE/FALSE in an sql statement, it has to be 0/1 or whatever RB stores there. But you have no control over what RB stores, and you don't know what to expect there, unless you inspect the table to find out. I personally do not use boolean columns because of the differences between databases and the way they handle them. I use a one character field and store the string Y or N. It makes for better looking sql statements, in my opinion. And it's slightly more efficient for space than using an entire integer. But you can use whatever you want, as long as you're consistent. DaveS wrote:here are some snippets from a medical program I am writing I hope it's hippie compliant. Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 2 [ 17 posts ] Go to page 1, 2 Next -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
