9 Replies Latest reply: Oct 21, 2011 4:35 AM by Cédric_Courlet RSS

    derby embedded question

    bobba1 Community Member

      Hello - I am a flash builder developer who has previously worked with mySQL/php, and I am very, very new to cf -- and have a very basic question developing CFB/Flash builder projects using derby embedded as the DB. I just called my webhosting service (a shared resource on crystaltech) and tech support told me I probably could not host a CF project on their server if it was developed using derby embedded, as their IIS-based servers could noptm run apache derby (??) but rather only db's like SQL, mySQL, etc.

       

      Does anyone know if the tech guy (who didn't sound like he was sure) is missing something? I assumed that derby embedded was included in CF9 because it would make integrated development easier, so I was going to use it instead of mySQL, but now I am concerned I will run into problems on my production server.

       

      Any thoughts on this?

       

      Thank you.

       

      Bob

        • 1. Re: derby embedded question
          charlie@carehart.org Community Member

          Yes, he is mistaken. Derby is in fact embedded in CF 8 and 9 (Standard and Enterprise). There's nothing that he (or you) need to do to "enable it". It's enabled by default. You (and/or he) will find that the CF Admin DSN page offers Derby Embedded as a driver option. Point to it, and either use the checkbox there to say that you're creating a new DB, or place your DB files in the appropriate place in CF (\db\[dbname], by default), if moving them from another server. It's the latter which may trip up a host, if they're not prepared to make it easy/possible for you to put Derby files (the folder in which they are stored) into the appropriate location within CF that they are expected to be found.

           

          Very few people appreciate Derby and its value, especially hosts I suspect. You can point them to the CF docs. This is a documented feature. I also have done presentations on the topic (see http://www.carehart.org/presentations/) and articles (carehart.org/articles/), which could help and also point to still more resources.

           

          Hope that helps.

           

          /charlie

          Providing CF troubleshooting services at carehart.org/consulting

          charlie@carehart.org

          • 2. Re: derby embedded question
            bobba1 Community Member

            Thanks very much for the reply, Charlie. I called my webhost tech support and sent them your posted reply -- this is what they said (below); from their response it appears as if derbyembedded is not an option for me. it is surprising, because crystaltech (my web host) promote themselves as aggressively supporting CF9, so it is a bit odd that they cannot support the derby option. I guess I'll need to use mysql, unless I am (or they are) missing something.

             

            "Hello - I checked with my lead on using the derby databases and he informed me this is not an option on our servers. We do not offer shared Linux hosting, which is where this database works best. I did find a page that would allow the dervy databases to work on a windows environment (IIS), but due to being in a shared environment, we are unable to allow it. You would need to use a MySQL, MS SQL, or access database"

             

            I did forward your post ot this tech, so hopefully he understood that it is an embedded database, but perhaps the issue is placing the file under their CF9 root in a shared environment. Thanks again for your reply, Charlie.

             

            I guess I'll try and get it to work with mySQL

             

             

             

            Best,

             

            Bob

            • 3. Re: derby embedded question
              Jochem van Dieten Community Member

              The files for the Derby database can be placed anywhere on the filesystem, they do not need to be in any shared root.

               

              I do not think there is any technical or security reason why it would not work on a Windows shared server. However, your host may have policy reasons for not allowing it. They may not have trained their support personel, they may not know how to deal with backups, they may not have integrated it with their control panel or whatever. The bottom line is that I doubt you are going to win this one with your current host.

              • 4. Re: derby embedded question
                bobba1 Community Member

                Thanks for the post, Joachim.

                 

                Would anyone know of a good best hosting service for CF9, where I could execute the derbyembedded database? It looks like I won't have luck on crystaltech.

                 

                Thanks,

                 

                Bob

                • 5. Re: derby embedded question
                  Jochem van Dieten Community Member

                  Charlie maintains a great list of resources at http://www.cf411.com/ I am sure he has a list of hosting providers there.

                  • 6. Re: derby embedded question
                    charlie@carehart.org Community Member

                    Wow, I think they're just confused. There's no option to enable/disable. It's definitely NOT limited to any OS. Derby is based on Java. In fact, for the past couple of years it's been bundled WITH java (as JavaDB, just a new name for the same engine).

                     

                    They may be thinking about the Derby Server version: that's where you start up a new server process to run the DB out of process from CF. Again, that's a built-in feature of CF. (One need not install anything if one DID want to use that option.)

                     

                    And to be clear, Derby is not new in CF9. It was added in CF8. It is by far a very poorly understood DB. I'm actually surprised no one has yet asked, "why do you want to even bother with Derby anyway? It's not a production database". Again, that's just not true. It IS intended for production use. And it is also NOT a single-user DB (as SQLite is), nor is it hamstrung for multi-threaded use as MS Access is.

                     

                    Still, one may wonder: why don't you just punt and use what your host offers? Is there anything about Derby you need? Is it perhaps because hosts may charge for adding DB's and you hoped that this might be free? That's not likely going to be so. In fact, part of the problem is that hosts DO often provide custom mechanisms to add new DSNs, and they may not yet have added support for adding Derby ones.

                     

                    Indeed, that leads nicely to Jochem's observation in the next message of the thread. I'll reply with a thought there.

                     

                    /charlie

                    • 7. Re: derby embedded question
                      charlie@carehart.org Community Member

                      Yes, that's right. while the default location is \root, that's really only because Adobe offers sample DBs and they had to put them somewhere. One creating a new Derby Embedded database can put it anywhere, so again this is more about the hosts being willing to update their automation mechanisms for adding new DSNs. And as Jochem rightly points out, some who propose to offer other things (support, backups, security) may feel that Derby is an unknown for them.

                       

                      /charlie

                      • 8. Re: derby embedded question
                        charlie@carehart.org Community Member

                        Thanks, Jochem. Yep, I do have a category for CF hosts there (one of over 125 categories of over 1,000 tools and resources for CFers). But I will note that I don't distinguish which hosts support different features. You'd have to investigate that, Bob. Sadly, I doubt any will promote the fact that they support Derby. Instead, I imagine it will be more like you'll be lucky to find one who does support it because they've updated their DSN automation mechanism to create Derby DBs. It's not outlandish to think a host might, if they want are inclined just to keep up with whatever CF offers. But as you're experiencing with CT, they may let it slip through the cracks. (It's also possible, though, that it could just "work", even if a host said they were not aware of it being supported. Worth a try, for anyone else in this situation of Bob's.)

                         

                        /charlie

                        • 9. Re: derby embedded question
                          Cédric_Courlet

                          When you create the DB from the ColdFusion administrator, CGLOBAL & CDATA can't be created on ColdFusion 8. This is possible on ColdFusion 9.

                           

                          To to this manually you can use this method.

                           

                           

                          1) Stop the instance

                           

                          2) Then connect to the created db with derbyrun :

                           

                          /opt/jrun4/servers/cfusion15311/cfusion-ear/cfusion-war/WEB-INF/cfusion/db

                          => java -jar ../lib/derbyrun.jar ij

                          ij> connect 'jdbc:derby:cf_variables_storage';

                          ij> CREATE TABLE CDATA

                          (

                              CFID                                                VARCHAR(64),

                              APP                                                 VARCHAR(64),

                              DATA                                                LONG VARCHAR

                          );

                          0 rows inserted/updated/deleted

                          ij> CREATE TABLE CGLOBAL

                          (

                              CFID                                                VARCHAR(64),

                              DATA                                                LONG VARCHAR,

                              LVISIT                                              DATE

                          );

                          0 rows inserted/updated/deleted

                          ij> CREATE INDEX ID2 ON CGLOBAL(CFID);

                          0 rows inserted/updated/deleted

                          CREATE INDEX ID3 ON CGLOBAL(LVISIT);

                          0 rows inserted/updated/deleted

                          ij> CREATE UNIQUE INDEX ID1 ON CDATA(CFID, APP);

                          0 rows inserted/updated/deleted

                          ij> exit ;

                          /opt/jrun4/servers/cfusion15311/cfusion-ear/cfusion-war/WEB-INF/cfusion/db

                           

                          3) Then restart the instance...

                           

                          Regards.