Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Reply
 
Thread Tools Search this Thread Display Modes
Old May 25, 2009, 10:26 PM   #1
bob5731
macrumors member
 
Join Date: Jun 2008
vb 6.0 to AppleScript

Hi
I'm to AppleScript
Code is in vb 6.0
http://www.vbforums.com/showthread.p...ght=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

Last edited by kainjow; May 26, 2009 at 01:55 PM. Reason: please use code tags
bob5731 is offline   0 Reply With Quote
Old May 25, 2009, 10:57 PM   #2
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
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
lee1210 is offline   0 Reply With Quote
Old May 25, 2009, 11:06 PM   #3
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
No
It is on http://www.upcdatabase.com/item.pl
bob5731 is offline   0 Reply With Quote
Old May 26, 2009, 10:22 AM   #4
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
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
lee1210 is offline   0 Reply With Quote
Old May 26, 2009, 11:52 AM   #5
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
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 www.xml-rpc.net
// 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;
        }
    }
 
}

Last edited by kainjow; May 26, 2009 at 01:55 PM. Reason: please use code tags
bob5731 is offline   0 Reply With Quote
Old May 26, 2009, 11:55 AM   #6
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
http://www.upcdatabase.com/xmlrpc.asp
bob5731 is offline   0 Reply With Quote
Old May 26, 2009, 01:09 PM   #7
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
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
lee1210 is offline   0 Reply With Quote
Old May 26, 2009, 02:54 PM   #8
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
How do I do it in xcode?
bob5731 is offline   0 Reply With Quote
Old May 26, 2009, 03:03 PM   #9
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
If you mean with an Objective-C app, making web service requests is not as trivial as with some other languages and corresponding libraries. There was just a thread about this, here:
http://forums.macrumors.com/showthread.php?t=693307

-Lee
lee1210 is offline   0 Reply With Quote
Old May 26, 2009, 10:02 PM   #10
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
How do I do it in xcode?
bob5731 is offline   0 Reply With Quote
Old May 26, 2009, 10:31 PM   #11
Chirone
macrumors 6502
 
Join Date: Mar 2009
Location: NZ
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?
Chirone is offline   0 Reply With Quote
Old May 28, 2009, 12:20 AM   #12
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
I'm new to xcode how to I use xcode?
bob5731 is offline   0 Reply With Quote
Old May 28, 2009, 08:27 AM   #13
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
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
lee1210 is offline   0 Reply With Quote
Old May 28, 2009, 08:45 AM   #14
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
Can some one do it for me?
bob5731 is offline   0 Reply With Quote
Old May 28, 2009, 09:03 AM   #15
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
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...tml#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 is offline   0 Reply With Quote
Old May 28, 2009, 09:08 AM   #16
wrldwzrd89
macrumors G4
 
wrldwzrd89's Avatar
 
Join Date: Jun 2003
Location: Solon, OH
Quote:
Originally Posted by lee1210 View Post
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...tml#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
Actually, SQLite ships with Mac OS X, not MySQL. That must be downloaded separately.
__________________
iMac Intel (Rev H, 27"), 1TB HDD, 16GB RAM, 10.8.4
wrldwzrd89 is offline   0 Reply With Quote
Old May 28, 2009, 09:35 AM   #17
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
Quote:
Originally Posted by wrldwzrd89 View Post
Actually, SQLite ships with Mac OS X, not MySQL. That must be downloaded separately.
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
lee1210 is offline   0 Reply With Quote
Old May 28, 2009, 12:05 PM   #18
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
It is not my web page!!!
bob5731 is offline   0 Reply With Quote
Old May 28, 2009, 01:29 PM   #19
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
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
lee1210 is offline   0 Reply With Quote
Old May 28, 2009, 01:37 PM   #20
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
I have it working in vb 6.0. But vb 6.0 will not run on a mac.
bob5731 is offline   0 Reply With Quote
Old May 28, 2009, 11:27 PM   #21
bob5731
Thread Starter
macrumors member
 
Join Date: Jun 2008
The code vb 6.0 but need it in mac code?
bob5731 is offline   0 Reply With Quote
Old May 28, 2009, 11:42 PM   #22
lee1210
macrumors 68040
 
lee1210's Avatar
 
Join Date: Jan 2005
Location: Dallas, TX
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
lee1210 is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Programming > Mac Programming

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
thread Thread Starter Forum Replies Last Post
iChat encryption option missing in Lion/ iChat 6.0? orvn Mac OS X 10.7 Lion 0 Aug 11, 2011 11:43 PM
Drag and drop mac to mac in parallels or vmware? GrahamW0009 Mac Applications and Mac App Store 2 Jun 5, 2011 10:57 AM
10.6.4 to 10.6.6 MAIL problems loungecorps Mac Pro 3 Jan 9, 2011 11:34 AM
time machine causing osx 10.6.5 to crash Blunder82 OS X 0 Nov 23, 2010 10:33 AM
Help - Need Safari or Firefox to Appear to Server as IE 6.0 or 7.0 BAP Mac Applications and Mac App Store 1 Aug 26, 2009 12:23 AM


All times are GMT -5. The time now is 12:20 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC