#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Location
    Alkmaar
    Posts
    96
    Rep Power
    14

    Sybase+Php in production


    Hello all,
    I really miss stored procs in PostgreSQL so I am considering to move to Sybase.

    Anybody here using Sybase ASE with PHP under heavy load?
    I would like to hear some experiences in production environment.

    Thanks in advance
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Just in case you missed my reply in this other thread, please take a look at the PostgreSQL procedural languages documentation, where you can see that stored procedures are definitely included in PostgreSQL.

    Not that I'm knocking Sybase at all, but your reason for moving to it is not the case.

    I think the reason many people get the impression that Postgres lacks SP is because at the PostgreSQL site they don't use the phrase "stored procedures" very often, preferring to call them functions. But it is the same thing. In fact, once you get the feel for it, PostgreSQL's choices in languages for SP opens up many interesting possibilities. One of the coolest is the ability to create a procedure in Perl, and (if running in untrusted mode), your procedure can even interact with the full range of Perl functionality, including any OS system calls, emails, you-name-it.

    Also PostgreSQL has another interesting feature called a RULE. It functions similarly to a trigger+SP, but can be tied into multiple tables, and allows you to "rewrite" the query on the back end, based on whatever conditions you set up. One example is to make a view updateable by creating a rule which specifices ON UPDATE DO INSTEAD, and then updates the corresponding tables in the view.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    We use ASE 12.5 with PHP, both on Linux in production. Distributed.net runs ASE 11.0.0.3 on BSD (ugh). What do you need to know?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Location
    Alkmaar
    Posts
    96
    Rep Power
    14
    Thanks for quick posts.

    My problem with functions in postgre is I need to return record sets( I will check when I am home today if it is possible to return record set to PHP from a function )

    About Sybase:
    I read your post in sitepoint forums, you already answered my questions there by saying scalability, and stability.

    + 11.0.0.3 is free on Linux which will let me to start without paying. Then I can see with my own eyes how in action Sybase works for me.

    Thank you both for your posts.

    Regards
    Last edited by sylow; August 1st, 2002 at 05:45 AM.
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Here are two methods of returning result sets from functions:

    http://www.postgresql.org/idocs/inde....html#AEN26425

    http://developer.postgresql.org/docs...l-cursors.html (this is probably the better method)

    As far as PHP is concerned, the results of a PostgreSQL function are treated the same as results from a standard query: rowsets.

    MattR -- when you say "BSD(ugh)", are you referring to BSDi, the commercial OS, or FreeBSD (or Net/OpenBSD)? And why is it 'ugh'? Just curious, because I might have reason to want Sybase on a FreeBSD(yeah!) system, which I did not think was possible at the moment. Except with Linux compat mode, of course.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    FreeBSD(4.4?) under linux compat mode. I think they just set it up wrong, but it's not working well on SMP.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    13
    Rycamor -

    I'm having trouble using functions to return open cursors. It is quite new and has been undocumented for some time.

    Could you post example code (php or java) using open cursors?

    Thanks!
    Casuistry Agnostic
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Here is a simple example. It is a little puzzling at first to handle cursors with PHP because PHP doesn't explicitly give us cursor functions, but as long as you do things in the right order, it should be fine. (I have a feeling this can cause problems with persistent connections, though -- see note at the bottom.)

    I started by converting the example at the bottom of the second link I posted above:

    I have a table that logs events for a CRM-type system, thus it records multiple events per prospect, with the fields 'eventdate', and 'eventtype'. I wanted a function that would return all the events for a particular prospect.

    Here is the function:
    Code:
    ;
    CREATE FUNCTION events4prospect(int4) returns refcursor AS '
    DECLARE
       results refcursor;
    
    BEGIN
                OPEN results FOR SELECT eventdate,eventtype FROM prospect_events
                 WHERE prospect_id = $1;
                RETURN results;
        
    END;
    ' LANGUAGE 'plpgsql';
    Now, here is how we would get the results on the command line. Notice it returns a handle called <unnamed cursor 1>. Inelegant, but works for now .
    Code:
    crm_db=# BEGIN;
    BEGIN
    
    crm_db=# SELECT sp_events4prospect(10309);
     sp_events4prospect 
    --------------------
     <unnamed cursor 1>
    (1 row)
    
    crm_db=# FETCH ALL IN "<unnamed cursor 1>";
           eventdate       |     eventtype      
    ------------------------+---------------------
     2002-03-27 09:39:16+01 | begin
     2002-03-27 13:50:10+01 | first viewing
     2002-03-27 13:50:18+01 | contact established
     2002-03-27 13:50:45+01 | confirmed
     2002-03-28 09:10:32+01 | edit_info
     2002-05-03 11:33:45+02 | closing
     2002-06-07 12:46:20+02 | closing
     2002-06-07 12:57:04+02 | edit_info
     2002-06-07 12:57:54+02 | edit_info
    (9 rows)
    crm_db=# COMMIT; 
    COMMIT
    To do the same in PHP, you would just use whatever abstraction layer you want to do exactly the same things we did above:
    Code:
    1. begin a transaction
    2. execute the cursor function:
      $db->executeQuery("SELECT sp_events4prospect($int_id)")
      or pg_query($dbresource,"SELECT sp_events4prospect($int_id)"); 
      (don't need to get result resource here)
    3. execute the query which fetches the cursor:
      $db->executeQuery("FETCH ALL IN \"<unnamed cursor 1>\";")
      or $result = pg_query($dbresource, "FETCH ALL IN \"<unnamed cursor 1>\";")
    4. Loop through the result set for the last query:
      while($row = $db->fetchArray()){ etc... }
      or while($row = pg_fetch_array($result)){ etc... }
    5. Commit the transaction;
    This is a simple, stupid example that could use a good deal more thought. The "<unnamed cursor 1>" will become"<unnamed cursor 2>" if you execute the function a second time within a certain postgreSQL connection session. This is easy enough to keep track of within a certain script, but if you have persistent connections, I believe there will be problems. In the end you should take a more advanced approach which explicitly names the cursor. I leave this as an exercise for the reader.

    The power of cursors does have some interesting implications for web application development, if you think about it. Especially if you start playing around with "bound cursors", etc... which can cache and considerably speed up queries which are repeated often.

    (Sorry to hijack this thread. If there are any more questions about cursors with PostgreSQL, maybe we should start another thread)
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    13

    Talking


    Superb, Rycamor! I have the cursor behaving in postgres and am seeing about using 'em in the upgrade to our old site - moving from mysql to postgres plus a complete redesign and retooling.

    Thanks again!


    Casuistry Agnostic
  18. #10
  19. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Glad to be of help. That's a very interesting website, by the way.
    \\me bookmarks
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  20. #11
  21. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Originally posted by MattR
    FreeBSD(4.4?) under linux compat mode. I think they just set it up wrong, but it's not working well on SMP.
    Hmm... Are you in touch with the distributed.net developers? I would be curious to hear more details on these problems. I have with an associate who is a FreeBSD guru. We work with FreeBSD SMP systems all the time and they just sing along. Of course maybe there is some kind of incompatibility with the way Sybase for Linux expects SMP to be handled.

    I have read some notes which suggest that certain DBMS's, like DB2 for Linux, actually run a little faster on FreeBSD with Linux compat.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    Yes, and they're not quite sure what is the problem. The OS itself runs fine with SMP, but ASE does odd things (or rather PHP connecting to ASE) and we're not sure if it is a lib problem or some obscure bug in the linux compat layer which we're not aware of (Sybase does things a little differently to facilitate ease of running on different systems which may not be used in many native apps).
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Location
    Alkmaar
    Posts
    96
    Rep Power
    14
    Hello MattR,
    I have a question about Sybase+php.
    I installed Sybase 11.0.3.3 on Redhat machine and achieved to make it work with PHP.
    What I want is to connect it from apache+php which is on my windows machine which is on the same local network.

    Can you forward me to any documents about it?

    Thanks
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    I've used ODBC before, you can also use ADOdb:
    http://php.weblogs.com/ADODB#downloads

    I don't recall where to get the Sybase or MS SQL PHP .DLLs for Windows -- that was a major struggle when I tried to use MS SQL around 2 or 3 years ago with PHP 3 since no one provided it.

    http://php.weblogs.com/easywindows
    That says it has Sybase ability.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    Here's the Sybase CT lib. I had to rename it to be .zip, so make sure you change it back to .dll when you download it.
    Attached Files

IMN logo majestic logo threadwatch logo seochat tools logo