Sybase+Php in production

Dev Shed Forums Sponsor:

July 31st, 2002, 09:13 AM

sylow

Contributing User

Join Date: Feb 2001

Location: Alkmaar

Posts: 96

Time spent in forums: 4 h 10 m 41 sec
Reputation Power: 13

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

__________________
Sylow Web Development and Design
Web Developer

July 31st, 2002, 07:07 PM

rycamor

Gödelian monster

Join Date: Jul 1999

Location: Central Florida, USA

Posts: 2,306

Time spent in forums: 3 Days 6 h 42 m 51 sec
Reputation Power: 60

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

July 31st, 2002, 07:42 PM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 12

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?

__________________
Matt - matt@fanhome.com
FanHome.com - Where Sports Fans Connect (our SYBASE-backed vB Forums!)
Sybase DBA / PHP fanatic

Sybase v. MySQL v. Oracle | Why I don't like MySQL

| Download Sybase TODAY! | Visit DBForums.com for all your RDBMS talk!

August 1st, 2002, 04:29 AM

sylow

Contributing User

Join Date: Feb 2001

Location: Alkmaar

Posts: 96

Time spent in forums: 4 h 10 m 41 sec
Reputation Power: 13

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 04:45 AM.

August 1st, 2002, 11:23 AM

rycamor

Gödelian monster

Join Date: Jul 1999

Location: Central Florida, USA

Posts: 2,306

Time spent in forums: 3 Days 6 h 42 m 51 sec
Reputation Power: 60

Here are two methods of returning result sets from functions:

http://www.postgresql.org/idocs/ind...l.html#AEN26425

http://developer.postgresql.org/doc...ql-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.

August 1st, 2002, 11:32 AM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 12

FreeBSD(4.4?) under linux compat mode. I think they just set it up wrong, but it's not working well on SMP.

August 1st, 2002, 09:02 PM

ultraslacker

Contributing User

Join Date: May 2002

Posts: 56

Time spent in forums: < 1 sec
Reputation Power: 12

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

August 1st, 2002, 11:36 PM

rycamor

Gödelian monster

Join Date: Jul 1999

Location: Central Florida, USA

Posts: 2,306

Time spent in forums: 3 Days 6 h 42 m 51 sec
Reputation Power: 60

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)

August 2nd, 2002, 10:22 AM

ultraslacker

Contributing User

Join Date: May 2002

Posts: 56

Time spent in forums: < 1 sec
Reputation Power: 12

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!

#10

August 2nd, 2002, 01:37 PM

rycamor

Gödelian monster

Join Date: Jul 1999

Location: Central Florida, USA

Posts: 2,306

Time spent in forums: 3 Days 6 h 42 m 51 sec
Reputation Power: 60

Glad to be of help. That's a very interesting website, by the way.
\\me bookmarks

#11

August 2nd, 2002, 01:49 PM

rycamor

Gödelian monster

Join Date: Jul 1999

Location: Central Florida, USA

Posts: 2,306

Time spent in forums: 3 Days 6 h 42 m 51 sec
Reputation Power: 60

Quote:

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.

#12

August 2nd, 2002, 01:56 PM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 12

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).

#13

August 3rd, 2002, 09:18 AM

sylow

Contributing User

Join Date: Feb 2001

Location: Alkmaar

Posts: 96

Time spent in forums: 4 h 10 m 41 sec
Reputation Power: 13

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

#14

August 3rd, 2002, 10:06 AM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 12

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.

#15

August 3rd, 2002, 10:42 AM

MattR

Contributing User

Join Date: Jun 2001

Location: High above the mucky-muck (Columbus, OH)

Posts: 266

Time spent in forums: < 1 sec
Reputation Power: 12

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

php_sybase_ct.zip (28.0 KB, 362 views)

Sybase+Php in production

Developer Shed Advertisers and Affiliates