Jump to content

_SQL.au3. ADODB.Connection


Recommended Posts

Hi I`m a bit confussed, my english is not so good, and so i am not sure if the Posts above solve my problem...

here is my tasl

i am working on a skript, which includes an sql query to an DSN, so far so good,

im using an odbc and i need a direct access, my db is an AS400 db and its called Ci98PROD

and my query is a kind of complicated^^

Querry:

(select vhistory, fo.fo_id from vhistory vh

inner join vinstanz vi on vi.vinstanz=vh.vinstanz

left outer join titel ti on ti.titel_nr=vi.ankervar1

left outer join forderun fo on fo.fo_nr=vi.ankervar1

where vh.vhistory ="value*")

union

(select vhistory, fo1.fo_id from vhistory vh

inner join vinstanz vi on vi.vinstanz=vh.vinstanz

left outer join titel ti on ti.titel_nr=vi.ankervar1

left outer join forderun fo1 on ti.leitford=fo1.fo_nr

where vh.vhistory ="vallue*")

* value that needs to be transformed

so my problem is, how can I get this in a script?

however, my programming skill is very low when it comes to such specific problems, can anybody help me out?

Link to post
Share on other sites
  • Replies 131
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

I searched around on here for some SQL stuff to use with an MSDE database and I ended up getting confused so I tried to simplfy it a bit, there are only a couple of functions so far but I guess someo

I got the solution. The problem for i am getting the error message is we have upgraded our SQL server to 2008 version.The Function _SQL_Connect() is not suppporting SQL2008. so we need to modify the f

dear @StefanoVR 1. please read: >How to post code on the forum 2. Function header should be before function declaration ; #FUNCTION# ======================================================

Posted Images

Hi Gardi,

post in support and I'll try to help you.

Mega

Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Link to post
Share on other sites

ChrisL: you have any good ideas on how to get recaffected, like one do with php?

I found a ADO reference over at w3schools (here). But where unable to make it work inn a script.

UDF:Crypter a file encrypt / decrypt tool with no need to remember a password again. Based on Caesar cipher using entire ASCII Table.Script's: PixelSearch Helper, quick and simple way to create a PixelSeach.Chatserver - simplified, not so complicated multi-socket server.AutoIT - Firewall, simple example on howto create a firewall with AutoIt.
Link to post
Share on other sites
  • 1 month later...

ChrisL: you have any good ideas on how to get recaffected, like one do with php?

I found a ADO reference over at w3schools (here). But where unable to make it work inn a script.

No I don't sorry, have you tried a post in the main support forum, someone there might be able to help

Link to post
Share on other sites
  • 6 months later...
  • 2 weeks later...

I just can't seem to get the example to work for me, I'm running win xp with SQL 2005 express. It won't connect to the database, errors out with the _sql_connect, I've tried changing the localhost to my computer's name. I'm not great at SQL but I know that it should be working

looks like on my system it's "computer name"\SQLEXPRESS

ok, now to get everything else working

-guy

Edited by stinson6016

Gnatwork Networks

Link to post
Share on other sites
  • 3 weeks later...

I just can't seem to get the example to work for me, I'm running win xp with SQL 2005 express. It won't connect to the database, errors out with the _sql_connect, I've tried changing the localhost to my computer's name. I'm not great at SQL but I know that it should be working

looks like on my system it's "computer name"\SQLEXPRESS

ok, now to get everything else working

-guy

It's been installed as an instance called SQLEXPRESS, you can install multiple instances of SQL

Link to post
Share on other sites
  • 2 weeks later...

so this wont work, if Ihave mysql 5.1 running?

My Projects:[list][*]Guide - ytube step by step tut for reading memory with autoitscript + samples[*]WinHide - tool to show hide windows, Skinned With GDI+[*]Virtualdub batch job list maker - Batch Process all files with same settings[*]Exp calc - Exp calculator for online games[*]Automated Microsoft SQL Server 2000 installer[*]Image sorter helper for IrfanView - 1 click opens img & move ur mouse to close opened img[/list]
Link to post
Share on other sites
  • 3 weeks later...

This function does not appear to work when you try and get information from a table. Each time I get the following message:

_sql.au3 (262) : ==> Error in expression.:

$hQuery = $hConHandle.Execute($vQuery)

$hQuery = ^ ERROR

I'm using AutoIt 3.3.0, I notice the fimction used AutoIt 3.2. Is this the cause of the problem?

Link to post
Share on other sites
  • 3 months later...

This function does not appear to work when you try and get information from a table. Each time I get the following message:

_sql.au3 (262) : ==> Error in expression.:

$hQuery = $hConHandle.Execute($vQuery)

$hQuery = ^ ERROR

I'm using AutoIt 3.3.0, I notice the fimction used AutoIt 3.2. Is this the cause of the problem?

Got the same problem here. using 3.3.0 and SQL 2008 Express.

Any idea how to fix it? :D

Link to post
Share on other sites
  • 2 weeks later...
  • 2 months later...

Thank you!! Thank you!! Thank you!! ;)

As a newb I had been struggling with getting ANY connection working. The _sql.au3 solved my problems (I am still working on why). I am testing with SQLExpress 2008 and did not change anything except the server/db name.

Link to post
Share on other sites
  • 2 months later...

Hi ChrisL:

Your Work is great, Help me to make my firs Program with SQL, and now is working fine, So now im working in another software but I need to kno How i can get only the data from a specific field only the exact information, without the table names :mellow: y try of many ways whit this

_SQL_GetTableAsString(-1,"SELECT Client,Credit FROM Gen_Clientes where Id=1;",$vString)

but always i get the table name and then, the data what i whant.

and i try this

_SQL_Execute(-1,"SELECT Client,Credit FROM Gen_Clientes where Id=1")

But dont show nothing

can you please helpme?

many thanx! :(

Link to post
Share on other sites
  • 2 months later...
  • 1 month later...

I have this error from SQL

[Microsoft][ODBC SQL Server Driver]Sessiontimeout

SQLManager runs query 2:13min but AutoIT don't wait this time.

Bit late but...

; #FUNCTION# ===================================================================
; Name ..........: _SQL_ConnectionTimeout
; Description ...: Sets and retrieves SQL ConnectionTimeout
; Syntax.........:  _SQL_ConnectionTimeout($ADODBHandle,$iTimeout)
; Parameters ....: $ADODBHandle - ADODB.Connection handle.
;                  $iTimeout   - The timeout period to set if left blank the current value will be retrieved
; Return values .: On Success - Returns Connection timeout period
;                  On Failure - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_ConnectionTimeout($ADODBHandle = -1,$iTimeOut = "")
    
    $SQLErr = ""
     If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $iTimeOut = "" then Return $ADODBHandle.ConnectionTimeout
    
    If NOT isInt($iTimeOut) then 
        $SQLErr = "ConnectionTimeOut value must be an integer"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $ADODBHandle.ConnectionTimeout = $iTimeOut
    Return $ADODBHandle.ConnectionTimeout
    
EndFunc   ;==>_SQL_Startup

; #FUNCTION# ===================================================================
; Name ..........: _SQL_CommandTimeout
; Description ...: Sets and retrieves SQL CommandTimeout
; Syntax.........:  _SQL_CommandTimeout($ADODBHandle,$iTimeout)
; Parameters ....: $ADODBHandle - ADODB.Connection handle.
;                  $iTimeout   - The timeout period to set if left blank the current value will be retrieved
; Return values .: On Success - Returns SQL Command timeout period
;                  On Failure - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_CommandTimeout($ADODBHandle = -1,$iTimeOut = "")
    
    $SQLErr = ""
     If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $iTimeOut = "" then Return $ADODBHandle.CommandTimeout
    
    If NOT isInt($iTimeOut) then 
        $SQLErr = "CommandTimeOut value must be an integer"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $ADODBHandle.CommandTimeout = $iTimeOut
    Return $ADODBHandle.CommandTimeout
    
EndFunc   ;==>_SQL_Startup
Link to post
Share on other sites
  • 2 weeks later...

I found a small bug in _SQL_ExcelConnect() which produces the error "Could not find installable ISAM." After some searching, I found the answer at http://www.connectionstrings.com/excel - the "Extended Properties" values in the connection string need to be quoted.

IE. Change the line "Extended Properties=Excel 8.0;HDR=" & $HDR & ";")

to "Extended Properties='Excel 8.0;HDR=" & $HDR & "';")

Thanks for the great UDF,

Carl

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...