If I understand your question correctly, you are asking whether to use foam insulation at the roof/attic area or in the walls. You are also concerned about water problems.
Based on your explanation it appears your concern regarding water has to do with high humidity in your area, since obviously the insulation can't stop water intrusion.
The foam insulation is a very good form of thermal barrier, much better than common insulations including batt insulation. It not only provides higher R-value for a given depth, but is also more consistent in filling cavities. By its characteristics it provides a seal against moisture and air penetration and migration through the wall cavity.
If you are asking whether to use foam insulation in the wall or the attic space, I would recommend both. However, it is easier to apply to the wall cavities than the attic space. It should be noted that foam insulation, especially the closed cell (higher R-value, more expensive), should be considered as an investment with a higher initial cost.
In order to protect your attic space from water problems, besides obviously ensuring the roof has no leaks and possible point of water entry, the best line of defense is adequate attic ventilation as well as the proper amount of insulation and installation of an air and moisture barrier system at the lid. I should note that any penetration in the ceiling/lid, such as light fixtures, etc., should be properly sealed, preferably using foam insulation.
For more information:
You should read architect Chris Benedict's Q&A "Which foam insulation is best for the south coast Atlantic region: closed cell or open cell?"