12 Replies Latest reply on Feb 11, 2013 2:28 PM by Billy~Verreynne

    Oracle database front end

      Hello, I hope I'm in the right section here!

      I'm creating a management system for football for a project at uni. I've created all my tables using SQL Developer and done this using a localhost connection. All my tables and queries are running as I want in SQL Developer so the next stage is to create a front end for the database using Visual Basic. I've downloaded Microsoft Visual Studio 2012 and seemingly had successfully connected to my localhost database using the Tools tab in Visual Studio followed by 'Connect to Database'.

      At this stage I believed that I had successfully connected because all of the tables I had created in SQL Developer were showing down the left hand side of the Visual Studio page under the localhost connection in Server Explorer. I am able to right click any of my tables (e.g. players, managers, fixtures, etc) and select the 'Show Table Data' command.. This successfully displays all of the records that I had put in using SQL Developer. The really confusing bit starts here:

      When I go to try and actually write a new query in Visual Studio for any of these tables by right clicking them and selecting 'New Query', the Add Table box that comes up shows none of my tables and in fact only contains the default HR tables such as employees (HR), departments (HR), countries (HR), etc. The same applies if I try and select a data source for my Visual Studio project, it seemingly connects to my localhost database but then displays only the default tables.. none of my football related tables (which show up fine in the Server Explorer) are showing.

      I'm really confused at this stage, I'm new to Visual Studio (as you may have guessed!), but at this stage unless I've missed something obvious I just don't understand why I am unable to manipulate my tables.

      Does anyone have any ideas?

      I'll be happy to give any more info if needed! Thanks!!!
        • 1. Re: Oracle database front end
          Hi and welcome to the forums.

          Your issue isn't really related to Oracle's SQL or PL/SQL funcationality, but rather to the use of the Microsoft Visual Studio product.

          You would probably be better asking on the Visual Studio forums over at Microsoft:

          • 2. Re: Oracle database front end

            If you are using Oracle Developer Tools for Visual Studio you can have a look here: 5 Using Oracle Developer Tools for Visual Studio.

            At step 7 you should add the schema where your tables reside.

            If you are not using Oracle Developer Tools for Visual Studio then in this case I have to stick to what has been already told you by BluShadow.

            • 3. Re: Oracle database front end
              Any specific reason why you are using Visual Studio? That introduces another layer of complexities. And another language that you need to know, understand and use, in addition to the database SQL and PL/SQL.

              And PL/SQL and SQL are mandatory to know and use if you expect to develop a half decent performing and scalable system.

              My suggestion is to drop Visual Studio all together. That removes a very significant complexity. Instead, use Apex (Application Express).

              Apex has 2 components:
              - web application framework and run-time system (for running Apex apps)
              - a RAD GUI that only needs a web browser (IE, Firefox or Chrome) to create Apex apps

              Apex itself is written in PL/SQL and SQL. It resides in the database. If you have a default 11g database, you have Apex right now, already installed in the database.

              An Apex (web) application resides in the Oracle database. Is executed inside the database. Right next to where the application's data is. This means quick and fast access to database data, a lot faster than Visual Studio app (or any other app) can ever hope for.

              To use an Apex application requires a web server. You have 3 choices:
              - Oracle's Apache 2.x web server (also called OHS or Oracle Http Server)
              - Apex Listener (jar container that you can run stand-alone, or in a Java app server)
              - use the Oracle database's XDB servlet called EPG

              The easiest to get this to work with a couple of clicks - download and install Oracle XE (Express Edition). This is a free 11g database, that includes Apex, and comes with EPG configured for you. All you need to do after the install is click the Apex URL shortcut on your desktop to launch your default web browser and start using your XE database and Apex.

              Any other method requires a far steeper learning curve, and has significantly more moving parts, and more complexity. Apex is easy enough for beginners to use (with little to no web application and Oracle experience). Apex is flexible and powerful enough to be used for corporate systems (Oracle's cloud.oracle.com servers run Apex applications for cloud access and management - and many Oracle customers use Apex extensively).

              Not considering Apex would be a major mistake on your part IMO. Developing a proper .Net app using Visual Studio and Oracle, is not as easy and as simplistic as you may think. And locks you into proprietary framework that must run on Windows. Apex runs in the database - and the database runs on Windows, Linux, Solaris, AIX, HP-UX and so on.
              1 person found this helpful
              • 4. Re: Oracle database front end
                All of your suggestions are massively appreciated so Thankyou!! It's certainly given me a lot more to work with and the suggestion of Apex in particular sounds very appealing, I never even considered that! There's no restrictions on the front end I just chose (probably stupidly haha) to choose Visual Basic.

                Sorry if I posted in the wrong place too, ill be sure to have a look at all you've said over today and let you all know how I end up and I ever get a front end going! Your certainly right about VB not being as simple as you think, I have to admit I thought this would be the easier bit once the database was done!

                • 5. Re: Oracle database front end
                  Ok mini update now that I'm home.. I've decided to go with the APEX route, and because I already had the Express Edition of Oracle installed I was able to find it within my desktop shortcuts without any trouble.

                  Once in there is where I'm a bit lost, I've created a workspace and I have played around with some of the tools within the actual application developer. However at this stage I am unable to access my tables that were created in SQL Developer, is there something I need to do within SQL Developer in order to make my tables show up? I assumed that because I was connecting to the localhost connection I used with SQL Developer, that I would be able to access the tables from APEX too.

                  I notice there's an APEX listener in SQL Developer under the View tab, is this something I need to look at?

                  EDIT: Wait a minute, am I even meant to use SQL Developer here? I've just found a link in the SQL Workshop part of APEX that allowed me to upload the script file I used to create the tables.. once I uploaded and ran it, the tables all appear as wanted... is this what I was supposed to do in the first place, or should it have worked anyway given that I'd already ran the scripts in SQL Developer? Either way I appear to be OK with this at the minute but I'd appreciate an answer anyway because it would be nice to understand it! :)

                  Edited by: 986500 on Feb 6, 2013 12:11 PM
                  • 6. Re: Oracle database front end
                    In Oracle, all objects are stored in schemas (which you can think of as the Oracle user that owns the object).

                    In APEX, a workspace has a default schema.

                    If the default schema of your APEX workspace is a different schema than the one you connected to in SQL Developer (and assuming that you didn't grant the APEX workspace's default schema privileges on the tables you created in SQL Developer), your APEX app won't be able to access them. You would either need to create the objects in the APEX workspace's default schema (which it sounds like you did) or you would need to modify the APEX workspace's default schema to be the schema that you used originally to create the objects.

                    Generally, when you are building a new application, you would create a new schema
                    CREATE USER football_mgmt
                    create a workspace in APEX that has FOOTBALL_MGMT as the default schema, and then create your objects in that schema either using SQL Developer or APEX (or SQL*Plus or whatever other tool you prefer). I find SQL Developer much easier to work with for creating tables, packages, etc. but that's a matter of personal preference.

                    You can have an APEX application in a workspace whose default schema is different than the one that actually owns the objects. But in order to do that, you would need the owner of the objects to grant the default schema user access to the objects and you would either need to use fully qualified names in your code
                    SELECT *
                      FROM some_other_user.some_table_name
                    or you would need to create synonyms (either local or global) for the objects that are owned by the other user.

                    1 person found this helpful
                    • 7. Re: Oracle database front end
                      Thanks for that Justin, yes that makes sense now!

                      I'm flying ahead with this now, I simply cannot believe I even considered Visual Studio when I see how much simpler this Application Express is to use!! One final question, when I have created all my forms and reports, how would a given 'user' be able to use my application? I.e. with Visual Studio when you are done, you create a final solution which is a runnable application, is there anything similar with this APEX that will achieve a similar outcome?

                      Thanks again everyone!

                      • 8. Re: Oracle database front end
                        APEX creates a web-based application. In theory, you could just give someone else the URL to the application running on your machine. In practice, though, you would probably want to deploy the application to a real server (running XE or some other edition of Oracle) and let the users access the application via a URL to that server.

                        Potentially, you could register for a workspace on http://apex.oracle.com as well which is a free hosted APEX environment.

                        • 9. Re: Oracle database front end
                          Brilliant, right well hopefully I should be OK with that now!... lets hope things are a bit smoother now anyway! Thanks for all your help everyone I'd still be messing around with VB if it wasn't for this forum!

                          Thank you!!
                          • 10. Re: Oracle database front end
                            I suggest that you keep development and production separate.

                            You can for example create your Apex dev app as application id 100. When a milestone is reached and the dev app is ready for production, export application 100 and import it as application 500 for example.

                            Application 500 is the production app and you provide its URL to users.

                            Interactive updates are also possible - while application 500 is in use, you can import a new dev version and overwrite it. As long as the changes in the new dev app are minor, the users on application 500 will not even know that you just ripped out the app and replaced it with an updated version.

                            Also a good idea to keep backup copies of these export scripts - as they serve as backups when you loose the entire database and need to recreate your Apex app from scratch. Also provides you with the ability to roll production back to a previous version in case of problems with new functionality deployed.
                            1 person found this helpful
                            • 11. Re: Oracle database front end
                              Thats great, thanks for your help with that! I'm amazed at how brilliant APEX is, I've created pages for everything now I've actually only got one problem left.. I'm obviously needing pages to insert data into each of my tables, e.g. for clubs, players, teams, etc.

                              I've followed the guide here http://docs.oracle.com/cd/E14373_01/appdev.32/e13363/frm_tabular.htm but when clicking the 'Add Row' in the tabular forms that I create, my primary key is setting as null (which essentially means I am, at this stage, unable to create any more records for any of my tables using this application). Is there a way of instructing my forms to generate a random number for my primary keys? I thought this would be automatic!
                              • 12. Re: Oracle database front end
                                Best to ask these questions in the {forum:id=137} forum.

                                I've last used Apex wizard generated data forms back in v2 I think. I prefer generating my own stuff instead - allows for better and finer control, and integrating additional features (like JQuery UI widgets).