New topic: RealSQLServer
<http://forums.realsoftware.com/viewtopic.php?t=30446> Page 1 of 1 [ 9 posts ] Previous topic | Next topic Author Message BillC Post subject: RealSQLServerPosted: Mon Oct 12, 2009 4:46 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 26 Location: Orange County Ca I am using the following code to try to populate a table with a list of words coming from a flat file. The words show up in the ListBox on the screen where it is processing but seem to fail to be inserted into the table. I have tried it both with and without the &apos on each side of the variable "word" with the same result. The error trap is somehow not correct but beyond that, the Insert statement must not be correct either. Any suggestions would be appreciated. Bill Code: dim rec as DatabaseRecord dim f as FolderItem dim DictFileName as string dim stream as textinputstream dim word as string dim myDB as new REALSQLServerDatabase DictFileName = tfLoad.Text f=GetFolderItem("C:/Documents and Settings/...)'gets flat file if f<>Nil then If f.exists then stream = TextInputStream.Open(f) While not stream.eof word=stream.ReadLine Word=Uppercase(trim(word)) lbLoad.addrow word rec=New DatabaseRecord rec.Column("Word") = word myDB.SQLExecute("INSERT INTO tblRawDict (Word) VALUES ("+Word+");") if myDB.error then app.DisplayDatabaseError False end if wend stream.close else MsgBox"The file does not exist" end if Else MsgBox "Bad Path" end if tfLoad.text="" Top Mike Bailey Post subject: Re: RealSQLServerPosted: Mon Oct 12, 2009 6:00 pm Joined: Wed Sep 28, 2005 8:30 am Posts: 5432 Location: Austin, TX I'm spotting a few things here: 1. You are never connecting to your database. You are instantiating a new REALSQLServerDatabase object into local variable myDB, but you never connect to the server, therefore the data does not have anywhere to go. 2. You need to choose either to use the DatabaseRecord class with Database.InsertRecord, or SQLExecute, but you do not need both. If you are using SQLExecute then you do need the single quotes around the value in the SQL command. Also make sure you properly escape the data if you use this technique (see my Tips thread at the top of this forum for more information on this). 3. Insert your records in a transaction and call Database.Commit at the end to make sure they are written to the database. Hope this helps. _________________ Mike Bailey Ekim Software http://www.ekimsoftware.com/ Top BillC Post subject: Re: RealSQLServerPosted: Mon Oct 12, 2009 8:17 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 26 Location: Orange County Ca Well, that is annoying. I had about 60 or so lines of message in this window and then some keystroke combination wiped it all away. I'll try again. I have a database connect in the app Open: Code:DIM mydb as REALSQLServerDatabase mydb=New REALSQLServerDatabase mydb.host="192.168.2.5" mydb.port=4430 mydb.UserName="admin" mydb.Password="admin" If mydb.Connect then MsgBox "Database Connect Successful!" if mydb.error then DisplayDatabaseError FALSE Quit END IF ELSE DisplayDatabaseError False Quit end if Note that I am using the IP address I see in the REAL Server Admin rather than the 127.0.0.1 that I see other places. I had tried the 127.0.0.1 earlier and thought that was perhaps part of the problem. For whatever reason, when I pasted the code snipet into the window, the comment single quotes were removed. The three lines dim rec... rec=... and Rec.Column... had been commented out. I had been wandering between code samples trying whichever to make it work. The insertrecord seemed perhaps to be more tied to REAL SQL rather than REALSQLServer. I haven't found much of anything that seems to be samples of the mehods for the REALSQLServer product. Virtually everything I see refers to REALSQL. I have the current code as: MYDB.SQLExecute("INSERT INTO tblRawDict (Word) VALUES ('"+Word+"');") I am puzzled as to why a string variable would need single quotes. I can understand a string litteral needing them since it has to distinguish between the string and the overall quotes themselves but a variable to me would not have that problem. Unfortunately, all the samples I see seem to have string litterals. Seems odd to me that the examples show litterals rather than variables. I was going to add the transaction and commit later to minimize my chances of failure. Somewhere in the docs, it says that if I don't do a transaction and commit, the program does an automatic commit when I close out. Speaking of which, that has also been giving me an error. (!) I have app.mydb.Close in the app Close process. Perhaps because it was never open? The line says app.mydb.Close. It passes the code test but generates a bug report when it actually tries to execute. I'm trying to follow the bouncing ball here but it isn't clear to me why this is failing. Thanks, Bill Top timhare Post subject: Re: RealSQLServerPosted: Mon Oct 12, 2009 9:55 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6540 Location: Portland, OR USA BillC wrote:Code: If mydb.Connect then MsgBox "Database Connect Successful!" if mydb.error then If Connect returns true, you won't have an error condition, so checking mydb.error here is pointless. Quote:I haven't found much of anything that seems to be samples of the mehods for the REALSQLServer product. Virtually everything I see refers to REALSQL. Once you get a connection, the commands are practically identical for all database types, so for simplicity, the examples use REALSQLdatabase. Quote:I have the current code as: MYDB.SQLExecute("INSERT INTO tblRawDict (Word) VALUES ('"+Word+"');") I am puzzled as to why a string variable would need single quotes. I can understand a string litteral needing them since it has to distinguish between the string and the overall quotes themselves but a variable to me would not have that problem. Unfortunately, all the samples I see seem to have string litterals. Seems odd to me that the examples show litterals rather than variables. This is the hardest part about using SQL. You are literally writing one language (sql) inside of another language (RB). And you're doing it by using string concatenation. It is very difficult to envision the resulting string. But let's take your example (replacing "'" with a single quote). Suppose the variable, Word, contained the value "abcd". When you replace the variable with its value, you get the following: MYDB.SQLExecute("INSERT INTO tblRawDict (Word) VALUES ('abcd')") As you see, the value nestles down in between the single quotes, just like all the examples you have seen. It is very useful to write this kind of thing as: dim sql as string sql = "INSERT INTO tblRawDict (Word) VALUES '" + Word + "')" MYDB.SQLExecute(sql) That way, you can put a breakpoint at the SQLExecute and examine the string that you have constructed. Quote:that has also been giving me an error. (!) I have app.mydb.Close in the app Close process. Perhaps because it was never open? The line says app.mydb.Close. It passes the code test but generates a bug report when it actually tries to execute. The problem is that you are dimming a local database variable inside your method. That leaves your app.mydb variable untouched and still set to nil. If you want to use a single database instance that you connect to early and then use in all your code (which is a very good idea), don't dim any local database variables. Ever. Tim Top BillC Post subject: Re: RealSQLServer - TimPosted: Mon Oct 12, 2009 10:31 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 26 Location: Orange County Ca Hi Tim! Well, as you can tell I am struggling with this. I put the Insert into a string and put a break on the line and it returned the following: INSERT INTO tblRawDict(Word) Values('AARDVARK') When I try to check the code it fails on MYDB which is why I put in the dim database reference (which I have now removed). I changed the line to say app.mydb.SQLExecute(sql) where sql resolves to the string above and it gives me a bug when it hits that line. I assumed I needed the app. to resolve back to the open in the app open. I tried it with both IP addresses, same result. It gives me "An exception of class NilObjectException was not handled. The application must shut down." I think I am closer but still no cigar. Not that I want one anyway. ) I keep puzzling over the line : If mydb.Connect then It seems as though I am testing the connect but never do actually connect. Thank you for your assistance. It cleared up some of my confusion and I certainly like the use of the sql string to see what I am doing. Bill Top timhare Post subject: Re: RealSQLServerPosted: Mon Oct 12, 2009 10:38 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6540 Location: Portland, OR USA You need to figure out what is nil, and then why. Make sure you aren't still dimming mydb in app.Open. That would account for the error you're describing. mydb.Connect is not a test for whether the db is connected. It is a method that performs the actual connection. But you're not dealing with a problem of not being connected, you're dealing with an object that hasn't been initialized. Tim Top BillC Post subject: Re: RealSQLServer - TimPosted: Mon Oct 12, 2009 10:51 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 26 Location: Orange County Ca You are good! I did have the dim in the app.open. I pulled that out. I thought I needed to "initialize" it someplace. I still have a line: mydb=New REALSQLServerDatabase When I execute the line: app.mydb.SQLExecute(sql) if app.mydb.error then app.DISPLAYDATABASEERROR False It gives me a msgbox saying: "The operation couldn't be completed because no database has been selectd." I thought that after I told it mydb.connect it had identified the db I was opening. If you would be so kind, how do I "select" the db? Thanks, Bill Top timhare Post subject: Re: RealSQLServerPosted: Mon Oct 12, 2009 10:58 pm Joined: Fri Jan 06, 2006 3:21 pm Posts: 6540 Location: Portland, OR USA Not 100% sure of REALSQLServer, but you might have to set mydb.DatabaseName. When you add the property, mydb, to app, that allocates space for it, similar to "dim mydb", but more global in scope. If you "dim mydb", it is only available in that one method. Tim Top BillC Post subject: Re: RealSQLServer - TimPosted: Mon Oct 12, 2009 11:18 pm Joined: Sun Nov 25, 2007 12:02 pm Posts: 26 Location: Orange County Ca Hi Tim! In my App I have a global Property defined as: mydb as REALSQLServerDatabase that is global. Bill Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 9 posts ] -- 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]
