[Haskell-cafe] re: Oracle stored procedures
Hi, Peter I did and uploaded some examples regarding Oracle and Takusen and the intended approach you would employ. Example 01: Create table and some tuples. http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29883#a29883 Example 02: Create a package with procedures to mantain the table. http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29884#a29884 Example 03: Optional. See how it works in Oracle sqlplus. http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29885#a29885 Example 04: The Takusen-Haskell program. It follows the scheme of example #03. http://hpaste.org/fastcgi/hpaste.fcgi/view?id=29894#a29894 Some comments on the topic: This approach has limitations. Takusen (if not use in conjunction with Template Haskell) will be bound up to iteratees of eight values. I (and I'm pretty sure some other people) have successfully used Template Haskell to overcome this limitation. (I've been working in removing some boilerplate). The all stored procedure approach is not a limitation as you can see from example #04, unless these procedures return values these types: Record Of , complex objects (PL/SQL tables, objects, etc), Boolean. Oracle has several programatic interfaces, some that I remember: JDBC, ODBC, OCI, OCCI and those for Windows .NET. Takusen is a wrapper for OCI (Oracle Call Interface) which seems to me pretty low level. Yet, I don't know if you can specify just the name of the procedure. Please note that if there is a procedure named close_accounts, by writing "begin close_accounts; end;" you are not wrapping it in a SQL statement. It actually is an anonymous PL/SQL block and it is needed in that form to be processed by the server. Oracle offers for Windows .NET: ODT (Oracle Developer Tools for Visual Studio), ODP (Oracle Data Provider) and ODE (Oracle Database Extensions). I did test (and taste) a bit of .NET tools with F#. It was nice. Maybe you wish to try hs-dotnet with these. A final remark, I have used Takusen both on Windows and Linux. The given example was developed and tested actually on Windows. Regards, Leonel Fonseca. ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
[Haskell-cafe] re: Oracle stored procedures
Hi Peter,
Yes, from Takusen you can call Oracle stored procedures, functions,
packaged stored procedures or functions, or execute an arbitrary
pl/sql block.
In the Takusen software release there is a directory called
"Database\Oracle\Test". There, Enumerator.lhs, among other code has
these helpers you may want to use:
>wrapPLSQLFunc funcname parms =
> let sqltext = "begin " ++ (head args) ++ " := " ++ funcname
> ++ "(" ++ placeholders ++ "); end;"
> placeholders = concat (intersperse "," (tail args))
> args = take (length parms) (map (\n -> ":x" ++ show n) [1..])
> in cmdbind sqltext parms
>wrapPLSQLProc procname parms =
> let sqltext = "begin " ++ procname
> ++ "(" ++ placeholders ++ "); end;"
> placeholders = concat (intersperse "," args)
> args = take (length parms) (map (\n -> ":x" ++ show n) [1..])
> in cmdbind sqltext parms
Please, be aware of the following points:
1) If the pl/sql code doesn't need parameters and has no results, you
can use "execDDL". (execDML returns a counter of affected rows).
2) If the procedure/function receives parameter, you'll need to use
"cmdbind" (or similar to "cmdbind") to pass the parameters.
3) If the pl/sql code returns values, you have this options:
3.a) The returned value is a reference (cursor): Takusen supports
this very fine. Use "doQuery" or similar.
3.b) The return value is an scalar value: You can collect the
result with an iteratee, even if it is a single value.
3.c) The return value is a complex oracle object: As of Takusen
0.8.5 there is no support for table of records of ...
3.d) The return value is Boolean. You'll get an error.
Little examples:
For case #1:
> -- Example 1.a: We set nls_language to american english.
> set_NlsLang_Eng :: DBM mark Session ()
> set_NlsLang_Eng = execDDL $ sql
> "alter session set nls_language='AMERICAN'"
> -- Example #1.b: Now we set session language parameter to spanish.
> set_NlsLang_Esp :: DBM mark Session ()
> set_NlsLang_Esp = execDDL $ sql
> "alter session set nls_language='LATIN AMERICAN SPANISH'"
For case #2:
> -- Example 2.a: We use database string "concat" function
>concat' :: String -> String -> DBM mark Session String
>concat' a b = do
> let ite :: Monad m => String -> IterAct m String
> ite v _ = return $ Left v
> sqlcmd = wrapPLSQLFunc "concat"
> [bindP $ Out (""::String), bindP a, bindP b]
> doQuery sqlcmd ite undefined
>
> -- later on the program, you'd have...
> some_string <- concat' "a" "b"
For case #3:
> -- Case 3.b: We collect a single scalar value.
> qNlsLang :: DBM mark Session [String]
> qNlsLang = doQuery s ite []
> where
> s = "select value from nls_session_parameters \
> \ where parameter = 'NLS_LANGUAGE'"
> ite :: (Monad m) => String -> IterAct m [String]
> ite a acc = result' ( a:acc )
> mostrar_NlsLang :: DBM mark Session ()
> mostrar_NlsLang = qNlsLang >>= liftIO . print . head
> -- Another example for Case 3.b
> -- This time we don't use a list to accumulate results.
> s1 = sql "select systimestamp from dual"
>
> sysTSasCTQ :: DBM mark Session CalendarTime
> sysTSasCTQ= do
>
>let ite :: (Monad m) => CalendarTime -> IterAct m CalendarTime
>ite x _ = result' x
>
>t <- liftIO ( getClockTime >>= toCalendarTime)
>doQuery s1 ite t
--
Leonel Fonseca.
___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
[Haskell-cafe] help me evangelize haskell
Michael: Hi. I take the risk to mention some facts you might already be working on. I speculate on three perceptions in the mind of decision makers related to the adoption of a new language in a shop. This perceptions can be modified in stages. First Stage: Awareness. Why programming in that language matters? Once you can justified this, advance to the following stage. Haskell is nice, quick to develop, brings tools to make abstractions at any level and has tools and libraries. And community support is really awesome! If you invest more in learning, you can strive to use it as formal programming method and earn safety guarantees. Please, see elsewere for success stories that help you to motivate and encourage management to test the new alternative. Second Stage: Small term engagment. Why your shop should do little investment in trying a new language in a small but representative project? Here you can make two arguments to gain support in this stage: 2.A) The shortcommings of the current tools/languages. 2.B) Small concept programs of how your current language/tool would fail where the new one would success. You can gain credibility by showing how your new tool could be short on some aspect that it's well mastered in the old one. (i.e. be impartial). Someone has already pointed how fragile interpreted programs are. In this stage you must gain both decision makers and peers recognition of the problem the shop is facing with the current language versus the improvements of using the new language. A project must be acomplished and reviewed, so a decision can be made over the facts of costs and benefits. Third Stage: Adoption. Is it really better to adopt a new language? At this point you have evidence of possible benefits and costs. And a decision is made. Among other costs, please take in account that embracing any new language in a shop will require formalised (funded, time alloted to, machines, any other resource) learning and coaching activities. Haskell has long learning curve. And you must consider this cost of being less productive than in your current language. There's too the risk of hitting walls in a tight-schedule situation. You can lower this risk of failure if you arrange to have a mentor (master) who can lead, provide hints to co-workers, discuss approaches and solve hard issues whenever they appear. Counter-arguments that you must deal with: c1) Why not... Erlang? F#? See Jon Harrop's claims. Scala or Clojure? c2) Performance predictability. c3) On the fly script generation and execution. c4) Will your co-workers be happy of embracing a new paradigm of solving problems? Will type signatures appear as pain or a valuable thing to them? -- Leonel Fonseca. ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Merge hsql and HDBC -- there can only be one!
On the subject of bridging relational persistence and fucntional programming I wish to communicate on what I'm working on (Tránsito Abierto Library) (or Open Transit): 1) Whole objective: To disminish to gap between functional and relational paradigms. 2) Specific objetive: Illustrate and explore the gap. 3) Narrow focus: I'm using Takusen and Oracle and Haskell (á la GHC). And Template Haskell (TH). 4) Hypothesis: There'll be always a gap because you're using distinct nature beasts, but you a bit more of convinience would yield great improvements. 4.1) Another thought on this is: A mapping always needs to be specified between data shapes (types/representations). 5) Findings/Achievements(?): 5.1) Generation of Haskell types and iterator code given the shape of relational table. This is not very useful itself. Because of (4.1), using SQL is an effective way to: (1) convey such mapping and (2) specify queries that involve more than one table. 5.2) Given a sql query text, use the facilities on Oracle database to describe the query. With such description, use Template Haskell to generate Haskell types and iterator code. Let us call Geco to this generated code that is built by means of an AST. 5.3) As 5.2 (spliced Geco) but saving the Geco AST. What for? It turns out that later, during program execution you can regenerate a fresh AST code and compare it to Geco AST. If there's a difference it means your database environment is changed and it is likely that your code is no longer safe. The idea here, your program reacts to the enviroment detecting if its unsafe to run. 5.4) Takusen covers many database datatypes but not all. 5.5) I was about to automate pl/sql bindings using a inspired Takusen wrapper found on the enumerator tests directory . It follows scheme 5.2. But it is uncomplete. 5.6) Takusen is limited to iterators up to 8 fields. By using TH, easily you can have any number of columns in your query. 5.7) I wrote a serialiser library on the DB side and a deserialiser library on Haskell side. The idea it's to push *any* pl/sql result onto a string and recover it on the Haskell side. Needs schemes 5.2 and 5.5 to work. Status: Not working. 6) I believe intelligent schema generation is possible following the scheme above. What you would get is a whole module that would enforce constraints (cardinality, restrictions, etc). 7) I haven't try the way Haskell -> DB. Neither the storing of functional values as it was done before in Persistent Haskell and others. Cheers, -- Leonel Fonseca. ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
[Haskell-cafe] Fun Facts: A lazy bill
I share this just for the sake of fun. In Costa Rica (Central America) a new series of bills are to be emitted and to celebrate bio-diversity some animal drawings are going to be used. Look at the new 10.000 colones bill: http://www.bccr.fi.cr/WebPages/PaginaInicio/NuevaFamilia/10.html Choose "reverso" in order to see the other side. -- Leonel Fonseca. ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
Re: [Haskell-cafe] Would it be evil to add "deriving Typeable" to newtype Q?
Hey, the hint provided by Ben worked like a charm.
I've also tried Ivan suggestions both on my windows and linux installations.
DrIFT-cabalized couldn't install at all at windows since I don't use MinGW.
So, I ghc'ed --make DrIFT.
Both, windows and linux, refused to complete work with this error:
drift: can't find module Control/Monad
Thank you.
> {-# LANGUAGE DeriveDataTypeable, StandaloneDeriving #-}
>
> import Data.Typeable
> import Language.Haskell.TH
>
> deriving instance Typeable1 Q
--
Leonel Fonseca.
___
Haskell-Cafe mailing list
Haskell-Cafe@haskell.org
http://www.haskell.org/mailman/listinfo/haskell-cafe
[Haskell-cafe] Would it be evil to add "deriving Typeable" to newtype Q?
Hi everybody, Is it reasonable to add "deriving Typeable" to newtype Q? In case you wonder why I want to do that, it is because I've constructed a Q [Dec] inside a monad, I want to extract it from the monad (via runIO) and the monad has constraint "Typeable" over this parameter. I've also tried to write the Typeable instance in my own module (not Language.Haskell.TH.Syntax). But I've got no luck since the Q type constructor is exported but the data constructor is not. Thanks. -- Leonel Fonseca. ___ Haskell-Cafe mailing list Haskell-Cafe@haskell.org http://www.haskell.org/mailman/listinfo/haskell-cafe
