on-rev database help

[Bob Sneidar]

Hi Bill. 

It is my opinion that SQL is not difficult to learn in theory. It is difficult to master in reality. Syntax is a big issue, along with making sure your data is "escaped" or formatted in such a way that your data does not contain characters that can alter your queries, like commas or quotes. Different engines subscribe to different syntax and subsets of syntax, so you need to know the engine you are working with. 

Also the kind of encoding your database uses comes into play if you are using any kind of data that contains non-ASCII characters. I know you could say, "I won't be doing that" but can you depend on your users to not use special characters? If someone CAN screw things up, they eventually WILL screw things up. You can't trust to that. You have to escape data that users can alter. 

Another issue is knowing how to plan your table structures. For small amounts of data, this is unnecessary, but for large databases, it is absolutely essential. Also, when you get into very large data sets, the limitations of Revolution begin to come into play. So far as I know, all cursors are stored in real memory and not cached to disk, so that can become an issue, but for your application, I don't think you will ever get there. 

Now there IS a product produced by Trevor Devore called sqlYoga, which takes care of most of these issues for you. Without knowing much at all about syntax, you can pass data to his API and trust that your data will get to your databases. When you first initialize sqlYoga, it will read the structure of your database itself, so you don't even have to bother with that. It can save that information to an "object" (a hidden button actually) as a kind of caching mechanism. After that, you store data to variables, and then call commands that take care of escaping the data and updating your databases for you. 

Like any new way of doing things, sqlYoga takes a little practice and getting used to, but once you "get it" you will find accessing multiuser databases a snap. 

So the answer to using On-Rev as a multiuser database is, you absolutely CAN do this! I am doing it now. By default though, On-Rev has IP address blocking, so you have to enable the IP addresses that users will be connecting from. This can be a hassle for mobile users with laptops. It is definitely NOT recommended to access remote SQL servers without this kind of protection, as there are ways to "get at" SQL databases, even if they are firewalled, by using the query language itself. 

Speed is also a bit of an issue. Since it's remote, your queries can take a few seconds at it's worst to return. Finally, be aware that idle connections are dumped from server side after a while, so you need to get into the habit of getting in, getting data and getting out. You can't assume the connection is going to still be there if you leave it idle for any length of time. 

Bob


On Oct 16, 2010, at 11:34 AM, Bill Ziegler wrote:

> I'm not even sure if I'm asking the question correctly.
> 
> I've made a simple time tracking database for my colleagues. Dates, fill-ins, drop downs etc. I'm not using any of the sophisticated Rev DB tools or SQL. I am just saving each collection of responses in separate fields. I also have a button that exports as a text file all info with records on separate lines, commas between items. Each teacher keeps their own stack on their laptop. When our supervisor needs to do a report for the school districts, each of the 13 teacher/consultants will generate the data-text file and send it to her. She will then import each text file into excel to do her sorts and reports.
> 
> Every time she needs to do an update, she'll need to ask us to send our latest data and create a new excel to keep the data correct. I do have an on-rev server account. I did attend and have the dvds for rev-live Las Vegas. I do own the DVDs from the On-Rev Edinburgh conference, but haven't had time to watch them yet. I know RunRev/Live Code fairly well. I do not know SQL.
> 
> Questions
> 
> 1. Can I put my existing stack on my on-rev server account for multiple people to use at one time?
> 
> 2. Can I put my existing stack on our school server for multiple people to use at one time?
> 
> 3. Am i up-that-creek for not knowing SQL?
> 
> 4. If (I) and (2) are possible with or without SQL what's my best option to learn how to do whatever I'll need to do.
> 
> Thanks,
> 
> Bill
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution