powerbuilder 10.5 with quickbooks SDK 8.0

How Do I use Quickbooks SDK with Powerbuilder 10.5
1030071002Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

diasroshanCommented:
Hi,

u need to provide more details.

You can access the database using ODBC connection.

Cheers,
Rosh
0
grant300Commented:
What kinds of things are you going to want to do using the SDK?
 - batch loads or data
 - batch extracts of data
 - some kind of quasi real time integration
 - reporting
 - some kind of additional functionality requiring a GUI
 - do you anticipate adding tables to the schema?

Regards,
Bill
0
1030071002Author Commented:
some kind of quasi real time integration using the subscribe. I am trying to get the remaining balance from invoice when it becomes zero on the fly I want to update my database when ever that happen. I would assume one by one with a date range
0
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

grant300Commented:
PowerBuilder may not be the easiest tool to use for this job.

The SDK is expecting a 3GL like C, C++, VB, or even C#.  I am not a PowerBuilder expert but you would have to deal with making calls to the SDK DLL methods from inside PB.  In addition, PB is mostly a GUI tool.  There is not user interaction with this operation at all so you really don't need to carry that around.

What is your target database?

My first thought is that you might be able to build a database trigger in Sybase ASA (the database under QuickBooks) to fire and determine when an invoice balance goes to zero.  If your target database is another Sybase product, you could the do direct updates to the target database.

While you may not want to get into the guts of the QB database, you could use the SDK with a 3GL program that runs, waits for a subscribed event, extracts the pertinant information, and then updates the target database.  It will have to run as a daemon AND you will have to have some kind of mechanism to make certain it is up and running all the time or you will lose events.

Without using a database trigger and a logging table, there is no way to be 100 percent certain you will catch every event unless you can automatically restart your daemon.  I just don't think PB is the best vehicle to do what you need done.

Regards,
Bill
0
1030071002Author Commented:
Lets go with the VB I am using a ODBC database
Regards you sound like you know your stuff inside and out  if you
need the PDF Doc for this project I can send it to you
this is more than a 500 points help so I will split this help up several times
0
1030071002Author Commented:
instead of regards I meant bill
0
1030071002Author Commented:
http://qodbc.com/schema.htm

Invoice ---> Refnumber

Invoice --> FOB = NO

Invoice --> Balance Remaining = 0.0

Invoice --> IsPaid = True

A Example of what I want to do
    strqbSQL = "SELECT txnID, refnumber FROM invoice WHERE ispaid = 1 AND fob = 'NO' AND BalanceRemaining = 0.0"

    qbrs.Open strqbSQL, qb, adOpenStatic, adLockOptimistic

   

    Do Until qbrs.EOF

       

        With qbrs

       

            txnID = Trim(.Fields("txnID"))

            orderID = Trim(.Fields("refnumber"))

            Invoices.GetByTxnID txnID

            If Invoices.Count <> 1 Then

                logfile.WriteLine ("    Error Updating Invoice " & orderID)

                mainForm.Text2.Text = "Error Updating Invoice " & orderID

                mainForm.Refresh

            Else

                logfile.WriteLine ("    Payment received for Invoice " & orderID)

                cn.Execute "UPDATE orders_participant SET qb_status = 'ADD' WHERE orderid = " & orderID

                cn.Execute "UPDATE orders SET total_recvd = total_due, order_status = 'P' WHERE orderid = " & orderID

                Invoices(1).FOB = "YES"

                Invoices.Commit

                mainForm.Text2.Text = "Invoice " & orderID

                mainForm.Refresh

            End If

       

        End With

   

        Invoices.Clear

        qbrs.MoveNext

    Loop

qbrs.close



0
1030071002Author Commented:
This Attachment is for Bill AKA grant300 once you get it I will close out this session and start a new one
QBSDK-ProGuide.zip
0
grant300Commented:
I've been cut and pasted!!! 8-)

Almost all databases are "ODBC" databases; you are only talking about the access mechanism.  There are even ODBC drivers for flat files and Excel; both of which are patently not databases.  What is the target database brand and version?

VB will work.  You will have to do a little research and figure out how to run a VB app as a service.  I know it is possible.

I guess the next step is figure out what your requirements are in terms of reliability and completeness.  If you absolutely positively have to have 100% reliability and can never lose an Invoice-zero-balance event, you have to role up your sleeves and figure out how and where to create a database trigger.  You also have to be certain that you can create a QB SDK subscription

The trick may well be that QB does not publish the DBA password but only gives you user level SELECT/INSERT/UPDATE/DELETE privs on the existing tables.  If you can get past that barrier, you are in good shape.  If not, you may have to settle for either less than 100 percent completeness or do some form of periodic poll/extract operation.

Now that I think about it, I think about it, I would use a combination of the two techniques.
 - write your daemon against the SDK
 - at startup, have it look for all the zero-balance invoices since the last time one was transfered to the target database (more about that later)
 - transfer those invoices and/or status to the target and insert a row in a new target table that tracks the invoice number and the date/time the status was transfered to the target
 - subscribe to the invoice-zero-balance event, assuming you can create such a thing
 - transfer those invoices/status as the events are received and insert rows in the new tracking table in the target

By keeping track of what was transfered when, you can be sure to get all the zero-balance invoices AND not have to worry about hacking up the QB database.

Regards,
Bill
0
grant300Commented:
BTW, have you figured out yet what you are going to do when an invoice with a zero balance is updated (corrected) and it is no longer at zero?

Replication of any kind is difficult to get right.

Bill
0
1030071002Author Commented:
When the remaining balance is zero it stayed at zero OK all I am trying to request and recieve from QB Invoice ---> Refnumber
Invoice --> FOB = NO
Invoice --> Balance Remaining = 0.0
Invoice --> IsPaid = True

when I recieve all this info I would update a flag in total different database but before I can do that I need to recieve these info on the fly and update the other database on the fly. once I can request and recieve data the rest is a piece of cake.  do you want me to close this session  out and reward you the points and start a new session or continue ? I planing to use ms studio 2005 or vb6 I would rather vb6 what is your opinion on the vb version.
0
grant300Commented:
I really can't help you any further with the QB SDK other than to say you will have to start digging through the documentation.  I looked at the EVENT portion of the doc you posted and it says that the actual events that are available are listed in the "on screen" help, whatever that is.

One piece of information I did determine is that QB events are only fired by the GUI.  Batch and SDK updates to the database do not fire events.  This may or not be a problem for you.  If you are applying cash by hand, you are in good shape, however, if you are using credit card processing or some batch application method, you cannot use QB events and subscriptions at all.

As I said before, unless the moon and the stars align and there is a QB event you can use to capture the Invoice event, even if you have to filter some of them out, I would use a database trigger and be done with it.

The very first thing you have to do is track down the list of stock events and see if you can use that mechanism at all.

You don't really have much choice about what version of VB to use; it will be whatever is supported by the QB SDK.  It may be that VB6 is too old and won't work or it will be fine.

What language would you use in Visual Studio 2005?  It supports a bunch of different languages so saying you are going to use VS doesn't net us much information.

Regards,
Bill
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1030071002Author Commented:
Excellent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.