FAQ

E R P

Error!
images/[dataset] : Permission denied

change the permission for the 'image' directory writeable for the webserver
perl 5.26 @INC error
If you get something like this instead of the login screen
Can't locate bin/mozilla/login.pl in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5) at /var/www/html/sql-ledger/login.pl line 119.
add the code below at the beginning of am.pl and login.pl if you have symlinks to the other files in the root directory, otherwise add it to all the scripts in the sql-ledger root directory
```
 BEGIN {
     push @INC, '.';
 }
 
```
For some other workarounds see https://metacpan.org/pod/release/XSAWYERX/perl-5.26.0/pod/perldelta.pod#Removal-of-the-current-directory-%28%22.%22%29-from-@INC
spool and template directory missing
The latest archives, starting with 3.0.7, do not have a spool and/or templates directory. Add the directories with web server write privileges in the sql-ledger directory before calling up admin.pl

Apache 2.4 configuration

 AddHandler cgi-script .pl
 
 Alias /sql-ledger /usr/local/sql-ledger/
 <Directory /usr/local/sql-ledger>
   Options ExecCGI Includes FollowSymlinks
 </Directory>
 
 <Directory /usr/local/sql-ledger/>
   AllowOverride None
   Require all granted
 </Directory>

preamble.tex : No such file or directory
templates are provided as is, some templates may have links to other files which are not included. The preample.tex file is a separate file which holds the header information for a template. Find a template with the header, that is everything up to \begin document, and put the stuff into a file called preamble.tex or simply cut and paste the header into the file and remove the <%include preamble.tex%> directive.
wrong papersize
If the papersize for postscript and PDF does not match set the proper size with texconfig-sys
```
 texconfig-sys pdftex paper letter
 
```
Install Notes for Lighttpd
- Make sure mod_cgi and mod_alias are in server.modules list
- Add sql-ledger directory as an alias, ie:
  alias_url = ( "/sql-ledger/" => "/usr/local/sql-ledger/" )
- Set up perl as handler for *.pl files.
  cgi.assign = ( ".pl" => "/usr/bin/perl" )
  Note: server.follow-symlink is on by default in lighttpd.
- Deny access to users directory
  $HTTP["url"] =~ "/sql-ledger/users" { url.access-deny = ("") }

PostgreSQL 8.x

PostgreSQL added a restriction in v8.x that a sequence cannot be dropped after if it is referenced by a table. However backups created with SQL-Ledger v2.6.13 and earlier have a DROP SEQUENCE step in the backup which will fail. To fix this edit the backup before restoring on a 8.x system.

This typically looks like this:

 DROP SEQUENCE id ;
 CREATE SEQUENCE id START 10579;
 --
 DROP SEQUENCE invoiceid ;
 CREATE SEQUENCE invoiceid START 11096;
 --
 DROP SEQUENCE orderitemsid ;
 CREATE SEQUENCE orderitemsid START 178;
 --
 DROP SEQUENCE jcitemsid ;
 CREATE SEQUENCE jcitemsid START 53;

Change it to:

 SELECT SETVAL('id', 10579);
 SELECT SETVAL('invoiceid', 11096);
 SELECT SETVAL('orderitemsid', 178);
 SELECT SETVAL('jcitemsid', 53);

PostgreSQL template1
template1 is a template to build new databases from. Anything you put into template1 will be copied to a new database. When you try to create a new dataset, the tables are already created from template1 and when SQL-Ledger tries to create them you'll get an error that the tables already exist.

PostgreSQL 8.0+ and SQL-Ledger v2.6.3 and older

To use SQL-Ledger < v2.6.3 with 8.0+ you have to modify the Pg-tables.sql file. The file is in the sql directory.

 CREATE TABLE orderitems (
   trans_id int,
   parts_id int,
   description text,
   qty float4,
   sellprice float8,
   discount float4,
   unit varchar(5),
   project_id int,
   reqdate date,
   ship float4,
   serialnumber text,
   id int default nextval('orderitemsid')
 ) WITH OIDS;
 
 CREATE TABLE inventory (
   warehouse_id int,
   parts_id int,
   trans_id int,
   orderitems_id int,
   qty float4,
   shippingdate date,
   employee_id int
 ) WITH OIDS;
 
 CREATE TABLE assembly (
   id int,
   parts_id int,
   qty float,
   bom bool,
   adj bool
 ) WITH OIDS;

This change is only required for SQL-Ledger versions 2.6.4 and older!

If you already built a dataset without this change you will get an error when you try to save a PO or assembly.

"ERROR: column "oid" does not exist"

Drop the tables and rebuild WITH OIDS.

Japanese characters
modify apache so that EUC_JP is the main additional language.
comment out all unrelated languages except, UTF_8 shift jis, EUC_JP
restart apache
Fire up psql and do the encoding as per the table:-
Table 5-2. Postgres Client/Server Character Set Encodings
Server Encoding Available Client Encodings
EUC_JP EUC_JP, SJIS
EUC_TW EUC_TW, BIG5
LATIN2 LATIN2, WIN1250
LATIN5 LATIN5, WIN, ALT
MULE_INTERNAL EUC_JP, SJIS, EUC_KR, EUC_CN, EUC_TW, BIG5, LATIN1 to LATIN5, WIN, ALT, WIN1250
Create the dataset from admin.pl, now the Japanese shows up in multibyte encoding
Now you can write in Hiragana, Katakana, Kanji and English

Error posting a check/receipt

 DBD::Pg::db do failed: ERROR: syntax error at or near "," at character 114
 Error!
 INSERT INTO acc_trans (trans_id, chart_id, transdate,amount)
 VALUES (13314, , '03-22-2005',1.00 * 1)
 ERROR: syntax error at or near "," at character 114

This error is caused by a transaction which does not have links to an AR/AP account.
There must be at least on AR and AP account. Then edit all your transactions and repost.

SELinux
http://fedora.redhat.com/docs/selinux-faq-fc3/index.html
modify /etc/selinux/conf and set SELINUX=permissive
use audit2allow and add the following to policy.conf, then run "make reload"
#line 83
allow httpd_sys_script_t port_type:{ tcp_socket udp_socket } { send_msg recv_msg };
allow httpd_sys_script_t sysctl_kernel_t:dir search;
allow httpd_sys_script_t sysctl_t:dir search;
allow httpd_sys_script_t tmp_t:sock_file write;
allow httpd_sys_script_t httpd_sys_content_t:lnk_file read;
allow httpd_sys_script_t sysctl_kernel_t:file read;
allow httpd_sys_script_t unconfined_t:unix_stream_socket connectto;
session timeouts
If you get frequent timeouts or can't even log in synchronize the clock with your server. It could also be an issue with the timeout value set in your preferences. The timeout is calculated in seconds so if you want to limit this to 10 minutes set the value to 600.
lineitems not printing
Templates edited on a UNIX platform don't work on a DOS platform. To make them work load the template either with the builtin editor or a text editor and save. Templates designed on a Windows platform have the same problem and won't work on a UNIX platform. Once you strip the ^M's the'll work just fine. This only affects the .tex templates because latex can't process them properly.
UTF-8 character encoding
Most of the translations are in ISO format. To convert the translations change directory to locale/cc and convert the files with iconv to UTF-8. You should also convert the COAs too and make sure you select UTF-8 encoding when you set up a new dataset.
characterset problems
If you have problems displaying the correct characterset try adding
```
   AddDefaultCharset On
```
in your httpd.conf file.
About installation
The easiest is to use the setup.pl script as root. You don't need to know very much about your system, just login as root, run 'perl setup.pl' and follow the prompts.
setup.pl does not check for missing software packages and it is your responsibility to have the required software installed either from source or from a package.
Requirements are clearly indicated in the README file and on the download page.
cannot create function
- either run createlang and install the plpgsql language handler or install yourself. For more information how to do that, visit PostgreSQL and read the interactive documentation for procedural languages.
- load admin.pl
- unlock the system
- login
The requested URL /sql-ledger/admin.pl was not found
Your webserver doesn't know where to find the script. Most commonly this is from distributions hiding webserver configuration files in different locations or files and setup.pl wasn't able to configure the location for you. Find out which file (httpd.conf, httpdcommon.conf, ...) controls your webserver configuration and add
```
   # SQL-Ledger
   Include /etc/httpd/sql-ledger-httpd.conf
```
Create a file 'sql-ledger-httpd.conf' in /etc/httpd and copy the next part into the file.
```
   AddHandler cgi-script .pl
   AddDefaultCharset On
   Alias /sql-ledger /var/www/sql-ledger/
   <Directory /var/www/sql-ledger>
     Options ExecCGI Includes FollowSymlinks
   </Directory>
 
   <Directory /var/www/sql-ledger/users>
     Order Deny,Allow
     Deny from All
   </Directory>
```
replace '/etc/httpd' and '/var/www' with the appropriate directories.
If you run Apache and configuration files are in a conf.d directory you don't need to change httpd.conf but simply copy sql-ledger-httpd.conf into /etc/apache/conf.d and restart apache.
users/members : Permission denied
Your webserver must have write access to the users directory. If your server runs as user/group 'apache:apache' then set the users directory to owner/group apache:apache.
```
   # chown -R apache:apache users
```
Dataset newer than version
You are trying to use an older version with a dataset which was created with a newer version.
PDF option not working
Check if you have latex and pdflatex installed.
Apache 2.0 "error 500"
Some of the early versions of Apache 2.0 (< patchlevel 44) had a rewrite engine which unescaped escaped strings. This created a lot of problems and I worked around it by escaping strings twice. If you get a server 500 error 'filename too long' or if collapsed menus don't expand you may have to adjusted the following code in SL/Form.pm and change the number (44) on line 84.
```
   # for Apache 2 we escape strings twice
   if (($ENV{SERVER_SIGNATURE} =~ /Apache\/2\.(\d+)\.(\d+)/) && !$beenthere) {
     $str = $self->escape($str, 1) if $1 < 2 && $2 < 44;
   }
```
IDENT Authentication failed for user "sql-ledger"
Edit pg_hba.conf and change authentication to
```
   local           all        all           trust
```
The file is in the 'data' directory of your postgresql installation. This is different with every distribution so look for it.
```
   # find / -name 'pg_hba.conf'
```
Some people can't read and seem to think 'localhost' is just some fancy name for a local machine. 'localhost' is a host like any other host on a network. A 'local' entry in pg_hba.conf will allow local connections ONLY and not allow host connections. If you then try to connect to 'localhost' postgres will come back with an authentication error because you tried to connect to a host.
WARNING! DO NOT just put a 'host all all trust' in pg_hba.conf and leave your computer hooked up to the Internet. It's ok for trusted internal networks but NOT when you are hooked up to the Internet.
DBD-Pg for Mandrake 9.0
Mandrake did not package a compiled DBD-Pg package again, so install DBD-Pg from the source package. Install perl-DBD-Pg-1.01-4mdk.i586.rpm from the 'contrib' area. (Mandrake / 9.0 / contrib / RPMS)
LaTeX error
If for some reason LaTeX produces an error message check for strange characters in your account description and parts description and use \usepackage[latin1]{inputenc} in the preamble.
LaTeX templates
If you don't want to edit tex code by hand, you could use Lyx, Abiword, or any WYSIWYG editor capable of exporting latex code. To do that you must change the tokens for the variables <% and %> to something like << and >>. % is the comment character in tex. There is also a pagebreak block which must be commented out. When you are done with the changes replace << and >> with <% and %> and uncomment the pagebreak block.
LaTeX is difficult but it also offers a much superior environment to produce professionally looking forms in postscript and PDF format. Unfortunately with all that power there is also a steep learning curve.

W3M

pass terminal=mozilla when you start w3m

   $ w3m -F http://localhost/sql-ledger/login.pl?terminal=mozilla

To use without frames

   $ w3m http://localhost/sql-ledger/login.pl?terminal=lynx

PDF option disappeared
Edit sql-ledger.conf and set $latex = 1;
sql-ledger.conf is perl code. If there is a mistake in the file it will not be loaded and the internal defaults are used which turn off $latex, hence no PDF option.
Installation on Windows (WIN32)
- install Apache, perl, Postgres or Oracle, DBI and the appropriate DBD module
- download the latest version of SQL-Ledger
- extract the files to c:\apache\sql-ledger
- run 'perl shebang' to change the first line of the scripts. If perl is not in c:\perl\bin' change '#!c:\\perl\\bin\\perl' to the location where your perl binary is.
- edit c:\apache\conf\httpd.conf and add
```
 Alias /sql-ledger "c:/apache/sql-ledger/"
 <Directory "c:/apache/sql-ledger">
   AllowOverride All
   AddHandler cgi-script .pl
   AddDefaultCharset On
   Options +ExecCGI
   Order Allow,Deny
   Allow from All
 </Directory>
 
 <Directory "c:/apache/sql-ledger/users">
   Order Deny,Allow
   Deny from All
 </Directory>
 
```
- start Apache
- start Postgres|Oracle|DB2
- connect to http://localhost/sql-ledger/admin.pl and set up datasets
- connect to http://localhost/sql-ledger/login.pl and login
Installation on Windows /w cygwin
- install cygwin Apache, perl, postgres, gcc and lynx
- install DBI
- tar zxvf DBI-1.30.tar.gz
- cd DBI-1.30
- perl Makefile.PL
- make install
- install DBD-Pg
- tar -zxvf DBD-Pg-1.13.tar.gz
- export POSTGRES_INCLUDE=/usr/include/postgresql
- export POSTGRES_LIB=/usr/lib/postgresql
- cd DBD-Pg-1.13
- perl Makefile.PL
- make install
- edit /etc/apache/httpd.conf and add
```
 AddHandler cgi-script .pl
 AddDefaultCharset On
 Alias /sql-ledger /var/www/sql-ledger/
 <Directory /var/www/sql-ledger>
   Options +ExecCGI
 </Directory>
 
 <Directory /var/www/sql-ledger/users>
   Order Deny,Allow
   Deny from All
 </Directory>
 
```
- install SQL-Ledger
- cd /var/www
- mkdir sql-ledger
- download setup.pl and save the file in /var/www/sql-ledger
- cd /var/www/sql-ledger
- perl setup.pl
What do I enter for the language
If you use English, nothing, if you want to use a foreign language for the login screen and admin stuff enter the language code, this is the directory in the locale directory.

Using samba to send printjobs to a printer attached to a Windows XP workstation

The next part applies to roll your own print filters only. If you use CUPS or LPRng use this as a guide how printing works. I use the printer 'Epson' as an example which is attached to a XP workstation called Raven.

 /etc/printcap entry on the server which runs lpd
  
 epson:Epson\
   :sh:\
   :lp=/dev/null:\
   :sd=/var/spool/output/epson:\
   :if=/usr/libexec/lpr/epson/prnfilter:\
 
 # end of entry in /etc/printcap
 
 
 # prnfilter shell script
 #!/bin/sh
 # Filter for Epson Stylus
 
 PATH="$PATH:/usr/local/bin"
 
 #read first_line
 read tmp
 first_line=`echo $tmp | cut -b1-2 | sed -n '1p'`
 
 first_chr=`expr "$first_line" : '\(.\)'`
 first_two=`expr "$first_line" : '\(..\)'`
 rewindstdin
 
 if [ "$first_two" = "%!" ]; then # Postscript file
   gs @st640p.upp -dSAFER -dNOPAUSE -q -sOutputFile=/tmp/$$ -
 else
   # text file
   cat > /tmp/$$
   echo -n "^L" >> /tmp/$$
   smbclient '\\Raven\Epson' "" -c 'printmode text'
 fi
 
 smbclient '\\Raven\Epson' "" -P -c "print /tmp/$$"
 rm /tmp/$$
 
 # EOF
 
 
 rewindstdin is a small program to rewind the filehandle for STDIN
 save the next part up to EOF to a file rewindstdin.c and compile
 
 #include <sys/types.h>
 #include <unistd.h>
 extern int errno;
 main()
 {
   if( lseek(0,0,0) == 0 ){
     errno = 0;
   }
   return( errno );
 }
 
 # EOF
 
 compile to an executable
 
 gcc -o /usr/local/bin/rewindstdin rewindstdin.c

beginning balances
Add a GL Journal entry and enter the beginning balance for your accounts. Beginning balances are the balances from your last balance sheet. If you also add open invoices to account for COGS for inventory, add the invoices and make the appropriate adjustments.
establish a beginning inventory
add the parts with a vendor invoice. Use the real cost for the items, not zero. If you use zero cost then the cost of goods will be zero when you sell the item.
Assemblies
Assemblies are manufactured goods assembled from parts, services and assemblies. Because you do not buy assemblies you 'stock assemblies' by adding assembled units to your inventory. The quantity for individual parts is reduced and the quantity for the assembly increased. To disassemble an assembly you simply return the parts to inventory by entering a negative quantity for the number of assemblies to stock.
customizing SQL-Ledger
The application interface consists of a global and private interface.
DBD-Pg not installed
Most modern distributions now package DBD-Pg. If it is not packaged follow this recipe to get it working.
- check if you have the header files for PostgreSQL
  $ find / -name 'libpq-fe.h'
  if nothing shows up install the development package for PostgreSQL
- download and untar DBD-Pg
- set the environment variables POSTGRES_LIB and POSTGRES_INCLUDE
- cd to DBD-Pg directory
  as ordinary user
  $ perl Makefile.PL
  $ make
  $ make test
  if all went well su to root
  # make install
- remove DBD-Pg
login.pl has compilation error
This could be because of a missing configuration file in the users directory
check the permission for the users directory. The directory must be set writeable for the webserver.
script not executing, shows in browser instead
Add
```
   AddHandler cgi-script .pl
```
in your httpd.conf file.
unknown terminal!
the frontend script couldn't figure out which browser you are using
include the terminal variable on the URL
```
   http://localhost/sql-ledger/login.pl?terminal=lynx
```
Valid terminal variables are lynx and mozilla

permission denied

Check if your webserver has write permission to write to the following directories and files therein:

   users/
   templates/
   images/
   spool/
   css/
 
   # chown -hR nobody:nogroup users templates images spool css
 
   replace nobody:nogroup with the web server user and group.
   Some systems use apache:apache, www, www-data, ...

permission denied to access tables
The user you entered in the "Database section" must be a valid database user who has rights to access the tables.
If the tables are owned by 'joe' and you enter 'mary' as the dba you might run into this problem if mary doesn't have the rights to access tables owned by joe.
html and graphics files don't show up on screen
Enable Includes and FollowSymlinks Options in your httpd.conf file
```
   <Directory /usr/local/sql-ledger>
     Options ExecCGI Includes FollowSymlinks
   </Directory>
 
```
switch display to a foreign language
Load your preferences and select the language.
Language selection is in accordance to ISO 3166-1 standards.
Text shows in English when I use a foreign language
This is because the corresponding hash entry is missing. Add the missing text in the locale/cc/all or locale/cc/missing file and run 'perl locales.pl' from the command line to rebuild the individual files.
cc refers to the country code.
switch to a foreign language for the login and admin screen
Edit sql-ledger.conf and enter the code for the $language variable
```
   $language = "de";
```
This is a global change and applies to all logins, individual settings can be changed by setting the language in your Preferences.

SQL-Ledger security

The security features built into SQL-Ledger provide passwords, two step authentication and access control. If you expose your server on the Internet we strongly advise to use your web server's authentication mechanism as well.

Some other precautions which are out of our control must also be taken. It matters where you install SL and how you configure your web server and SQL database server.

   Typical setups:
   
   /usr/local/vh/www                   <- DocumentRoot for virtual host
   /usr/local/vh/sql-ledger            <- Alias for sql-ledger
   /usr/local/vh/users                 <- users directory out of reach
 
 
   /usr/local/vh/www                   <- DocumentRoot for virtual host
   /usr/local/vh/www/sql-ledger        <- Alias for sql-ledger
   /usr/local/vh/www/sql-ledger/users  <- users configuration files and tmp space
 
   <Directory /usr/local/vh/www/sql-ledger/users>  <- disable webserver access
     Order Deny,Allow                                 for users directory
     Deny from All
   </Directory>

The location for the users directory can be specified in sql-ledger.conf

Set permission for the users and templates directory to 711
If you do not want anyone to change the templates with the builtin editor set the files in templates/directory/ to read only or disable the menu item to edit the templates.
You can set up a read-only environment if you disable the menu items to add data. i.e 'Add Transaction' if unchecked you will not be able to add a transaction or repost a transaction. You may look at it but nothing else.
There are various settings for audit control and you can disable reposting entirely or up to a certain date. And with audit trails enabled you can keep tab of who is doing what.
For PostgreSQL you can also set who has access to the server in the file pg_hba.conf
Authentication crypt does not work because not all SQL servers accept encrypted passwords.
In addition you can secure the tables from unauthorized access by setting up a different database user and GRANT rights. For instance, users without DELETE rights will still be able to use the program, change customers and vendors, add transactions but will not be able to delete or repost transactions.
To lock all the tables to create a RO system GRANT SELECT rights only.
Other security options include a secure shell, your webserver's authentication system, SSL, encrypted tunnels, ...

Error! images/[dataset] : Permission denied

perl 5.26 @INC error

spool and template directory missing

Apache 2.4 configuration

preamble.tex : No such file or directory

wrong papersize

Install Notes for Lighttpd

PostgreSQL 8.x

PostgreSQL template1

PostgreSQL 8.0+ and SQL-Ledger v2.6.3 and older

Japanese characters

Error posting a check/receipt

SELinux

session timeouts

lineitems not printing

UTF-8 character encoding

characterset problems

About installation

cannot create function

The requested URL /sql-ledger/admin.pl was not found

users/members : Permission denied

Dataset newer than version

PDF option not working

Apache 2.0 "error 500"

IDENT Authentication failed for user "sql-ledger"

DBD-Pg for Mandrake 9.0

LaTeX error

LaTeX templates

W3M

PDF option disappeared

Installation on Windows (WIN32)

Installation on Windows /w cygwin

What do I enter for the language

Using samba to send printjobs to a printer attached to a Windows XP workstation

beginning balances

establish a beginning inventory

Assemblies

customizing SQL-Ledger

DBD-Pg not installed

login.pl has compilation error

script not executing, shows in browser instead

unknown terminal!

permission denied

permission denied to access tables

html and graphics files don't show up on screen

switch display to a foreign language

Text shows in English when I use a foreign language

switch to a foreign language for the login and admin screen

SQL-Ledger security

Error!
images/[dataset] : Permission denied