7 Replies Latest reply on May 20, 2009 10:05 AM by VjFlex

    Create connection with Oracle DataBase

    VjFlex

      How do i create connection using flex with oracle database.

       

      Please mention all neccessary steps codewise as well as the configuration wise(for flex as well as Oracle9i)

       

      Thanks in advance.

        • 1. Re: Create connection with Oracle DataBase
          Richard_Abbott Level 3

          VjFlex,

          I always do this server side. In your Flex code do something like

                              service = new HTTPService();
                              current_date = new Date();
                              service.url = "my_oracle_call_page?call_name=get_data&additional_args=stuff&sep=" + current_date.valueOf();
                              service.addEventListener(FaultEvent.FAULT , fault_handler);
                              service.addEventListener(ResultEvent.RESULT, result_handler);
                              service.resultFormat = "text";
                              CursorManager.setBusyCursor();
                              service.requestTimeout = DATABASE_TIMEOUT;
                              service.send();

          where my_oracle_call_page is the name of a server-side page (eg talk_to_oracle.aspx or get_oracle_data.jsp), get_data is a string that identifies the specific database call you want (and stuff represents any additional parameters you need), and the date-separator string ensures that you don't just retrieve cached data but do a fresh call.

          In yuor server-side page (eg talk_to_oracle.aspx or get_oracle_data.jsp), you need to put the actual code to communicate with the database itself, using call_name and stuff where necessary. All server-side languages have ways to retrieve query-string information. However, the specifics of doing this are of course different depending what server language you are using - popular choices would be C#, VB, Java, PHP etc but that is up to you. If you are unfamiliar with writing that part of the code then a Flex list is not really the place to look for it - there is masses of information available on the web for this part. You'll need to make the connection, send the SQL request based on your supplied parameters, and then get the results back in some kind of tabular form (again, the classes and code for doing this vary with language).

          Back in Flex, your fault_handler code should just report back what went wrong (to the extent that you are able to find out - SQL is not always very good at telling you).

          Inside result_handler you would put the Flex code for parsing and using the database results. Supposing your server-side code returns results in XML, then doing this is very easy, but the specifics of this will vary according to what you have written in that page. To start with, I would just put Alert messages in both handlers so you can see what is going on, then move on to actually using the results.

          Hope that helps,

          Richard

          • 2. Re: Create connection with Oracle DataBase
            VjFlex Level 1

            Worked on what you told, made other file using php

            <?php
            $hostname = "jdbc:oracle:thin:@vidur:1521";
            $username= "scott";
            $password= "tiger";
            $dbid="DataHome";
            $link=mysql_connect($hostname, $username, $password);
            mysql_select_db($dbid) or die ("Unable to connect to Oracle");
            print "Connected to Oracle";
            mysql_close($link);
            ?>
            
            • 3. Re: Create connection with Oracle DataBase
              VjFlex Level 1

              But when i used i checked in alert of result Handler, it returned this code only....What i am missing here?

              • 4. Re: Create connection with Oracle DataBase
                Richard_Abbott Level 3

                Hi VjFlex,

                if you got the alert in the result handler rather than error handler, then your call worked fine. I'm not so familiar with PHP but it looked to me as though all you did was connect to the DB in question and then select a specific database schema. I don't think from the code you posted that you actually queried any table or returned a result. Now that you have shown you can connect to Oracle from PHP, the next stage is to query a table (syntax something like "select * from table_name" but you will know a query that is suitable for your system), then place the results into a format that can be passed back into the result handler. I would suggest you do this as XML since the Flex app can very readily make use of this in your visual components.

                The results will be available in the event.result variable of your handler, and will need casting to the correct format (eg my_xml = XML(event.result)). Your Flex parsing code must match however you have turned the SQL query into a return variable in PHP. That is a question of using PHP statements correctly, not Flex statements, and so this probably isn't the right list if you are uncertain about that. But in ASP.Net I would take the returned table object, iterate over records and turn each record into an XML element.

                Hope that helps,

                Richard

                • 5. Re: Create connection with Oracle DataBase
                  VjFlex Level 1

                  Check this as well..may be mistake lies in calling...

                   <mx:HTTPService
                          id="service"
                          url="DataConn.php?lal='sds'"
                          resultFormat="e4x"
                          result="resultHandler(event);"
                      />
                  

                  or in result handler

                       public function resultHandler(event:ResultEvent):void {     
                                 Alert.show(event.result.toString());  
                            
                            }
                  

                   

                  Suggest any change that has to be made....

                  And please pass along the asp.net code u have made for doing the same

                   

                   

                  Thanks

                  VjFlex

                  • 6. Re: Create connection with Oracle DataBase
                    Richard_Abbott Level 3

                    VjFlex,

                    looking at your code line

                    url="DataConn.php?lal='sds'"

                    I wouldn't have thought you need the ' marks around sds. Unless your php code is diong something special to handle them, you would normally just put lal=sds and all would be well.

                    You also say "please pass along the asp.net code u have made for doing the same". Of course I can't at this time pass on the actual code but the principles are as follows (I am using C# in ASP.NET as the server-side language in this case and SQL server as the database, and have not shown the connection string etc - you already know how to do that for your database)

                    sql_call = "select top 10 t1.a, t2.b, t2.c from t1, t2 where t1.id=t2.id order by a";
                    sql_server_command = new SqlCommand(sql_call, sql_server_connection);
                    sql_server_command.CommandType = CommandType.Text;
                    sql_server_data_adapter = new SqlDataAdapter(sql_server_command);
                    dataset = new DataSet();
                    sql_server_data_adapter.Fill(dataset);
                    datatable = dataset.Tables[0];
                    call_encoded = "<XML row_id='data' fields='a,b,c'>";
                    for (int i = 0; i < datatable.Rows.Count; i++)
                      {
                        data_record = "<data>" +
                          "<a>" + datatable.Rows[i][0].ToString() + "</a>" +
                          "<b>" + datatable.Rows[i][1].ToString() + "</b>" +
                          "<c>" + datatable.Rows[i][2].ToString() + "</c>" +
                          "</data>";
                        call_encoded += data_record;
                      } // for i
                    call_encoded += "</XML>";
                    return call_encoded;

                     

                    Hope that helps. You can see that the returned table is converted to XML so that once back in Flex it can be easily managed.

                    Richard