 |
AutoHotkey Community
Let's help each other out
|
| View previous topic :: View next topic |
| Author |
Message |
Sean
Joined: 12 Feb 2007
Posts: 2462
|
 Posted: Tue Jul 24, 2007 2:52 am Post subject: |
 |
|
| tfcahm wrote: | | Code: | Invoke_(pmstream, "Write", 12,Invoke(Invoke(Invoke(prs, "Fields"), "Item", "logo"), "Value"))
\_ 0/8 \_ 8209/8 |
|
8209 = 0x2011 is VT_ARRAY | VT_UI1, i.e., an array of bytes.
So, try after replacing 12 with 8209.
If it still doesn't work, I'd like to know what value was returned for Invoke(Invoke(Invoke(prs, "Fields"), "Item", "logo"), "Value"). |
|
| Back to top |
|
 |
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Tue Jul 24, 2007 3:00 am Post subject: |
|
|
Oh, I see. But, actually I succeeded to use VT_I8 and VT_UI8 in the variant, specifically with Excel sheet. Excel correctly converted the data as 1.3E+18, etc.
| Quote: | I had one example of a property returning a value that wouldn't fit in I4:
| Code: | Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oDrive = oFSO.GetDrive("C:")
oDrive.FreeSpace |
The FreeSpace property was returning a VT_R8 on my computer that had dozens of GB of free space. |
VT_R4 and VT_R8 would not be a problem, both as a parameter and as a result. They can be type-coerced from/to BString.
| Code: | #Include CoHelper.ahk
CoInitialize()
pfso := ActiveXObject("Scripting.FileSystemObject")
pdrv := Invoke(pfso, "GetDrive", "C:")
MsgBox, % Invoke(pdrv, "FreeSpace")
Release(pdrv)
Release(pfso)
CoUninitialize()
|
So, the simplest solution when need to specify an integer greater than 2**32 may be just using a floating number (string), like 12345678900.0 etc. |
|
| Back to top |
|
|
tfcahm
Joined: 20 May 2007
Posts: 48
|
| Posted: Tue Jul 24, 2007 4:13 am Post subject: |
|
|
| Sean wrote: | 8209 = 0x2011 is VT_ARRAY | VT_UI1, i.e., an array of bytes.
So, try after replacing 12 with 8209. |
An array of bytes certainly makes sense, but 8209 gave the same result.
| Quote: | | If it still doesn't work, I'd like to know what value was returned for Invoke(Invoke(Invoke(prs, "Fields"), "Item", "logo"), "Value"). |
There is binary in the database field "logo". Msgbox shows this (with some new lines added to keep it from being excessively wide) | Code: | ????????‰‰‰‰??????‹'‰‰'‰????????????????????????????????A????????????????????????d??????
?A?????????????????????????????????y??????????????????????????c????????A????????????????????????
??e?????????? ????????°?????????????????????????????A?????????????????????????????????????????
?????e????????4????????????????????????????7?????? |
A couple other data points: I tried changing the stream object type to adTypeText and to write the stream object with the value from a text field. | Code: | pmstream := ActiveXObject("ADODB.STREAM")
Invoke(pmstream, "Type=", adTypeText)
Invoke(pmstream, "Open")
Invoke_(pmstream, "Write", 8209,Invoke(Invoke(Invoke(prs, "Fields"), "Item", "caption"), "Value")) ;doesn't work
Invoke(pmstream, "SavetoFile", "c:\temp\pms.txt", adSaveCreateOverWrite) |
Invoke(Invoke(Invoke(prs, "Fields"), "Item", "Caption"), "Value") returns the proper text string ("Test string" in this case) but the stream object remains empty.
Next I tried using this line | Code: | | Invoke_(pmstream, "WriteText", 8,"Test string") |
and the stream object and disk file are properly written with "Test string". |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Tue Jul 24, 2007 4:30 am Post subject: |
|
|
| tfcahm wrote: | | Sean wrote: | 8209 = 0x2011 is VT_ARRAY | VT_UI1, i.e., an array of bytes.
So, try after replacing 12 with 8209. |
An array of bytes certainly makes sense, but 8209 gave the same result. |
The problem is that the type change into bstring was really occured.
Then, the Invoke() tried to convert it to the ANSI string, so all garbages was returned as a result...
I didn't expect that the OLE would really type-coerce an array into a bstring.
As a matter of fact, the fear about these unexpected outcomes prevented me from doing any user-friendly refinement into Invoke(), which I'm doing currently.
As a temporary workaround, replace the following part in varResult
| Code: | | NumGet(varResult,0,"Ushort")=8||NumGet(varResult,0,"Ushort")<>9&& |
with
| Code: | | NumGet(varResult,0,"Ushort")=8||NumGet(varResult,0,"Ushort")<>9&&NumGet(varResult,0,"Ushort")<0x1000&& |
|
|
| Back to top |
|
|
tfcahm
Joined: 20 May 2007
Posts: 48
|
| Posted: Tue Jul 24, 2007 5:20 am Post subject: |
|
|
| Quote: | As a temporary workaround, replace the following part in varResult
| Code: | NumGet(varResult,0,"Ushort")=8||NumGet(varResult,0,"Ushort")<>9&&
|
with
| Code: | | NumGet(varResult,0,"Ushort")=8||NumGet(varResult,0,"Ushort")<>9&&NumGet(varResult,0,"Ushort")<0x1000&& |
|
That fixed it! Thanks again Sean for all the help. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Tue Jul 24, 2007 8:00 am Post subject: |
|
|
| tfcahm wrote: | | That fixed it! |
Good to know, and don't forget to release the array after finished:
| Code: | | DllCall("oleaut32\SafeArrayDestroy", "Uint", pSafeArray) |
|
|
| Back to top |
|
|
tfcahm
Joined: 20 May 2007
Posts: 48
|
| Posted: Tue Jul 24, 2007 10:06 pm Post subject: |
|
|
| Sean wrote: | Good to know, and don't forget to release the array after finished:
|
Thanks for that.
Should Release() be called on every type IDispatch? For example: | Code: | ...
Invoke_(pmstream, "Write", 8209,pSafearray:=Invoke(pfld:=Invoke(pflds:=Invoke(prs, "Fields"), "Item", "logo"), "Value"))
...
Release(pmstream)
DllCall("oleaut32\SafeArrayDestroy", "Uint", pSafeArray)
Release(pfld)
Release(pflds)
Release(prs) |
I'll post the scripts to read and write binary objects between disk and database shortly. They work great and are good examples, just want to be sure there are no subtle memory issues. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Wed Jul 25, 2007 12:58 am Post subject: |
|
|
| tfcahm wrote: | | Should Release() be called on every type IDispatch? |
If the script is running for quite a while and creating constantly new objects, it may be the most resource-friendly way.
Otherwise, however, just CoUninitialize() at the exit of the app will be enough, I suppose.
| Quote: | | I'll post the scripts to read and write binary objects between disk and database shortly. They work great and are good examples, just want to be sure there are no subtle memory issues. |
You seem to be an expert on the database manipulation.
Eagerly waiting and thanks for sharing them. |
|
| Back to top |
|
|
tfcahm
Joined: 20 May 2007
Posts: 48
|
| Posted: Wed Jul 25, 2007 8:25 am Post subject: |
|
|
Sorry for the delay in posting the promised script. I ran into a problem with the most recent CoHelper.ahk (edit 33). The change from "Int64" to "Uchar" to this statement of Invoke_() | Code: | | NumPut(type%A_Index%,varg,16*(nParams-A_Index),"Uchar") |
causes this line to fail: | Code: | | Invoke_(pmstream, "Write", 8209,pSafearray) |
Should I adjust the script somehow, or is the behaviour not what you intended? The example database script works fine with that change reversed, but to avoid confusion I'll wait to post it until everything works together.
| Sean wrote: | If the script is running for quite a while and creating constantly new objects, it may be the most resource-friendly way.
Otherwise, however, just CoUninitialize() at the exit of the app will be enough, I suppose. |
I'll take the conservative approach. | Quote: | You seem to be an expert on the database manipulation.
Eagerly waiting and thanks for sharing them. |
Hardly an expert, but I have written several database applications. I had been waiting for an ODBC solution in AHK. While that would still be nice, it is no longer necessary. Your CoHelper provides an excellent solution. |
|
| Back to top |
|
|
Sean
Joined: 12 Feb 2007
Posts: 2462
|
| Posted: Wed Jul 25, 2007 10:13 am Post subject: |
|
|
| tfcahm wrote: | | I ran into a problem with the most recent CoHelper.ahk (edit 33). The change from "Int64" to "Uchar" to this statement of Invoke_() |
Oops, why Uchar was entered there... It should've been Ushort.
Sorry for the inconvenience, and I updated it. |
|
| Back to top |
|
|
tfcahm
Joined: 20 May 2007
Posts: 48
|
| Posted: Wed Jul 25, 2007 3:43 pm Post subject: |
|
|
Here's an example script that uses ADO COM to do some Access database manipulations. The script by itself does not do anything particularly useful, but it does demonstrate several basic operations. Before running the script you may need to adjust the three filename values at the top to match your environment. The script requires CoHelper.
| Code: | /*
Sample script using ADO COM that will create an Access database, create a
table in the database, populate the table, read binary data from a
file into a database field, and then write a file with binary data from
a database field. Error checking has been ommitted.
*/
#NoEnv
#SingleInstance
#Include CoHelper.ahk
CoInitialize()
;Change these values for your specific environment. Only file required to
;exist at the start is sFileIn. The others are created if they don't exist.
sFileIn = c:\windows\system32\setup.bmp ;can use any file as input
sFileOut = %A_Temp%\test_out.bmp ;output file, will be overwritten if exists
sDbFile = %A_Temp%\test_db.mdb ;database to be created, deleted if exists at start
;Some useful constants
Jet3x:=4, Jet4x:=5
adOpenKeyset:=1, adLockOptimistic:=3, adTypeBinary:=1
adSaveCreateOverWrite:=2, adInteger:=3, adKeyPrimary:=1
VT_I4:=0x3, VT_BSTR:=0x8, VT_DISPATCH:=0x9, VT_UI1:=0x11, VT_ARRAY:=0x2000
;Delete the database if it exists so we start fresh each time
IfExist, %sDbFile%
FileDelete, %sDbFile%
;::::::::::::::::::::::::::::::::::::
; Create a new database
;::::::::::::::::::::::::::::::::::::
;Define the connection string
sConn := "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Engine Type=" . Jet4x . "; Data Source=" . sDbFile . ";"
;Create a new database using ADOX.Catalog
pcat := ActiveXObject("ADOX.Catalog")
pDbase := Invoke(pcat, "Create", sConn)
Release(pDbase)
Release(pcat)
;Create a new Connection object and open a connection to the database
pcn := ActiveXObject("ADODB.CONNECTION")
Invoke(pcn, "Open", sConn)
;Create a table in the new database
sCreate := "CREATE TABLE Photos(PhotoId INTEGER NOT NULL
,Title TEXT(40) NOT NULL
,Location TEXT(40)
,Photo_1 OLEOBJECT)"
Invoke(pcn, "Execute", sCreate)
;Store some values into the table
Invoke(pcn, "Execute", "INSERT INTO Photos VALUES (1,'Grand Canyon', 'Arizona','')")
;At this point if we were finished we would close the connection
;and clean things up, but instead we will reuse the connection
;to open the recordset below.
;::::::::::::::::::::::::::::::::::::::::::::::::::
; Read binary data from a file into the database
;::::::::::::::::::::::::::::::::::::::::::::::::::
;Create a recordset object and open it with a query the database
prs := ActiveXObject("ADODB.RECORDSET")
Invoke_(prs, "Open", VT_BSTR,"SELECT * FROM Photos WHERE PhotoID=1;"
, VT_DISPATCH,pcn
, VT_I4,adOpenKeyset
, VT_I4,adLockOptimistic)
;Create a binary stream object to hold the data
pmstream := ActiveXObject("ADODB.STREAM")
Invoke(pmstream, "Type=", adTypeBinary)
Invoke(pmstream, "Open")
;Read the disk file into the stream object
Invoke(pmstream, "LoadFromFile", sFileIn)
pSafearray:=Invoke(pmstream, "Read")
;Set the value of the Photo_1 field to the binary data
pFields := Invoke(prs, "Fields")
pField := Invoke(pFields, "Item", "Photo_1")
Invoke_(pField, "Value=", VT_ARRAY|VT_UI1,pSafeArray)
Invoke(prs, "Update")
;Cleanup, conservative - release every IDispatch
DllCall("oleaut32\SafeArrayDestroy", "Uint", pSafeArray)
Release(pField)
Release(pFields)
Invoke(pcn, "Close")
Invoke(prs, "Close")
Release(pmstream)
Release(prs)
Release(pcn)
;::::::::::::::::::::::::::::::::::::::::::::::::::
; Write binary data from the database into a file
;::::::::::::::::::::::::::::::::::::::::::::::::::
;We could have reused the connection, recordset, and stream objects
;from above, but for this example we will create new objects.
;Create and open a connection
pcn := ActiveXObject("ADODB.CONNECTION")
Invoke(pcn, "Open", sConn)
;Create and open the recordset with a query
prs := ActiveXObject("ADODB.RECORDSET")
Invoke_(prs, "Open", VT_BSTR,"SELECT * FROM Photos WHERE PhotoID=1;"
, VT_DISPATCH,pcn
, VT_I4,adOpenKeyset
, VT_I4,adLockOptimistic)
;Create and open a binary stream
pmstream := ActiveXObject("ADODB.STREAM")
Invoke(pmstream, "Type=", adTypeBinary)
Invoke(pmstream, "Open")
;Write the value of the Photo_1 field to the stream object and then save it to a file with overwrite
pFields := Invoke(prs, "Fields")
pField := Invoke(pFields, "Item", "Photo_1")
pSafearray:=Invoke(pField, "Value")
Invoke_(pmstream, "Write", VT_ARRAY|VT_UI1,pSafearray)
Invoke(pmstream, "SavetoFile", sFileOut, adSaveCreateOverWrite)
;Cleanup, conservative - release every IDispatch
DllCall("oleaut32\SafeArrayDestroy", "Uint", pSafeArray)
Release(pField)
Release(pFields)
Invoke(pcn, "Close")
Invoke(prs, "Close")
Release(pmstream)
Release(prs)
Release(pcn)
CoUninitialize()
Msgbox, Done!
ExitApp
|
|
|
| Back to top |
|
|
ABCYourWay
Guest
|
| Posted: Wed Jul 25, 2007 6:21 pm Post subject: |
|
|
Thanks for the script.
I like th part of writing/reading binary files to DB.
I remeber there is a way of compressing MDB(access file).
Do you know what it is(maybe method name)?
And I take it that you are very active about COM related subject, so can you recommend or list free activeXs and the websites where they are? |
|
| Back to top |
|
|
tfcahm
Joined: 20 May 2007
Posts: 48
|
| Posted: Wed Jul 25, 2007 11:13 pm Post subject: |
|
|
| ABCYourWay wrote: | Thanks for the script.
I like th part of writing/reading binary files to DB. |
Glad you found it useful.
| ABCYourWay wrote: | I remeber there is a way of compressing MDB(access file).
Do you know what it is(maybe method name)? |
Please refer to http://support.microsoft.com/kb/230501 | Code: | #Include CoHelper.ahk
CoInitialize()
jro := ActiveXObject("JRO.JETENGINE")
Invoke(jro, "Compact"
, "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Engine Type=5; Data Source=<SOURCE MDB>;"
, "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Engine Type=5; Data Source=<TARGET.MDB>;")
Release(jro)
CoUninitialize() |
| ABCYourWay wrote: | | And I take it that you are very active about COM related subject, so can you recommend or list free activeXs and the websites where they are? |
Most apps come with the necessary components already (if they support COM). If the app you are interested in does not have COM you could try searching the web to see if a plug-in or something exists for it. |
|
| Back to top |
|
|
ABCyourWay
Guest
|
| Posted: Thu Jul 26, 2007 8:43 am Post subject: |
|
|
| Thanks tfcahm! I love Autohotkey community! |
|
| Back to top |
|
|
daonlyfreez
Joined: 16 Mar 2005
Posts: 949
Location: Berlin
|
| Posted: Tue Aug 07, 2007 3:23 pm Post subject: |
|
|
Can anybody create a simple script that demonstrates how to get Excel contents, a single cell, or a range? A variation of this example script?
_________________
 mirror 1 • mirror 2 • mirror 3 • ahk4.me • PM or  |
|
| Back to top |
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
