Mediocrity is no longer acceptable in business, but how do you tell the client that they have cleared the lowest bar?
Perhaps a more direct approach is needed!
Unexpected internal c-tree(R) error #7495 (uerr_cod=0)
That’s the error message I was getting one day. Nothing major happened, the update routines just stopped working on day!
I’m importing a bunch of data from the usual tables (CustomLists, Customers, Items, Sales) in to the SQL Server back end for faster processing against the company’s CMS. I do this because of the way MYOB has done it’s ODBC driver, every time you do a query, all the table records are read in to memory and THEN the filters are applied, it’s horrendous. Especially if, like this company, you have upwards of 100,000 sales lines. So anyway, it happily dumps the data into the SQL Server tables (using MS Access 2007 as an intermediary) without issue.
The error occurs when I start to insert new records in to the MYOB file.
I’ve tried using the same connection (Reading from a RW connection). I’ve tried using a two connections (Reading from a RO connection and writing to a RW connection); to no avail.
The solution?
ONLY read from or write to the MYOB file while the connection is open. I have taken this to the extreme and put a switch in at the start of the run; if it’s 4am – write any new sales to the MYOB file; if not – read all the MYOB details and update the CMS accordingly.
The error is gone and the work flow is easy to understand for the staff:
Obviously the memory handler for the underlying database couldn’t deal with the large number of rows being returned.
Update the booking system(s) with the latest payment information from MYOB.
The company does not want to service customers that are not paying their account.
Occasionally, very occasionally, I find myself having one database diagram to rule them all. This is the one that I have spent the most time on; the one that unifies the most tables and has the simplest layout. Something clicked when I was filling in the connections and now I want to bring it to all the other versions of the database, but there’s no cut-and-paste!
After a little searching I found this:
INSERT INTO
target_db.dbo.sysdiagrams
SELECT
[name],[principal_id],[version],[definition]
FROM
source_db.dbo.sysdiagrams
Just change the target_db and source_db labels and it will bring everything across. Obviously there are further refinements that could be put in the query, but the basics are there, extrapolate as required.
I have extensive experience in developing Access databases.
I have worked with Access 2007, 2003, xp, 2000, 97 and even 2.0!
I also have a solid understanding of SQL Server 2005/2000 and have built/maintained/improved upon many enterprise level systems.
In the interests of not having to re-figure out how to install the above systems on my mac os (tiger 10.5.8) I am going to post the commands that I eventually used to get the damn thing to, essentially, recognise the new version of openssl I installed to be able to use the SHA256 digest method so I could talk to facebook correctly.
3.days.ago everything went pear shaped.
Prior to running this:
brew install openssl
mkdir /usr/local/src
cd /usr/local/src
curl -O ftp://ftp.ruby-lang.org/pub/ruby/1.8/ruby-1.8.7-p330.tar.gz
tar xzvf ruby-1.8.7-p330.tar.gz
cd ruby-1.8.7-p330
./configure --enable-shared --enable-pthread CFLAGS=-D_XOPEN_SOURCE=1 --with-openssl-dir=/usr/local/Cellar/openssl/0.9.8o/
make
sudo make install
cd ..
curl -O http://rubyforge.iasi.roedu.net/files/rubygems/rubygems-1.3.4.tgz
tar xzvf rubygems-1.3.4.tgz
cd rubygems-1.3.4
sudo /usr/local/bin/ruby setup.rb
cd ..
sudo gem install rails
sudo gem install mongrel
sudo gem install capistrano
sudo gem install mysql -- --with-mysql-dir=/usr/local/mysql
And that should just about do it. Check it’s all working (specifically the SHA256 part) with this command
ruby -ropenssl -e 'p OpenSSL::Digest::Digest.new("sha256")'
which should not return any errors. That being the point of the entire exercise.
I’ve been using a wiki for documentation purposes (indeed!) at a client site in the last few months. It’s fantastic! Once again, usefulness trumps technology. The wiki (http://www.usemod.com/cgi-bin/wiki.pl” target=”_blank”>UseMOD) is a single perl script that happily runs on the Windows, Apache, SQL Server, Cygwin Perl/Rails/PHP (WASC, I guess) machine I have built to run their web sites. Very simple to configure and pretty easy to understand if you need to make changes, which I did, of course.
Getting the localtime right! Pretty simple stuff, but it makes such a difference when looking at the historical changes page. The changed lines are in red.
sub CalcDay {
my ($ts) = @_;
<span style="color: #ff0000;">#$ts += $TimeZoneOffset;</span>
my ($sec, $min, $hour, $mday, $mon, $year) = localtime($ts);
if ($NumberDates) {
return ($year + 1900) . '-' . ($mon+1) . '-' . $mday;
}
return ("January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November",
"December")[$mon]. " " . $mday . ", " . ($year+1900);
}
sub CalcTime {
my ($ts) = @_;
my ($ampm, $mytz);
<span style="color: #ff0000;">#$ts += $TimeZoneOffset;</span>
my ($sec, $min, $hour, $mday, $mon, $year) = localtime($ts);
$mytz = "";
if (($TimeZoneOffset == 0) && ($ScriptTZ ne "")) {
$mytz = " " . $ScriptTZ;
}
$ampm = "";
if ($UseAmPm) {
$ampm = " am";
if ($hour > 11) {
$ampm = " pm";
$hour = $hour - 12;
}
$hour = 12 if ($hour == 0);
}
$min = "0" . $min if ($min<10);
return $hour . ":" . $min . $ampm . $mytz;
}
sub TimeToText {
my ($t) = @_;
<span style="color: #ff0000;">$t-= $TimeZoneOffset;</span>
return &CalcDay($t) . " " . &CalcTime($t);
}
I’ve been doing a lot of book work lately, catching up on the finer details of my credit card statement(s), and I have had an absolutely fantastic time getting to know my http://saasu.com/“>online accounting package. It’s brilliant!
All the basics are there, and have been for quite a while, but now they are adding the “wouldn’t it be great if…” stuff. For instance: automatic statements. Automatic statements that only get emailed to over-due accounts. Automatically. Set it up now and be reminded when recalcitrant clients are dragging their feet. I have set mine to 5 days over due, checked every Monday. Did I mention it’s automatic?
I usually have to remember to contact my late paying clients and it’s a bit of a drag, calling people for money. Now my system will be starting the conversation! This gives me more time to do my business and less time lost to running it.
If you haven’t already; give the guys at Saasu.com a test run, it’s free and highly recommended (I use their stuff to streamline my business).
A simple VBA class (Version 1.0) that will let you process 2-Party credit card transactions securely through the CommonWealth Bank’s CommWeb VPC merchant facility. This is primarily for MOTO transactions (Mail Order / Telephone Order) where the purchaser isn’t directly involved in the purchase/refund.
You need to include the Microsoft XML 4.0 library in your references options for this to work out of the box.
I also use a public module called settings that provides global access to system wide variables like settings.merchant_id, I think that part is pretty self explanatory.
Option Compare Database
Option Explicit
Private vpc_version As String '1-8
Private vpc_command As String '1-16
Private vpc_merch_txn_ref As String '1-40
Private vpc_access_code As String '8
Private vpc_merchant As String '1-16
Private vpc_order_info As String '1-34
Private vpc_amount As Long '1-10
Private vpc_card_num As String '15-40
Private vpc_card_exp As String '4
Private vpc_user As String 'used to refund
Private vpc_password As String 'used to refund
Private vpc_transaction_no As String 'used to refund
Private vrs_authorise_id As String
Private vrs_transaction_no As String
Private vrs_response_code As String
Private vrs_receipt_no As String
Private vrs_txn_response_code As String
Private vrs_message As String
Private vrs_merch_txn_ref As String '1-40
Private vrs_order_info As String '1-34
Private vrs_amount As Long '1-10
Private h As MSXML2.ServerXMLHTTP40
Private errorExists As Boolean
Private message As String
Private exception As String
Private Sub Class_Initialize()
Set h = New MSXML2.ServerXMLHTTP40
vpc_version = "1"
vpc_merchant = settings.merchant_id
vpc_access_code = settings.moto_username
vpc_user = settings.creditcard_refund_username
vpc_password = settings.creditcard_refund_password
End Sub
Private Function refund_data() As String
Dim buf As String
buf = "vpc_Version=" & vpc_version & "&vpc_Command=refund"
buf = buf & "&vpc_MerchTxnRef=" & vpc_merch_txn_ref
buf = buf & "&vpc_AccessCode=" & vpc_access_code
buf = buf & "&vpc_Merchant=" & vpc_merchant
buf = buf & "&vpc_TransNo=" & vpc_transaction_no
buf = buf & "&vpc_Amount=" & vpc_amount
buf = buf & "&vpc_User=" & vpc_user
buf = buf & "&vpc_Password=" & vpc_password
refund_data = buf
End Function
Private Function payment_data() As String
Dim buf As String
buf = "vpc_Version=" & vpc_version & "&vpc_Command=pay"
buf = buf & "&vpc_AccessCode=" & vpc_access_code
buf = buf & "&vpc_Amount=" & vpc_amount
buf = buf & "&vpc_CardExp=" & vpc_card_exp
buf = buf & "&vpc_CardNum=" & vpc_card_num
buf = buf & "&vpc_Merchant=" & vpc_merchant
buf = buf & "&vpc_OrderInfo=" & URLEncode(vpc_order_info)
buf = buf & "&vpc_MerchTxnRef=" & URLEncode(vpc_merch_txn_ref)
payment_data = buf
End Function
Public Function process_transaction(cc_number As String, cc_exp As Date, Amount As Currency, our_ref As String) As Boolean
'pre-load
process_transaction = True
'prepare
vpc_merch_txn_ref = our_ref
vpc_card_num = cc_number
vpc_card_exp = Format(cc_exp, "yymm")
vpc_amount = (Round(Amount, 2) * 100)
'process
If Not Err Then
h.Open "POST", "https://migs.mastercard.com.au/vpcdps", False
h.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
h.Send payment_data()
If Len(h.responseText) = 0 Then
errorExists = True
message = "No response from the Virtual Payment Client (The Bank)"
process_transaction = False
Exit Function
End If
End If
'parse results
parse_results
'post-transaction clean up
If errors_returned() Then
process_transaction = False
Exit Function
End If
End Function
Public Function refund_transaction(our_ref As String, transaction As String, Amount As Currency) As Boolean
'pre-load
refund_transaction = True
'prepare
vpc_command = "refund"
vpc_merch_txn_ref = our_ref
vpc_transaction_no = transaction
vpc_amount = (Round(Amount, 2) * 100)
vrs_authorise_id = "0" 'for those times when the provider doesn't give and auth number
'process
If Not Err Then
h.Open "POST", "https://migs.mastercard.com.au/vpcdps", False
h.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
h.Send refund_data()
If Len(h.responseText) = 0 Then
errorExists = True
message = "No response from the Virtual Payment Client (The Bank)"
refund_transaction = False
Exit Function
End If
End If
'parse results
parse_results
'post-transaction clean up
If errors_returned() Then
refund_transaction = False
Exit Function
End If
End Function
Private Sub parse_results()
' The content returned by the VPC is a HTTP POST, so the content will
' be in the format parameter1=value¶meter2=value¶meter3=value,
' i.e. key/value pairs separated by ampersands "&".
Dim results() As String
Dim pair() As String
Dim i As Long
results = Split(h.responseText, "&")
For i = 0 To UBound(results)
pair = Split(results(i), "=")
Select Case (pair(0))
Case "vpc_Amount"
vrs_amount = CCur(pair(1)) / 100
Case "vpc_AuthorizeId"
vrs_authorise_id = pair(1)
Case "vpc_ReceiptNo"
vrs_receipt_no = pair(1)
Case "vpc_transactionNo"
vrs_transaction_no = pair(1)
Case "vpc_AcqResponseCode"
vrs_response_code = pair(1)
Case "vpc_TxnResponseCode"
vrs_txn_response_code = pair(1)
Case "vpc_Message"
vrs_message = URLDecode(pair(1))
Case Else
'I'm not interested in other fields at this stage
'MsgBox "unexpected pair: " & results(i)
End Select
Next
End Sub
Private Function errors_returned() As Boolean
errors_returned = True
If Len(vrs_txn_response_code) = 0 Then
' Display an Error Page as the QSIResponseCode could not be retrieved
message = "(23) No result for this field: 'TxnResponseCode'" & vbCrLf & "Bank responded: " & vrs_message
Else
Select Case vrs_txn_response_code
Case "0" 'Transaction Succesful
Case "1" 'Unknown Error
message = "Transaction could not be processed (Unknown Error)"
Case "2", "E" 'Bank Declined Transaction
message = "Bank declined transaction (Customer should contact their Bank)"
Case "3" 'No reply from Bank
message = "No reply from Bank"
Case "4" 'Expired Card
message = "Expired Card"
Case "5" 'Insufficient Funds
message = "Insufficient Funds in Account"
Case "6" 'Error Communicating with Bank
message = "Error communicating with Bank"
Case "7" 'Payment Server System Error"
message = "Payment Server system error"
Case "8" 'Transaction Type not supported
message = "Transaction type not supported"
Case "9" 'Bank Declined Transaction
message = "Bank Declined Transaction"
Case Else
message = "Unknown Error: " & vrs_txn_response_code
End Select
' Check if the result contains an error message
If vrs_txn_response_code <> "0" Then
Dim result As String
' Get the error returned from the Payment Client
result = vrs_message
' check if result contains a value
If Len(result) <> 0 Then
' there is an error message so generate an Error Page
exception = result
End If
Else
errors_returned = False
End If
End If
End Function
Public Function get_result_transaction_id() As String
get_result_transaction_id = vrs_transaction_no
End Function
Public Function get_result_amount() As Currency
get_result_amount = vrs_amount
End Function
Public Function get_result_receipt() As String
get_result_receipt = vrs_receipt_no
End Function
Public Function get_authorise_id() As String
get_authorise_id = vrs_authorise_id
End Function
Public Function show_errors() As String
If Len(message) Then
show_errors = message
If Len(exception) > 0 Then
show_errors = message & " - " & exception
End If
Else
show_errors = "There was an error but no messages for it"
End If
End Function
Private Sub Class_Terminate()
Set h = Nothing
End Sub
I have been working on understanding the text_field_with_auto_complete functionality in Ruby on Rails for a project I am currently working on. There seems to be two written tutorials on the subject and they were a little confusing. Here is my attempt to be more specific.
Goal:
While editing an address record I want to show the user a short list of available suburbs that match what they have begun typing into he text box; and allow them to pick the one they want to use. When they save the record, I only want to store the ID of the suburb they typed in, not the actual text.
Solution:
Install the auto_complete gem:
ruby script/plugin install git://github.com/rails/auto_complete.git
This didn’t work on the windows system I was using, so I ended up having to grab the ZIP file and copy the contents to the ‘vendor/plugins/auto_complete’ folder the above command created.
First of all add to the site.rb model so we can refer to the suburb_name as if it was a field in the sites table:
def <span style="color: #ff00ff;">suburb_name</span>
<span style="color: #00ccff;">suburb.name</span> if <span style="color: #ff6600;">suburb_id</span>
end
def <span style="color: #ff00ff;">suburb_name</span>=(value)
self.<span style="color: #ff6600;">suburb_id</span> = Suburb.find_by_name(<span style="color: #ff0000;">value.split(',')[0]</span>).<span style="color: #000000;">id</span> unless value.blank?
end
Update the sites/edit.html.erb view to do the auto_complete markup:
<p>
<%= f.label :<span style="color: #ff6600;">suburb </span>%><br />
<%= text_field_with_auto_complete <span style="color: #993366;">:site</span>, :<span style="color: #ff00ff;">suburb_name</span>, {}, :skip_style => false %>
</p>
Add an action to the sites_controller.rb so the auto_complete call(s) can get the short list:
def auto_complete_for_<span style="color: #cc99ff;">site</span>_<span style="color: #ff00ff;">suburb_name</span>() <span style="color: #0000ff;">@suburbs</span> = Suburb.find(:all , :conditions=> "<span style="color: #33cccc;">name </span>like '%"+params[<span style="color: #993366;"><span style="color: #000000;">:</span><span style="color: #cc99ff;">site</span></span>][:<span style="color: #ff00ff;">suburb_name</span>].upcase+"%' and we_deliver_to=1") render :partial => '<span style="color: #99cc00;">auto_complete_</span><span style="color: #ff00ff;">suburb_name</span>' end
Finally, create the _auto_complete_suburb_name.html.erb partial in the views/sites folder, the smallest form of which is like this
<ul><% for suburb in <span style="color: #0000ff;">@suburbs</span> do %><li><%=h <span style="color: #00ccff;">suburb.name</span> %><span style="color: #ff0000;">, </span><%=h suburb.postcode %></li><% end %></ul>
Important stuff you should know