15 Replies Latest reply on Sep 23, 2010 7:59 AM by jfb00

    Flex to SQL server

    Enigma006

      Hai

       

      I am very new to Flex.

      I am trying to connect Flex to remote SQL server database.

       

      I have seen few blogs but getting confused.

      Can someone post an example to connect to remote SQL server database, get the data to xml and populate datagrid/list from it.

      I am using Flex 4.

       

      Thanks a lot..

        • 1. Re: Flex to SQL server
          Claudiu Ursica Level 4

          You want to connect directly ot the SQL? I know it is possible with Oracle, not sure about other vendors. Howeveer you normally Connect a middle tier app php/java/.net.. to the db and Flex talks to the middle tier not directly to the db.

           

          HTH,

          Claudiu

          • 2. Re: Flex to SQL server
            Enigma006 Level 1

            Ok. I know Flex cannot directly connect to database.

            How to do using PHP? can you post any example you have.

             

            Thanks

            • 3. Re: Flex to SQL server
              jfb00 Level 3

              Hi,

              The way that I connect to any database from flex is using coldfusion.

              http://learn.adobe.com/wiki/display/Flex/Flex+and+ColdFusion

               

              Rgds

               

              Johnny

              Please rate my answwer

              1 person found this helpful
              • 4. Re: Flex to SQL server
                flexillu10 Level 1

                i am also trying to do a similar thing but to mySql.

                 

                I'm using flash builder connect to data/service wizard but it asks me for a web root and root url of my server, when i type them in it says it can't find the web root. Does the server have to be local? becuase mine is not on my local pc

                • 5. Re: Flex to SQL server
                  Enigma006 Level 1

                  I have only flash builder. Do I need to download any other software.

                  Please specify in detail if possible.

                   

                  Thanks a lot.

                  • 6. Re: Flex to SQL server
                    Claudiu Ursica Level 4

                    http://corlan.org/2008/10/10/flex-and-php-remoting-with-amfphp/

                     

                    This explains it thorough.

                     

                    However it depends on what you want to achieve. The simplest way is to expose a php script (or more) from your server and from flex to use a HTTPService/URLLoader instance and ake GET/POST's to that script's url(s). You might need to encode data as XML though but is not difficult from php. Your script will process the call talk to the DB and respond according to your calls. Don;t have time to produce a code snippet right now but there should be hundreds of them on the web.

                     

                    C

                    • 7. Re: Flex to SQL server
                      Devtron Level 3

                      Are you using Web Services, by any chance?

                       

                      You can use HTTPService objects to access Web Services from a URL.

                       

                      This is how I interact with our SQL database.

                      • 8. Re: Flex to SQL server
                        Enigma006 Level 1

                        Hey Devtron,

                         

                        I am using Web services. but I dont know I can use HTTPService.

                        Can you specify something in detail please.

                         

                        Thanks a lot..

                        • 9. Re: Flex to SQL server
                          Devtron Level 3

                          Here is how I use HTTPService Objects. I am new to FLEX too, only 2 months in and I am launching a pretty huge app next week...

                           

                          Anyways, first you define your HTTPService Object(s). You can do this in ActionScript or MXML. Here I am defining them in MXML, in the Declaration section of my MXML component, like so:

                           

                          1. declare HTTPService Object

                          <fx:Declarations>

                                  <s:HTTPService id="setSomeDataSRV"/>
                                  <s:CallResponder id="setSomeDataSRVResult" result="setSomeDataSRVResult_resultHandler(event)"/>

                          </fx:Declarations>

                           

                           

                          ^ you can specify many parameters here, like URL, result, etc. These are rather empty and do not use many of the attributes available. I initialize those later, in AS3 code. You can see two objects, a HTTPService and a CallResponder. The CallResponder basically listens and receives focus after the HTTPService is invoked and run. The CallResponder has a result method, setSomeDataSRVResult_resultHandler(event). This is where the code jumps after the HTTPService is run. We will get to that in a minute.

                           

                          2. In ActionScript/CDATA script section in the same component, I have the following function:

                           

                                      private function executeSomeDataWebService():void {


                                          if (_someDataChanged) {

                           

                                              var params:Object = {};
                                              params["UserID"] = lblUserID.text;
                                              params["Parameter2"] = lblParameter2.text;
                                              params["Parameter3"] = someDataNumStepper.value.toString();
                                              params["Parameter4"] = _userTempType.data.tempValue;

                                             
                                              setSomeDataSRV.url = "http://255.255.255.255:8060/MyServiceLayer/MyDataService.asmx/MyWebServicesNameHere";
                                             
                                              setSomeDataSRVResult.token = setSomeDataSRV.send(params);

                           

                                          }
                                         
                                      }

                           

                           

                          255.255.255.255:8060 needs to be your actual Server URL. This is only example stuff.

                           

                           

                          3. here is the result handler, in Action Script 3. This gets called back when the HTTPService is completed:

                           

                                      private function setSomeDataSRVResult_resultHandler(event:ResultEvent):void {
                                          Alert.show('someData web service invokation was succesful');
                                      }

                           

                           

                           

                           

                           

                          I hope this can put you in the right direction. May the FLEX force be with you.

                          • 10. Re: Flex to SQL server
                            Devtron Level 3

                            oh, and I would invoke the web service by having a Submit button with a click event for "executeSomeDataWebService()".

                             

                            <mx:Button id="btnFireOffAWebService" label="Run Web Service" click="executeSomeDataWebService()" />

                            • 11. Re: Flex to SQL server
                              Enigma006 Level 1

                              Thanks Devtron..this is pretty helpful and seems to be working too..bcz I got the alert message saying successful

                              How do I use populate any datagrid or list using that data?

                              • 12. Re: Flex to SQL server
                                Devtron Level 3

                                1. First of all, you must define a "bindable" ArrayCollection. (Atleast this is the approach I take). Declare this.

                                 


                                [Bindable]
                                public var myDataCollection:ArrayCollection; //this is used to bind data to your datagrid

                                 


                                2. Make sure your dataGrid's dataprovider is set to your ArrayCollection object.

                                 


                                <mx:DataGrid id="myDG" dataprovider="{myDataCollection}" />

                                 


                                3. To get data into your ArrayCollection, you must loop through the result object in your result event handler (which we created in Step #3 above).

                                 


                                something like this:

                                 


                                private function setSomeDataSRVResult_resultHandler(event:ResultEvent):void {
                                                Alert.show('someData web service invokation was succesful');

                                 

                                                  for( var i:int=0; i< setSomeDataSRV.lastResult.YOURXMLParentNode.childnode.childnode.length; i++) {

                                 

                                                            var myDataObject:Object = setSomeDataSRV.lastResult.YOURXMLParentNode.childnode.childnode.getItemAt(i);

                                 

                                                            myDataCollection.addItem(myDataObject);

                                 

                                                  }
                                            }

                                 

                                 

                                 

                                 

                                This should put you in the right direction, I hope. This is not 100% accurate, you may need some adjustment as I just typed this off the top of my head. I apologize if there is bad logic here, but this is pretty much how I approach it.

                                • 13. Re: Flex to SQL server
                                  jfb00 Level 3

                                  You don't have to loop to the array collection.

                                   

                                  [Bindable] public var myDataCollection:ArrayCollection = new ArrayCollection;

                                   

                                  private function setSomeDataSRVResult_resultHandler(event:ResultEvent):void {

                                       myDataCollection = ArrayCollection(event.result);

                                  }

                                   

                                  I hope this help

                                   

                                  Johnny

                                  • 14. Re: Flex to SQL server
                                    Devtron Level 3

                                    ^ Thanks, I knew there was an easier way for that. I just havent had the need to use it that way.

                                     

                                    In my situation, I must loop through my result, to apply business logic/calculations on many fields. It's a little nasty but allows me to handle data that my DBA wont process for me

                                     

                                    Nice post!

                                    • 15. Re: Flex to SQL server
                                      jfb00 Level 3

                                      i believe the insert data should be in this way for better performance. After that you can loop thru the array collection and do any calculations.

                                      You also can use Filters.

                                      http://www.flex-blog.com/arraycollection-filter-example/

                                       

                                       

                                      Johnny