1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
|
<html>
<head>
<title>firstworks SQL Relay FAQ</title>
<link href="css/styles.css" rel="stylesheet">
</head>
<body>
<span class="heading1">SQL Relay FAQ</span><br><br>
<span class="heading2">General Questions</span><br><br>
<ol>
<li><a href="#whatis">What is SQL Relay?</a></li>
<li><a href="#doforme">What can SQL Relay do for me?</a></li>
<li><a href="#platforms">What platforms does SQL Relay run on?</a></li>
<li><a href="#howwork">How does SQL Relay work?</a></li>
<li><a href="#efficiency">How can SQL Relay improve the efficiency of my
website?</a></li>
<li><a href="#oracle">Why is SQL Relay especially good for migrating my
database-driven web-based application to Oracle?</a></li>
<li><a href="#freebsd">Why is SQL Relay especially good for Open/Net/FreeBSD
and PowerPC Linux?</a></li>
<li><a href="#pooling">Can I use SQL Relay for database connection
pooling?</a></li>
<li><a href="#overloaded">Can SQL Relay keep my database from getting
overloaded?</a></li>
<li><a href="#replicated">How can SQL Relay be used with replicated or
clustered databases?</a></li>
<li><a href="#firewall">Can I use SQL Relay to firewall ad-hoc
queries?</a></li>
<li><a href="#databaseusers">Can SQL Relay proxy multiple database users
instead of using the same user for every session?</a></li>
</ol>
<span class="heading2">API Comparison Questions</span><br><br>
<ol>
<li><a href="#cgi">Why should I use SQL Relay with CGI's instead of using a native database
API?</a></li>
<li><a href="#odbc">How does SQL Relay compare to ODBC, JDBC, Perl::DBI, PHP::ADODB, Ruby::DBI or PythonDB?</a></li>
<li><a href="#dbiproxy">How does SQL Relay compare to DBI::Proxy?</a></li>
<li><a href="#apachedbi">How does SQL Relay compare to Apache::DBI or PHP's internal
persistent database connections?</a></li>
<li><a href="#zope">Why should I use SQL Relay with Zope?</a></li>
</ol>
<span class="heading2">Database-Specific Questions</span><br><br>
<ol>
<li><a href="#canxxxdb">Can I use SQL Relay to connect to XXX
database?</a></li>
<li><a href="#whyxxxdb">Why should I use SQL Relay with XXX database?</a></li>
<li><a href="#features">Does SQL Relay support XXX's specific
features?</a></li>
<li><a href="#sqlserver">Why can't I get SQL Relay to connect to
Sybase/Microsoft SQL Server?</a></li>
<li><a href="#freetds">I know that Sybase and MS SQL Server support affected row counts, so why does the FreeTDS connection return -1's for affected rows?</a></li>
<li><a href="#money">Why are money types crashing my FreeTDS connections?</a>
<li><a href="#sybasefreetdsdiff">What's the difference between the Sybase and FreeTDS connections?</a></li>
<li><a href="#postgresql">Postgresql's native API uses numbers for types but
SQL Relay mangles them. How do I get the numbers?</a>
<li><a href="#oracleconnections">What is the difference between the Oracle7 connection and the Oracle8 connection?</a></li>
<li><a href="#nlslang">I'm using a non-ascii character set in my Oracle database, why am I get get garbage back when I do a select?</a></li>
</ol>
<span class="heading2">Programming Questions</span><br><br>
<ol>
<li><a href="#php">My PHP application uses up all of the SQL Relay connections,
and I keep having to restart SQL Relay. What gives?</a></li>
<li><a href="#buffer">Why does SQL Relay buffer the entire result set?</a></li>
<li><a href="#nobuffer">How do keep SQL Relay from buffering the entire result
set?</a></li>
<li><a href="#bindvars">How do I use bind variables?</a></li>
<li><a href="#vectorbinds">Why can't I do vector binds?</a></li>
<li><a href="#storedprocs">How do I run stored procedures?</a></li>
<li><a href="#returning">How do I get data out of DML with RETURNING
clauses?</a></li>
<li><a href="#temptables">Do temporary tables work with SQL Relay?</a></li>
<li><a href="#threadsafe">Is the SQL Relay API thread-safe?</a></li>
<li><a href="#clientcache">How does client-side result-set caching
work?</a></li>
<li><a href="#servercache">What about server-side result-set caching?</a></li>
</ol>
<span class="heading2">Administration Questions</span><br><br>
<ol>
<li><a href="#tweaks">What system parameters can I tweak to get better performance out of SQL Relay</a></li>
<li><a href="#firewallhang">If I leave SQL Relay idle for a long time, then come back and try to use it, I get a low level TCP error from the database. What does that mean?</a></li>
<li><a href="#ttl">Why doesn't the last connection die off when I set connections=0 and maxconnections to some larger number and let SQL Relay go idle for a long time?</a></li>
</ol>
<span class="heading2">General Questions</span><br><br>
<a name="whatis"></a>
<span class="heading3">What is SQL Relay?</span><br><br>
<p>
SQL Relay is a persistent database connection pooling, proxying and load
balancing system for Unix and Linux supporting ODBC, Oracle, MySQL, mSQL,
PostgreSQL, Sybase, MS SQL Server, IBM DB2, Interbase, SQLite and
MS Access (minimally) with APIs for C, C++, Perl, Perl-DBD, Python,
Python-DB, Zope, PHP, Ruby, Ruby-DBD, Java and TCL, drop-in replacement
libraries for MySQL and PostgreSQL clients, command line clients, a GUI
configuration tool and extensive documentation. The APIs support advanced
database operations such as bind variables, multi-row fetches, client-side
result set caching and suspended transactions. It is ideal for speeding up
database-driven web-based applications, accessing databases from unsupported
platforms, migrating between databases, distributing access to replicated
databases and throttling database access.
</p>
<a name="doforme"></a>
<span class="heading3">What can SQL Relay do for me?</span>
<p>SQL Relay can improve the efficiency of database-driven web-based
applications, provide databases access from unsupported platforms, ease
migration between database vendors, distribute access to replicated databases
and facilitate throttled database access.</p>
<p>SQL Relay is not exactly a "drop-in" solution though. It requires
configuration and possibly programming to be useful.</p>
<a name="platforms"></a>
<span class="heading3">What platforms does SQL Relay run on?</span>
<p>Unix variants. It is known to run on Linux (x86 and PPC), SCO Open Server,
Solaris, FreeBSD, OpenBSD and NetBSD. The client API's compile and work on
Win32 platforms using Cygwin or UWIN. I only have access to Linux, SCO,
Solaris, FreeBSD, OpenBSD, NetBSD and Win32 systems, so from time to time a new
release breaks compatibility with other platforms. If there's a platform you'd
like to see supported and if you can grant me access to a machine running that
platform, send mail to
<a href="mailto:david.muse@firstworks.com">david.muse@firstworks.com</a>.</p>
<a name="howwork"></a>
<span class="heading3">How does SQL Relay work?</span>
<p>SQL Relay's connection daemons log into and maintain sessions with
databases. These connection daemons advertise themselves with a listener
daemon which listens on an inet and/or unix port for client connections.
When a client connects to the listener, if a connection daemon is available,
the listener hands off the client to that connection. If no connection daemon
is available, the client must wait in queue until one is. Once a client
is handed off to a connection daemon, the client communicates to the database
through the session maintained by that daemon.</p>
<a name="efficiency"></a>
<span class="heading3">How can SQL Relay improve the efficiency of my
website?</span>
<p>Here are some examples of how SQL Relay can improve the efficiency
of your web site.</p>
<p>Let's say you're running CGI's againt a transactional database such as
PostgreSQL, MS SQL Server or Oracle. CGI's have to log into and out of the
database each time they run. If you use SQL Relay to maintain
persistent connections to the database and just log into and out of
SQL Relay, you can reduce the amount of time wasted establishing
database connections and handle more CGI requests per-second. This is both
because the time-cost of connecting to SQL Relay is smaller than
the time-cost of connecting to a transactional database, and because the
SQL Relay client library is smaller than most database client
libraries, resulting in a more lightweight CGI.</p>
<p>Let's say you're using Apache, PHP and Oracle and you determine by doing
all sorts of analysis that you need to keep 30 Apache processes running to
provide adequate response. Since most of your site isn't database-driven,
on average, no more than 5 PHP's actually access the database simultaneously.
Currently, you're using persistent connections to defeat the time-cost of
logging into Oracle, but you have to maintain 30 connections (1 per web server
process) which takes up a lot of memory on both the web server and database
server and you really only need 5 connections. By using SQL Relay you can
reduce the number of Oracle connections to the 5 that you need, continue to
run 30 Apache processes and reclaim the wasted memory on both machines.</p>
<p>Many websites run a combination of PHP's and Perl modules. Perl modules can
use Apache::DBI and PHP's have a persistent database connection system, but a
PHP cannot use an Apache::DBI connection and a Perl module cannot use a PHP
persistent connection. Thus in order to make sure that there are enough
database connections for each platform, many more web-server processes
have to be run, perhaps twice as many. If the PHP's and Perl modules used
SQL Relay instead, they could share databse connections and reduce the number
of web-server processes and database connections.</p>
<p>SQL Relay makes it easy to distribute load over replicated servers. A
common scaling solution when using MySQL or PostgreSQL in a read-only web
environment is to run several web servers with a dedicated database server for
each web server or group of web servers and update all the databases
simultaneously at scheduled intervals. This usually works pretty well, but
sometimes database or web servers get runs of heavy load while others
are idle. In other cases, an uneven number of machines is required. For
example, your application may need 3 web servers but only 2 database servers
or vice-versa. People usually just by 3 of each, wasting money. Moreover, in
most cases, the servers have to be equivalently powerful machines. You can't
usually just add another cheap machine that you have lying around into the
pool. SQL Relay can connect to multiple, replicated or clustered database
servers, providing web-based applications access to whichever server isn't
busy. SQL Relay can also be configured to maintain more connections to more
powerful machines and fewer connections to less powerful machines, enabling
unevenly matched machines to be used in the same database pool. Collectively,
these features allow you to save money by using only the exact number of
servers that you need and by enabling you to use spare hardware in your
database pools.</p>
<a name="oracle"></a>
<span class="heading3">Why is SQL Relay especially good for migrating my
database-driven web-based application to Oracle?</span>
<p>Connecting to Oracle databases is especially time-costly and OCI libraries
are especially heavyweight compared to other databases. Moving that overhead
out of your application is especially advantageous with Oracle.</p>
<a name="freebsd"></a>
<span class="heading3">Why is SQL Relay especially good for Open/Net/FreeBSD and
non-x86 Linux?</span>
<p>Open/Net/FreeBSD and non-x86 Linux are good platforms but lack API
support from prominent commercial database vendors. SQL Relay provides a
connection solution for these platforms and others.</p>
<a name="pooling"></a>
<span class="heading3">Can I use SQL Relay for database connection
pooling?</span>
<p>Yes.</p>
<p>SQL Relay maintains persistent connections to databases
which can be shared among clients over lightweight TCP connections using
inet or unix sockets. This means SQL Relay can even be used for
database connection pooling across multiple machines.</p>
<a name="overloaded"></a>
<span class="heading3">Can SQL Relay keep my database from getting
overloaded?</span>
<p>Yes.</p>
<p>A common problem with high-traffic, database-driven websites is that in
order to handle the number of incoming requests, large numbers of web server
processes or threads must run. Using conventional connection pooling
mechanisms, at least one persistent database connection would be maintained
per-process. Sometimes, under heavy load, the database server just can't
handle the traffic from that many simultaneous client connections.</p>
<p>Clustering is one solution, but clustering is expensive and not available
for all databases.</p>
<p>By placing SQL Relay between your web servers and database, you can
maintain a smaller number of persistent connections to your database and
funnel all database requests through those connections. When the number
of database session requests exceeds the number of persistent connections, the
session requests are queued. This ultimately causes delayed response to the
client, but keeps the database running smoothly. In most cases, the delay is
negligable and the tradeoff is acceptable.</p>
<a name="replicated"></a>
<span class="heading3">How can SQL Relay be used with replicated or clustered
databases?</span>
<p>If you have replicated or clustered databases, SQL Relay can be
configured to maintain connections to some or all of the database servers and
distribute sessions over them. SQL Relay can even be configured to
maintain more connections to more powerful machines and fewer connections
to less powerful machines, enabling a heterogeneous mixture of machines to
be used in the database server pool.</p>
<p>Note that SQL Relay cannot be used to replicate databases or
keep replicated databases synchronized. If you are using SQL Relay to
access replicated databases then it is assumed that there is some means by
which the databases are kept synchronized external to SQL Relay.</p>
<a name="firewall"></a>
<span class="heading3">Can I use SQL Relay to firewall ad-hoc queries?</span>
<p>No.</p>
<p>This feature is on the TODO list though.</p>
<a name="databaseusers"></a>
<span class="heading3">Can SQL Relay proxy multiple database users instead of
using the same user for every session?</span>
<p>Yes. Set the authtier attribute of the instance tag in the sqlrelay.conf
file to "database". See <a href="configuring.html">Configuring SQL Relay</a>
for more information.</p>
<p>SQL Relay does it very efficiently when used with Oracle8i/9i/10g and MySQL
version 3.23.3 or greater. For Oracle 8i/9i/10g, the database must be
configured properly. See <a href="oraclentier.html">this document</a> for
step-by-step instructions.</p>
<p>Oracle 8i/9i/10g and MySQL version 3.23.3 or greater allow a process that is
connected to the database to switch users without disconnecting from the
database. When used with other databases, SQL Relay logs out and logs back in
to the database whenever it needs to switch users.</p>
<span class="heading3">API Comparison Questions</span><br><br>
<a name="cgi"></a>
<span class="heading3">Why should I use SQL Relay with CGI's instead of using a
native database API?</span>
<p>CGI's have to log into and out of the database each time they run. This
can take a long time. Native database API libraries are often very large.
Since SQL Relay maintains persistent database connections, is fast to
connect to and has a lightweight client API, using SQL Relay with CGI's
usually results in a faster applications that use less memory.</p>
<a name="odbc"></a>
<span class="heading3">How does SQL Relay compare to ODBC, JDBC, Perl::DBI,
PHP::ADODB, Ruby::DBI or PythonDB?</span>
<p>This is sort of an apples-to-oranges comparison. These API's are
primarily targeted as abstraction layers and make no attempt to improve
application performance. They are in many ways more full featured than the
SQL Relay client API's. SQL Relay currently supports Perl::DBI, Ruby::DBI and
PythonDB on the API side and ODBC on the database connection side. An ODBC API
for SQL Relay is on the TODO list.</p>
<a name="dbiproxy"></a>
<span class="heading3">How does SQL Relay compare to DBI::Proxy?</span>
<p>DBI::Proxy is Perl-specific or at least very challenging to use from other
languages. SQL Relay is likely to outperform DBI::Proxy since DBI::Proxy is
primarily targeted at providing access to databases from unsupported
platforms, not at improving application performance. SQL Relay can provide
access to databases from unsupported platforms as well, even platforms for
which there is no unix support using the ODBC connection and an ODBC to ODBC
bridge.</p>
<a name="apachedbi"></a>
<span class="heading3">How does SQL Relay compare to Apache::DBI or PHP's persistent database connections?</span>
<p>SQL Relay is more lightweight and potentially faster than
Apache::DBI and is competitive in speed with PHP's persistent connections.
SQL Relay can be used to provide a connection pool to multiple machines
and has more backend features than Apache::DBI or PHP. However, the
DBD and PHP API's are more full featured than the SQL Relay API's and
are generally considered to be simpler to implement.</p>
<p>When using Apache::DBI or PHP's persistent connections, a connection is
opened to the database for every web server process. Frequently, web sites
need to run large numbers of processes to provide adequate response. As the
number of database connections grows, resources get strained and a lot of
database connections go unused most of the time.</p>
<p>If a website runs a mixture of Perl modules and PHP scripts, the issue can
be doubled.</p>
<p>SQL Relay makes more efficient use of resources by maintaining fewer
persistent connections to the database and funnelling all database requests
through those connections. When the number of database session requests
exceeds the number of persistent connections, the session requests are queued.
This ultimately causes delayed response to the client, but keeps the database
running smoothly. In most cases, the delay is negligable and the tradeoff is
acceptable.</p>
<a name="zope"></a>
<span class="heading3">Why should I use SQL Relay with Zope?</span>
<p>The same efficiency arguments that can be made against
Apache::DBI and PHP's persistent connections cannot be made against Zope.
Zope maintains a hackable (some say "configurable") number of persistent
database connections in it's cache and shares them among it's threads. The
number of database connections and threads are independent. There is always
the possibility that one or all of the database connections will get pushed
out of the cache and have to be started back up later, but in practice, this
is highly unlikely and happens very infrequently.</p>
<p>If you have such a large farm of Zope machines that the number of persistent
database connections is straining the database server's resources, SQL Relay
can provide a middle tier to reduce the number of persistent connections.</p>
<p>SQL Relay adds immediate support for load distribution over a group
of clustered or replicated databases to Zope.</p>
<p>SQL Relay can provide a means for connecting to databases for which there is no Zope adapter.</p>
<p>When using the ZOracleDA, Zope generally needs to be restarted if the
database is bounced. When using SQL Relay, the database can be bounced
without having to restart Zope. This behavior may be specific to the
ZOracleDA though, and it may just be a bug. SQL Relay also supports
Oracle LOB and long datatypes. ZOracleDA uses OCI7 calls instead of OCI8
calls and does not support those datatypes.</p>
<span class="heading3">Database-Specific Questions</span><br><br>
<a name="canxxxdb"></a>
<span class="heading3">Can I use SQL Relay to connect to XXX database?</span>
<p>SQL Relay can connect to Oracle, MySQL, mSQL, PostgreSQL, Sybase,
DB2, Interbase and SQLite databases using connection daemons compiled against
their native API's. Additionally, SQL Relay can connect to Microsoft SQL Server
or Sybase using a connection compiled against FreeTDS and MS Access Databases
using a connection compiled against MDBTools. Using the ODBC connection,
compiled against iODBC or unixODBC, SQL Relay can connect to any database with
an ODBC driver for unix or, using the ODBC to ODBC bridge, can connect to any
database with an ODBC driver for any platform.</p>
<a name="whyxxxdb"></a>
<span class="heading3">Why should I use SQL Relay with XXX database?</span>
<br><br>
<table border="1">
<tr>
<td><b>Database</b></td>
<td><b>Limit the number of open connections.</b></td>
<td><b>Distribute over replicated or clustered databases.</b></td>
<td><b>Overcome the connection delay.</b></td>
<td><b>Provide remote access.</b></td>
</tr>
<tr>
<td><b>Oracle</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>MySQL</b></td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>mSQL</b></td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>PostgreSQL</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>Sybase</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>DB2</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>Interbase</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>SQLite</b></td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
</tr>
<tr>
<td><b>FreeTDS</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>ODBC</b></td>
<td>Yes</td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
</tr>
<tr>
<td><b>MDB Tools</b></td>
<td>Yes</td>
<td>Yes</td>
<td>No</td>
<td>Yes</td>
</tr>
</table>
<br><br>
<a name="features"></a>
<span class="heading3">Does SQL Relay support XXX's specific features?</span>
<br><br>
<table border="1">
<tr>
<td><b>Database</b></td>
<td><b>Queries</b></td>
<td><b>Bind Variables</b></td>
<td><b>Procedural Language</b></td>
<td><b>Auto-Commit</b></td>
</tr>
<tr>
<td><b>Oracle</b></td>
<td>Yes</td>
<td>Scalar Input/Output</td>
<td>Yes</td>
<td>Yes</td>
</tr>
<tr>
<td><b>MySQL</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>mSQL</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>PostgreSQL</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>Sybase</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>DB2</b></td>
<td>Yes</td>
<td>Scalar Input/Output</td>
<td>Unknown</td>
<td>Yes</td>
</tr>
<tr>
<td><b>Interbase</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>Unknown</td>
<td>Yes</td>
</tr>
<tr>
<td><b>SQLite</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>FreeTDS</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No</td>
<td>No</td>
</tr>
<tr>
<td><b>ODBC</b></td>
<td>Yes</td>
<td>Scalar Input/Output if DB supports it.</td>
<td>Yes if DB supports it.</td>
<td>Yes if DB supports it.</td>
</tr>
<tr>
<td><b>MDB Tools</b></td>
<td>Yes</td>
<td>Scalar Input</td>
<td>No.</td>
<td>No.</td>
</tr>
</table>
<br><br>
<a name="sqlserver"></a>
<span class="heading3">Why can't I get SQL Relay to connect to Sybase/Microsoft
SQL Server?</span>
<p>The server parameter in the string attribute of the connection tag does not
refer to the DNS name of the server. Rather it refers to an entry in the
"interfaces" file. The Sybase and FreeTDS libraries look for that file in
default places, but if the file is installed somewhere else and the library
can't find it, it will not be able to figure out what host/port the server
is running on. One way to tell SQL Relay where the file is located is to
set the SYBASE environment variable to the directory containing the file before
starting SQL Relay. Starting with version 0.32, the string attribute of the
connection tag takes a sybase parameter which sets the environment variable.</p>
<p>Another problem that people have connecting to Sybase/Microsoft SQL Server
is related to database selection. Until version 0.32, the Sybase and FreeTDS
connection daemons ignored the "db" connectstring parameter. This was an
oversight. The connection daemons would connect to the correct server but
would not use the correct database. Instead, the connection daemons would
be connected to the master database on that server. This is fixed in 0.32.
To work around this problem in an older release it is necessary to fully
qualify table names if the table is not in the master database. A fully
qualified table name is dbname.username.tablename.</p>
<a name="freetds"></a>
<span class="heading3">I know that Sybase and MS SQL Server support affected row counts, so why does the FreeTDS connection return -1's for affected rows?</span>
<p>Before version 0.53, calling the FreeTDS function to get the number of
affected rows would cause a segmentation fault. As of version 0.32, SQL Relay
figures out what version of FreeTDS is installed at compile time and only
enables affected rows if the FreeTDS version is greater than 0.52. If you
compile against an earlier version of FreeTDS, a -1 is returned for affected
rows as if the database didn't support the feature.</p>
<a name="money"></a>
<span class="heading3">Why are money types crashing my FreeTDS connections?</span>
<p>Before version 0.53, calling the FreeTDS function ct_fetch when a result
set had a MONEY or SMALLMONEY column in it would cause a segmentation fault.
As of version 0.32, SQL Relay figures out what version of FreeTDS is installed
at compile time and only enables queries selecting MONEY or SMALLMONEY columns
if the FreeTDS version is greater than 0.52. If you compile against an earlier
version of FreeTDS, any attempt to run a query selecting MONEY or SMALLMONEY
column will fail with an error indicating that you should recompile SQL Relay
against a newer version of FreeTDS.</p>
<a name="sybasefreetdsdiff"></a>
<span class="heading3">What's the difference between the Sybase and FreeTDS
connections?</span>
<p>The sqlr-connection-sybase program is compiled against Sybase ctlib; the
libraries that come with Sybase Adaptive Server Enterprise. They use a
protocol called TDS (Tabular Data Stream) to talk to the database.</p>
<p>The sqlr-connection-freetds program is compiled against FreeTDS, an
open-source implementation of the TDS protocol and ctlib.</p>
<p>Older versions of Microsoft SQL Server are compatible with Sybase ctlib, but
newer versions are not. FreeTDS is compatible with all versions of Sybase
Adaptive Server Enterprise and Microsoft SQL Server.</p>
<p>Unfortunately, FreeTDS is an incomplete implementation of TDS. Several
features are buggy, inconsistent or non-existent. For example...</p>
<ul>
<li>Programs compiled against FreeTDS<0.53 crash when fetching MONEY and
SMALLMONEY datatypes. SQL Relay handles this by preventing queries which
fetch MONEY or SMALLMONEY datatypes from running if it's compiled against
FreeTDS<0.53.</li>
<li>Programs compiled against FreeTDS<0.53 crash when attempting to find out
how many rows were affected by an insert/update/delete query. SQL Relay handles
this by not requesting the affected row count and just returning -1 if it's
compiled against FreeTDS<0.53.</li>
<li>Bind variables are unsupported. The ct_param and ct_setparam functions
simply don't exist in FreeTDS ctlib. SQL Relay handles this by faking input
bind variables in the same manner that the MySQL, mSQL, SQLite and
MDB Tools connections do.</li>
<li>FLOAT, NUMERIC and DECIMAL datatypes are all represented as FLOAT
datatypes. There doesn't appear to be any workaround for this.</li>
<li>Columns of type CHAR have trailing spaces truncated. For a CHAR(20) column,
inserting a value
<pre>'hello'</pre>
will be returned as
<pre>'hello'</pre>
instead of
<pre>'hello '</pre>
To add to the problem CHAR and VARCHAR datatypes are both represented as CHAR
in FreeTDS and Sybase ctlib, so there's no good way to know whether to append
trailing spaces or not.</li>
<li>Under Sybase ctlib, MONEY datatypes have 2 decmial places by default but
can have up to 4. Under FreeTDS, they have 4 decimal places.</li>
<li>Date formats are slightly different. FreeTDS left-pads single digit days
and hours with a single 0. For example, Sybase ctlib returns 1:00 AM on
01/01/2001 as
<pre>Jan 1 2001 1:00AM'</pre>
while FreeTDS returns it as
<pre>Jan 01 2001 01:00AM'</pre></li>
<li>Sybase ctlib returns REAL and FLOAT datatypes with arbitrary decimal
places. Inserting 1.1 as a REAL or FLOAT type then selecting it might return
1.09888874310 or something similar. With FreeTDS, inserting 1.1 then selecting
it returns 1.1. Sybase behaves like FreeTDS for DECIMAL and NUMBER types.</li>
<li>FreeTDS only supports one cursor per connection. You can write code that
uses multiple cursors, but it will eventually throw errors like: "Error:
Attempt to initiate a new SQL Server operation with results pending." Prior
to SQL Relay 0.37, this was a common error to see under certain circumstances.
As of SQL Relay 0.37 the connection daemon funnels everything through a single
cursor and this error should no longer appear. From the client-side as long as
you don't use setResultSetBufferSize() to specify a buffer size other than 0,
it will appear like more than 1 cursor is available, but in fact only 1 is
being used.</ul>
<p>There are possibly other inconsistencies, but these are the only ones that
I've run into so far.</p>
<p>FreeTDS is great software despite it's inconsistencies. Sybase ctlib is
impossibly complex. Anyone attempting to re-engineer it is braver than I am.
I'm impressed that FreeTDS works as well as it does, and it's getting better
with each release.</p>
<a name="postgresql"></a>
<span class="heading3">Postgresql's native API uses numbers for types but
SQL Relay mangles them. How do I get the numbers?</span>
<p>Prior to version 0.28, SQL Relay mangled Postgresql numeric types into
pseudo-standard datatype names. I read a thread in a discussion group
indicating that someone was specifically unhappy with SQL Relay because of this
behavior though, and decided to change it. So, as of version 0.28, by default,
SQL Relay returns numeric types when run against Postgresql. If you prefer
getting type names, you can set the mangletypes connect string value to "yes"
in your sqlrelay.conf file.</p>
<p>For example, in version 0.28 or higher the following connectstring will
instruct SQL Relay to return type names instead of numbers:</p>
<blockquote>
user=myuser;password=mypass;db=testdb;mangletypes=yes
</blockquote>
<p>In version 0.28 or higher the following connectstring will instruct SQL
Relay to return type numbers:</p>
<blockquote>
user=myuser;password=mypass;db=testdb;mangletypes=no
</blockquote>
<p>Leaving the mangletypes parameter out altogether is the same as setting it
to "no".</p>
<a name="oracleconnections"></a>
<span class="heading3">What is the difference between the Oracle7 connection and the Oracle8 connection?</span>
<p>Between versions 7 and 8, Oracle radically changed OCI; their client API.
The Oracle7 connection uses OCI version 7 and the Oracle8 connection uses
OCI version 8.<p>
<p>The Oracle8 connection supports LOB datatypes and true bind-by-position.</p>
<P>For example, in the query:</p>
<blockquote>
<PRE>
<B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> mytable <FONT color=#6959cf>where</FONT> col1=:val1 <B><FONT color=#a62828>and</FONT></B> col2=:val2
</PRE>
</blockquote>
<P>You can bind variables to "val1" and "val2" or "1" and "2". Using the
Oracle7 connection, you can only bind to "val1" and "val2". If you want to
bind to "1" and "2", your query would have to look like this:</p>
<blockquote>
<PRE>
<B><FONT color=#a62828>select</FONT></B> * <FONT color=#6959cf>from</FONT> mytable <FONT color=#6959cf>where</FONT> col1=:<FONT color=#ff00ff>1</FONT> <B><FONT color=#a62828>and</FONT></B> col2=:<FONT color=#ff00ff>2</FONT>
</PRE>
</blockquote>
<p>Oracle 8,8i,9i and 10g support OCI 7 and OCI 8. If you have an Oracle 8,8i,
9i or 10g database, you can connect to it using the Oracle7 connection provided
that you are not using Oracle MTS (Multi-Threaded Server). Oracle 8, 8i, 9i and
10g support LOB datatypes though, which are not supported by Oracle 7. The
Oracle7 connection will retun empty strings and UNKNOWN datatypes for LOB
fields.</p>
<p>Oracle is attempting to phase out OCI 7, but has mostly maintained
compatibility. The libraries and headers that come with Oracle 8,8i,9i and 10g
contain OCI 7 and OCI 8 functions. If you compile SQL Relay against Oracle 8,
8i,9i or 10g both connections will be built. If you compile SQL Relay against
Oracle 7, only the Oracle7 connection will be built.<p>
<p>Since Oracle is phasing out OCI 7, you may run into some inconsistencies.
For example, the libraries that ship with Oracle 9.0.1 for Linux don't support
OCI 7 functions for dealing with LONG datatypes. If you compile SQL Relay
against Oracle 9.0.1, the Oracle7 connection will return empty strings for LONG
fields and 0's for their lengths. It seems like I had that same problem with
Oracle 8.1.6 on Linux, but 8.1.7 works fine (and 8.1.6 is no longer available
to verify the problem). So, expect inconsistencies with the Oracle7 connection
if it's not compiled against Oracle 7.</p>
<a name="nlslang"></a>
<span class="heading3">I'm using a non-ascii character set in my Oracle database, why am I get get garbage back when I do a select?</span>
<p>When the Oracle connection daemon fetches data from the database, the
Oracle client library converts the data from whatever character set the
database is using into whatever character set is specified by the NLS_LANG
environment variable.</p>
<p>If NLS_LANG isn't set, is set to a character set that doesn't contain some
of the characters that are stored in the database, or is set to a character set
that is in some other way incompatible with the character set of the database,
some of the data will be converted into garbage.</p>
<p><b>Note:</b> NLS_LANG must be set properly on the machine running the SQL
Relay <i><b>servers</b></i>, as that is the machine executing code from the
Oracle client library. It's not uncommon to forget to set NLS_LANG on that
machine and then have things break suddenly when switching from using the
Oracle client library directly to using SQL Relay.</p>
<p><b>Note:</b> When SQL Relay starts at boot time, the SQL Relay init script
has no particular environment. So, if NLS_LANG is set in /etc/profile or
/etc/bashrc or some other shell's init script, it may not be picked up by the
SQL Relay init script. In version 0.37, an nls_lang parameter was added to the
Oracle connection string parameters to address this issue. When running
versions older than 0.37, you'll need to edit the init script and add a line to
set the NLS_LANG environment variable.</p>
<span class="heading3">Programming Questions</span><br><br>
<a name="php"></a>
<span class="heading3">My PHP application uses up all of the SQL Relay
connections, and I keep having to restart SQL Relay. What gives?</span>
<p>Prior to version 0.36, PHP scripts needed to set up a shutdown handler to
clean up connections and cursors and call register_shutdown_function() or
call ignore_user_abort(). Otherwise, if someone hit stop in their browser, the
page would stop executing immediately and wouldn't close it's connections to the
SQL Relay server. In version 0.36, cleanup code was added to the PHP module,
making it unnecessary to set up a shutdown handler.</p>
<p>The cleanup code works with PHP 4.1 or greater. If you're using PHP 4.0,
then the old issue still exists. Below is a description of the issue and
how to deal with it.</p>
<p>PHP pages that use SQL Relay are usually set up like this:</p>
<blockquote>
<pre>
<font color="#6b59ce"><?</font>
<font color="#008a8c">dl</font><font color="#6b59ce">(</font>"<font color="#ff00ff">sql_relay.so</font>"<font color="#6b59ce">)</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#a52829"><b>=</b></font>sqlrcon_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>...</b></font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#a52829"><b>=</b></font>sqlrcur_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>...</b></font> <font color="#a52829"><b>do</b></font> some stuff <font color="#a52829"><b>...</b></font>
sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>
sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>
<font color="#6b59ce">?></font>
</pre>
</blockquote>
<p>If someone clicks stop in their browser before sqlrcur_free is called,
execution of the page stops there and sqlrcur_free never gets called. As a
result, the socket connection between the web server and SQL Relay is never
severed and the SQL Relay connection remains occupied even though the PHP page
that opened the connection isn't running anymore.</p>
<p>The best solution to this problem is to create a shutdown function and
register it with a call to register_shutdown_function().</p>
<p>For example:</p>
<blockquote>
<pre>
<font color="#6b59ce"><?</font>
<font color="#008a8c">dl</font><font color="#6b59ce">(</font>"<font color="#ff00ff">sql_relay.so</font>"<font color="#6b59ce">)</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">con</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font>;
<font color="#a520f7">function</font> shutdown<font color="#6b59ce">()</font> <font color="#6b59ce">{</font>
sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>;
sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#6b59ce">}</font>
<font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#a52829"><b>=</b></font>sqlrcon_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>...</b></font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#a52829"><b>=</b></font>sqlrcur_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#008a8c">register_shutdown_function</font><font color="#6b59ce">(</font>shutdown<font color="#6b59ce">)</font>;
<font color="#a52829"><b>...</b></font> <font color="#a52829"><b>do</b></font> some stuff <font color="#a52829"><b>...</b></font>
<font color="#6b59ce">?></font>
</pre>
</blockquote>
<p>Note that in this example, sqlrcur_free() and sqlrcon_free() aren't called
at the end of the page. This is because the shutdown function gets called
whether the page was aborted or completed successfully.</p>
<p>If you want to create a function who's contents are executed only on abort,
you can use the connection_aborted() function. In the following code, the
shutdown() function free's the cursor and connection if the script was aborted
but lets the script free them on it's own if it completed successfully. This
code is redundant but it illustrates the functionality of connection_aborted().
</p>
<blockquote>
<pre>
<font color="#6b59ce"><?</font>
<font color="#008a8c">dl</font><font color="#6b59ce">(</font>"<font color="#ff00ff">sql_relay.so</font>"<font color="#6b59ce">)</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">con</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font>;
<font color="#a520f7">function</font> shutdown<font color="#6b59ce">()</font> <font color="#6b59ce">{</font>
<font color="#a52829"><b>if</b></font> <font color="#6b59ce">(</font><font color="#008a8c">connection_aborted</font><font color="#6b59ce">())</font> <font color="#6b59ce">{</font>
sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>;
sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#6b59ce">}</font>
<font color="#6b59ce">}</font>
<font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#a52829"><b>=</b></font>sqlrcon_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>...</b></font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#a52829"><b>=</b></font>sqlrcur_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#008a8c">register_shutdown_function</font><font color="#6b59ce">(</font>shutdown<font color="#6b59ce">)</font>;
<font color="#a52829"><b>...</b></font> <font color="#a52829"><b>do</b></font> some stuff <font color="#a52829"><b>...</b></font>
sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>;
sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#6b59ce">?></font>
</pre>
</blockquote>
<p>Another option is to call ignore_user_abort(TRUE) early on in the script. If
ignore_user_abort() is used, then if a user clicks stop, the PHP script is not
aborted. Rather, the remainder of its output is just never sent to the browser.
This solution is less efficient than using register_shutdown_function() but also
works. For example:<p>
<blockquote>
<pre>
<font color="#6b59ce"><?</font>
<font color="#008a8c">dl</font><font color="#6b59ce">(</font>"<font color="#ff00ff">sql_relay.so</font>"<font color="#6b59ce">)</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">con</font>;
<font color="#a52829"><b>var</b></font> <font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font>;
<font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#a52829"><b>=</b></font>sqlrcon_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>...</b></font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#a52829"><b>=</b></font>sqlrcur_alloc<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#008a8c">ignore_user_abort</font><font color="#6b59ce">(</font><font color="#ff00ff">TRUE</font><font color="#6b59ce">)</font>;
<font color="#a52829"><b>...</b></font> <font color="#a52829"><b>do</b></font> some stuff <font color="#a52829"><b>...</b></font>
sqlrcur_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">cur</font><font color="#6b59ce">)</font>;
sqlrcon_free<font color="#6b59ce">(</font><font color="#a52829"><b>$</b></font><font color="#008a8c">con</font><font color="#6b59ce">)</font>;
<font color="#6b59ce">?></font>
</pre>
</blockquote>
<p>SQL Relay version 0.37 features an idle client timeout. If a client remains
connected to SQL Relay for a configurable amount of time but does nothing, then
SQL Relay will close the connection to that client. This should make the
results less catastrophic when people forget to call
register_shutdown_function() or ignore_user_abort() but is still less
efficient.</p>
<a name="buffer"></a>
<span class="heading3">Why does SQL Relay buffer the entire result set?</span>
<p>SQL Relay is targeted for web-based applications. For the most part,
queries with relatively small result sets are used to build web pages. For
small result sets, it more efficient to buffer the entire result set than to
step through it, building the page. It's usually faster because it reduces
network round-trips and allows one program to drop the connection to SQL
Relay, freeing it up for more programs to use while the first program builds
its page.</p>
<a name="nobuffer"></a>
<span class="heading3">How do keep SQL Relay from buffering the entire result
set?</span>
<p>For large result sets it can be impractical to buffer the entire result set.
Use the setResultSetBufferSize() method in the C++, Perl, Python, Ruby and Java
API's or the sqlrcur_setResultSetBufferSize() function in the C or PHP API's to
specify how many rows of the result set to buffer at once.</p>
<a name="bindvars"></a>
<span class="heading3">How do I use bind variables?</span>
<p>That depends on the database you're using. Oracle supports named bind
variables while other databases only support binds by position. Below are
pseudocode examples of both.</p>
<p>Oracle example:</p>
<pre>
sqlrconnection *con=new sqlrconnection(...);
sqlrcursor *cur=new sqlrcursor(cur);
cur->prepareQuery("select * from table where charcol=:charval and intcol=:intval and floatcol=:floatval");
cur->inputBind("charval","hello");
cur->inputBind("intval",10);
cur->inputBind("floatval",5.5,1,1);
cur->executeQuery();
delete cur;
delete con;
</pre>
<p>Sybase/MS SQL Server example:</p>
<pre>
sqlrconnection *con=new sqlrconnection(...);
sqlrcursor *cur=new sqlrcursor(cur);
cur->prepareQuery("select * from table where charcol=@charval and intcol=@intval and floatcol=@floatval");
cur->inputBind("charval","hello");
cur->inputBind("intval",10);
cur->inputBind("floatval",5.5,1,1);
cur->executeQuery();
delete cur;
delete con;
</pre>
<p>Other DB example:</p>
<pre>
sqlrconnection *con=new sqlrconnection(...);
sqlrcursor *cur=new sqlrcursor(cur);
cur->prepareQuery("select * from table where charcol=? and intcol=? and floatcol=?");
cur->inputBind("0","hello");
cur->inputBind("1",10);
cur->inputBind("2",5.5,1,1);
cur->executeQuery();
delete cur;
delete con;
</pre>
<p>Output bind variables work similarly but are only supported on Oracle.</p>
<pre>
sqlrconnection *con=new sqlrconnection(...);
sqlrcursor *cur=new sqlrcursor(con);
cur->prepareQuery("insert into table values ('hello') returning :charval");
cur->defineOutputBind("charval","hello",10);
cur->executeQuery();
cout << "charval is: " << cur->getOutputBind("charval") << endl;
delete con;
delete cur;
</pre>
<a name="vectorbinds"></a>
<span class="heading3">Why can't I do vector binds?</span>
<p>Vector binds just aren't implemented yet.</p>
<a name="storedprocs"></a>
<span class="heading3">How do I run stored procedures?</span>
<p>Stored procedures work in most databases that support them, with some
caveats. Stored procedures are not supported when using FreeTDS against
Sybase/MS SQL Server. Binding clob/blob values are only supported in
Oracle 8i or higher. When using stored procedures with Sybase, output
parameters must be varchar types.</p>
<p>The syntax for creating and executing a stored procedure is different for
each database. Each of the "Programming with SQL Relay using the XXX API"
documents explain how to use stored procedures in detail.</p>
<a name="returning"></a>
<span class="heading3">How do I get data out of DML with RETURNING
clauses?</span>
<p>DML with RETURNING clauses are only known to work with Oracle. You can use
output bind variables to get data out of DML with RETURNING clauses if the
values returned are scalar. Vector values are not supported. For example:</p>
<pre>
insert into testtable values ("one",2) returning :first, :second
</pre>
<a name="temptables"></a>
<span class="heading3">Do temporary tables work with SQL Relay?</span>
<p>Yes. Support for PostgreSQL temporary tables was added in version 0.34 and
support for all other databases was added in 0.35.</p>
<p>The reason temporary tables are an issue is because normally temporary tables
are either dropped or truncated when an application closes it's connection to
the database or commits/rolls-back a transaction. Transaction-scope tables
work naturally with SQL Relay. But, since SQL Relay never logs out of the
database, the database doesn't know to drop or truncate tables when an
application closes it's connection to SQL Relay. To remedy this, SQL Relay
parses each query according to the rules for creating a temporary table for
the database that it is connected to and keeps a list of temporary tables.
Then, when the application closes it's connection, SQL Relay drops or
truncates the tables as appropriate.</p>
<p>In Oracle, temporary tables are never dropped when the session or
transaction ends, only truncated. Transaction-scope tables are handled
naturally by the database, but session-scope tables must be handled by
SQL Relay. In other databases, a create table query would have to have been
issued but not with Oracle. SQL Relay only knows to truncate the table if a
create table query was issued. So, when using session-scope tables in Oracle,
you should issue a create table query, even if you know the table already
exists. The query will fail, but SQL Relay will know to truncate the table
when the session ends.</p>
<a name="threadsafe"></a>
<span class="heading3">Is the SQL Relay API thread-safe?</span>
<p>Yes. However, you cannot share an instance of an sqlrconnection or
sqlrcursor between threads without protecting the calls with mutexes.</p>
<a name="clientcache"></a>
<span class="heading3">How does client-side result-set caching work?</span>
<p>When the cacheToFile() and setCacheTtl() methods are called prior to running
a query, the client caches the result set from the query in a file on the
local file system and attaches a time-to-live tag to the file. The full
pathname of this file can be retrieved using the getCacheFileName() method.</p>
<p>The file sits in the cache directory (usually
/usr/local/firstworks/var/sqlrelay/cache) until it is removed by the
sqlr-cachemanager program. sqlr-cachemanager scans the files in the cache
directory every so often and removes the ones who's time to live has expired.
</p>
<p>Until the file is removed, other applications can open the file by name
using the openCachedResultSet() methods. At this point, the API acts as if it
had run a query that generated that result set.</p>
<a name="servercache"></a>
<span class="heading3">What about server-side result-set caching?</span>
<p>Server-side result set caching has not been implemented. This feature is
on the TODO list though.</p>
<a name="tweaks"></a>
<span class="heading3">What system parameters can I tweak to get better performance out of SQL Relay?</span><br><br>
<p>See <a href="tuning.html">Tuning SQL Relay...</a></p>
<a name="firewallhang"></a>
<span class="heading3">If I leave SQL Relay idle for a long time, then come back and try to use it, my program gets hung or I get a low level TCP error from the database. What does that mean?</span><br><br>
<p>If you are running SQL Relay on one machine and the database on a seperate
machine, and there is a firewall between the two machines, and no queries have
been run in a while, the firewall may "close" the connection between SQL Relay
and the database by discarding any packets that it receives from either.
The firewall does not send "close packets" to either SQL Relay or the database,
so SQL Relay thinks it's still connected to the database, but when it sends a
query, the firewall discards it.</p>
<p>Some database client API's catch this condition and return an obscure
error but many do not, they just hang silently forever.</p>
<p>Cisco PIX firewalls are known to cause this problem. Others may as well.</p>
<p>Allegedly, an IOS upgrade will solve the problem for Cisco PIX firewalls,
but I don't know what version you have to upgrade to.</p>
<p>Another solution is to turn off the time out.</p>
<p>Yet another solution is to set up a cron job that runs a query periodically
and keeps the connection from timing out.</p>
<p>You could also just set up the timeout to be so long that it's really
unlikely that it will ever occur. The PIX firewall's default is 1 hour. You
can change it from the PIX's command line using a command such as the
following:</p>
<blockquote><b>
timeout conn 04:00:00
</b></blockquote>
<p>You can also set it using the graphical web interface as follows:</p>
<p><img src="WPM_4877_1.PNG"></p>
<a name="ttl"></a>
<span class="heading3">Why doesn't the last connection die off when I set connections=0 and maxconnections to some larger number and let SQL Relay go idle for a long time?</span><br><br>
<p>When SQL Relay first starts up, since no connections are started, no connections are registered with the listener. Clients connect, connections fire up to handle them, clients disconnect, the connections time out and die. However, one of the connections will still remain registered with the listener as an "available connection". This connection cannot die off.</p>
<p>It's really, really complicated why. I tried lots of approaches and couldn't find one that didn't have a fundamental problem.</p>
<p>Basically, here's the code:</p>
<blockquote>
<pre>
listener:
acquireExclusiveAccessToSharedMemoryAmongListenerProcesses();
waitForConnectionToSignalUsToRead();
readRegistration();
signalConnectionThatWeHaveRead();
releaseEclusiveAccessToSharedMemoryAmongListenerProcesses();
connection:
acquireExclusiveAccessToSharedMemoryAmongConnectionProcesses();
writeRegistration();
signalListenerToRead();
waitForListenerToSignalUsThatItHasRead();
releaseEclusiveAccessToSharedMemoryAmongConnectionProcesses();
</pre>
</blockquote>
<p>If the connection dies between signalListenerToRead() and
waitForListenerToSignalUsThatItHasRead() or while waiting then obscure problems
occur. Most notably, the next connection with a that starts up will not die
off. Also, it is certain that the currently registered connection will die
at this point because that's where it sits and waits for the listener to hand
it a client.</p>
<p>Fundamentally, the listener needs to know that the connection has died off
and needs not to signalConnectionThatWeHaveRead(). For the time being, I'm not
certain how to do that. So for now, I avoid the problem by making it impossible
for a connection to die off there.</p>
<p>Of course, it's always possible to kill the connection manually. In that
case, you would cause the problem that I'm trying to avoid.</p>
<p>
</body>
</html>
|
