Sidebar Sidebar
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

macOS vb 6.0 to AppleScript

B

bob5731

macrumors regular
Original poster
Jun 27, 2008
120
1
Hi
I'm to AppleScript
Code is in vb 6.0
http://www.vbforums.com/showthread.php?t=330341&upc&page=1

Code: 
Private Sub Command1_Click()
Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim sSQL As String
Dim iStart As Integer
Dim Text
    Dim iEnd As Integer
    iStart = 0
    iEnd = 0

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= C:\Users\bob\Desktop\UPC\games.mdb;" & _
        "Persist Security Info=False"

Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Do While WebBrowser1.Busy = True
        DoEvents
        Sleep 500
    Loop
    Set hDoc = WebBrowser1.Document
    Set hInp = hDoc.getElementById("upc")
    'hInp.focus
    'hInp.Value = "02881920"
    'SendKeys "{ENTER}", True
    'Find the UPC
    iStart = InStr(1, hDoc.body.innerHTML, "<TD>upc</TD>") + 37
    iEnd = InStr(iStart, hDoc.body.innerHTML, "</TD></TR>")
    'MsgBox upc
    MsgBox Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    Text1.Text = Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    Do While WebBrowser1.Busy = True
        DoEvents
        Sleep 500
    Loop
Set hInp = Nothing
    'Parse the elements and read the values of the data returned
    'Set document variable = to the new results documents page
    Set hDoc = WebBrowser1.Document
    'Find the description:
    iStart = InStr(1, hDoc.body.innerHTML, "<TD>Description</TD>") + 37
    iEnd = InStr(iStart, hDoc.body.innerHTML, "</TD></TR>")
    MsgBox Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    Text2.Text = Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    'Find the Size/Weight:
    iStart = InStr(iStart, hDoc.body.innerHTML, "<TD>Size/Weight</TD>") + 37
    iEnd = InStr(iStart, hDoc.body.innerHTML, "</TD></TR>")
    MsgBox Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    Text3.Text = Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    'Find Manufacturer:
    'iStart = InStr(iStart, hDoc.body.innerHTML, "<TD>Manufacturer</TD>") + 38
    'iEnd = InStr(iStart, hDoc.body.innerHTML, "(<A href=")
    'MsgBox Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    'Text4.Text = Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    'Find the Entered/Modified:
    'iStart = InStr(iStart, hDoc.body.innerHTML, "<TD>Entered/Modified</TD>") + 42
    'iEnd = InStr(iStart, hDoc.body.innerHTML, "</TD></TR>")
    'MsgBox Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
    'Text5.Text = Mid$(hDoc.body.innerHTML, iStart, iEnd - iStart)
sSQL = "INSERT INTO table " & "(UPC, Description) VALUES ('" & Text1.Text & "', '" & Text2.Text & "')"
Debug.Print sSQL
'ADOCn.Execute sSQL
End Sub

Private Sub Form_Load()
    WebBrowser1.Navigate2 "http://www.upcdatabase.com/item.pl"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Dim strConn As String
'Dim strSQL As String
'strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Brandon Crocker\Desktop\SALES 1\SALES 1\Database.mdb;Persist Security Info=False"
'strSQL = "SELECT * FROM MemberDetails"
End Sub
:mad:
 
I'm afraid that's just not going to happen. There are probably ways to read access databases from a mac, but certainly nothing native to applescript. If i needed to do something similar to this on a mac, i'd probably try to hack it together with some shell, using curl, etc.

Can you move the backing store to mysql or some other format?

-Lee
 
Well... that part is fine, i mean replacing the access databases in use (.mdb files).

I had written a few lines that would pull out the description from the UPC page, but i hadn't figure out a good way to deal w/ the HTML escape sequences. The example in the script you posted has an apostrophe in the description. This isn't insurmountable, and using something other than curl to pull the page might help. Now that I think of it, i don't know if the vbscript would have dealt with this anyway, so maybe not a big deal. I don't really see any escaping of anything, so this would be roughly equivalent:
Code: 
curl http://www.upcdatabase.com/item/$1| grep Description | awk -F'<[/]*td>' '{print "Insert into table (UPC,Description) values (\047"'$1'"\047,\047"$6"\047);"}'

Saved as a .sh. The one argument it takes is the UPC, and it will generate the sql. I would imagine that if there was a means to run sql from the commandline for whatever database you were to want to use, it would be trivial to just run the insert.

-Lee
 
c#

Code: 
using System;
using System.Collections.Generic;
using System.Text;
using CookComputing.XmlRpc;
using System.Xml;
 
namespace KitchenInventory  //can you guess what my software does lol...
{
 
 
    [XmlRpcMissingMapping(MappingAction.Ignore)] 
//the above line keeps missing members from casuising an error,  more info at [url]www.xml-rpc.net[/url]
// I noticed many records don’t have the mfr_comment data, so the member is NOT returned in the xml.
//
// youll still need to deal with the missing data, but at least your
// rpc call will work, FYI:  Use the Internet Exploere "Fidiler add in"
// to veiw returned data when you make a rpc call… very handy
 
    public struct upcLookupValues
    {
        public string upc;
        public int pendingUpdates;
        public bool isCoupon;
        public string ean;
        public string issureCountryCode;
        public string mfr_comment;
        public string mfr;
        public string description;
        public bool found;
        public string size;
        public string message;
        public string issureCountry;
        public string lastModified;
 
    }
 
    [XmlRpcUrl("http://www.upcdatabase.com/rpc")]
    public interface IgetHelp : IXmlRpcProxy
    {
        [XmlRpcMethod("help")]
        string upcHelp();
    }
 
    [XmlRpcUrl("http://www.upcdatabase.com/rpc")]
    public interface IlookupUPC : IXmlRpcProxy
    {
        [XmlRpcMethod("lookupUPC")]
        upcLookupValues lookupUPC(string upcCode);
    }
 
    [XmlRpcUrl("http://www.upcdatabase.com/rpc")]
    public interface IlookupEAN : IXmlRpcProxy
    {
        [XmlRpcMethod("lookupEAN")]
        upcLookupValues lookupEAN(string EANCode);
 
    }
 
    [XmlRpcUrl("http://www.upcdatabase.com/rpc")]
    public interface IconvertUPCE : IXmlRpcProxy
    {
       [XmlRpcMethod("convertUPCE")]
       string convertUPCE(string upcCode);
 
    }
 
    class lookupUPC
    {
        public static string getHelp()
        {
 
            //calls the upcdatabase.com xml server with the help paramaters, returns as string with the help...
            IgetHelp upcObj = XmlRpcProxyGen.Create<IgetHelp>();
 
            string myLookup = upcObj.upcHelp();
            return myLookup;
        }
 
        public static string convertUPCE(string upce_code)
        {
            //cals the upce_convert method, and returns the ean string…
 
            IconvertUPCE upcObj = XmlRpcProxyGen.Create<IconvertUPCE>();
 
                //turn off the xmlRpcProxy keep alive,
            // fixes the The underlying connection was closed: An unexpected error occurred on a send. 
            upcObj.KeepAlive = false;
 
 
            string myLookup = upcObj.convertUPCE(upce_code);
            return myLookup;
        }
 
        public static upcLookupValues lookupBarCode(string barCode)
        {
            string myEan;
            upcLookupValues myOutput;
            switch (barCode.Length)
            {
                case 12:
                    myOutput = getEan("0"+ barCode);
                    break;
                case 8:
                    myEan = convertUPCE(barCode);
                    myOutput = getEan(myEan);
                    break;
                default:
                    myOutput = getEan(barCode);
                    break;
 
            }
            myOutput.upc = barCode;
            return myOutput;
 
        }
        public static upcLookupValues getEan(string eanCode)
        {
 
 
            IlookupEAN upcObj = XmlRpcProxyGen.Create<IlookupEAN>();
 
            //turn off the xmlRpcProxy keep alive,
            // fixes the The underlying connection was closed: An unexpected error occurred on a send. 
            upcObj.KeepAlive = false;
            //phone 821793000080
            upcLookupValues upcOutput = upcObj.lookupEAN(eanCode);
 
 
            return upcOutput;
        }
 
        public static upcLookupValues getUpc(string upcCode)
        {
            //this method depricated, but still works, I now padd a 0 on the begging of the UPCA code and use getEAN method
 
            IlookupUPC upcObj = XmlRpcProxyGen.Create<IlookupUPC>();
 
            //turn off the xmlRpcProxy keep alive,
            // fixes the The underlying connection was closed: An unexpected error occurred on a send. 
            upcObj.KeepAlive = false;
 
            upcLookupValues upcOutput = upcObj.lookupUPC(upcCode);
 
 
            return upcOutput;
        }
    }
 
}
 
There weren't any questions in those last two posts. If you want to use the web service requests instead of screen-scraping pages, you certainly can. There are examples on that page in perl, python, java and PHP, any of which you can use on OS X. The issue of accessing microsoft access databases remains with any of these, but if you moved to a different DB it should be easy to connect and insert records from one of these.

-Lee
 
so bob5731, what have you done for yourself? it sounds like you expect someone to just give you the answer without you doing any work or understanding anything...

did you even read the thread lee pointed you to? are there parts on that that you don't quite understand that you want clarified?
 
At the top of this forum there is a link for getting started, that points you here:
http://guides.macrumors.com/Cocoa_FAQ

There are a number of helpful links for getting started. If you're just trying to replace the functionality of this script, though, an Automator workflow that incorporates the script I posted above should do it for you.

-Lee
 
No, probably not.

You need to:
1) Export your current data from access to CSV or a SQL dump, and load that into a new database on your mac. I think mysql ships with OS X, so that seems like a reasonable choice. Even if it doesn't, it should be trivial to get installed. I am only mentioning this because mysql is popular, and pretty easy to setup in my experience. You can download from here if needed:
http://dev.mysql.com/downloads/mysql/5.1.html#macosx-dmg

2) Decide if the app needs a GUI or not. If it does, you can probably do that piece in applescript. I'm not too familiar w/ applescript, so i don't know how well you can parse webpages with it, and I'm sure you can't easily make web service requests with it. My tact would be to accept the UPC via a dialog in applescript, then pass that down to a shell script like the one I posted above.

3) The shell script I posted above would need to be modified slightly to run the generated query to insert into the new mysql database. This shouldn't be too hard.

If you really want an Objective-C app with a full GUI, etc. and you are brand new to mac programming, this will be a much bigger effort. People here would be happy to help you, but they are unlikely to do it for you. If you are going the Objective-C route, you may want to try sqlite for your backing store instead of mysql.

-Lee
 
lee1210 said:
No, probably not.

You need to:
1) Export your current data from access to CSV or a SQL dump, and load that into a new database on your mac. I think mysql ships with OS X, so that seems like a reasonable choice. Even if it doesn't, it should be trivial to get installed. I am only mentioning this because mysql is popular, and pretty easy to setup in my experience. You can download from here if needed:
http://dev.mysql.com/downloads/mysql/5.1.html#macosx-dmg

2) Decide if the app needs a GUI or not. If it does, you can probably do that piece in applescript. I'm not too familiar w/ applescript, so i don't know how well you can parse webpages with it, and I'm sure you can't easily make web service requests with it. My tact would be to accept the UPC via a dialog in applescript, then pass that down to a shell script like the one I posted above.

3) The shell script I posted above would need to be modified slightly to run the generated query to insert into the new mysql database. This shouldn't be too hard.

If you really want an Objective-C app with a full GUI, etc. and you are brand new to mac programming, this will be a much bigger effort. People here would be happy to help you, but they are unlikely to do it for you. If you are going the Objective-C route, you may want to try sqlite for your backing store instead of mysql.

-Lee
Click to expand...
Actually, SQLite ships with Mac OS X, not MySQL. That must be downloaded separately.
 
wrldwzrd89 said:
Actually, SQLite ships with Mac OS X, not MySQL. That must be downloaded separately.
Click to expand...

I knew it was trivial to get mysql going, i just couldn't remember if it was already there or if i had to download it. It's probably just as well, for these purposes, to use sqlite even if the OP doesn't go for a full-blown Objective-C app. I hadn't thought of that. If there was already a sqlite database with a table called table:
Code: 
curl http://www.upcdatabase.com/item/$1| grep Description | awk -F'<[/]*td>' '{print "Insert into table (UPC,Description) values (\047"'$1'"\047,\047"$6"\047);"}' | sqlite3 dbname <
Would probably work... not at a mac to try it, but seems reasonable.

-Lee
 
I don't know that anyone asserted that it is your webpage. They seem pretty copasetic with people using it to pull UPC info, especially since they have a web service available to pull information, etc.

If you mean using sqlite or mysql, etc... that is to replace the Microsoft Access database that is storing data in a table called table locally. That seems to be, as far as I can tell, the whole point of the script. It has a UPC hard-coded, but i'd expect that you'd want to be able to enter a new one as desired. The script then takes that UPC, queries the upcdatabase.com info for a description of the UPC, then inserts a row into the access database's table called table with the UPC and said description. The suggestion was not to somehow use one of these systems to replace the upcdatabase.com data source.

Since there's no good way to manipulate an access database on OS X, I was suggesting replacing it with mysql or sqlite, as they will be more portable in the future.

-Lee
 
unfortunately, bob, this is the end of the line for me. I just read the thread you linked in your first message, which i failed to do before. In that thread you just goaded people into doing all of the work in VB for you a couple of years ago. You just pasted their work here and are trying to get people to write a mac version for you. This simply isn't going to happen. If you don't know how to program, and don't want to learn, but need something programmed then you need to hire someone. You can't do that here, so it is perhaps not the best place to be posting.

Good luck getting this done, but I would not expect people to do the work for you for fun.

-Lee
 
You must log in or register to reply here.
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.
Top Bottom
Back