SQL Query with joined tables

[Klassieker]

Hello to you all,

I wonder, if there's anyone who can give me answer on the question if a SQL query with joined tables does work with adodb and the "open" command

 

Underneath will work

Local $oRS
Local $oConn

$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=c:filesdata.mdb ;pwd="")
$SQLt_Orders= "SELECT t_Orders.n_OrderNum, t_Orders.OrderDate, t_Orders.n_OrderStatus, t_Orders.n_TotalPrice, t_Orders.n_OrderPriceNet FROM t_Orders"

But if i'm joining fields from another table, I cant get it working in autoit, the sql statement is working properly in access.

Do I need to open all joined tables or do I need to simplify my SQL statement and join tables in auto IT?

 

Many thanks in advance

Klassieker

 

[JLogan3o13]

Hi, Klassieker. Something you might try - I recently ran into a similar problem (in my case a very, very long sql statement) and ended up trying something like this as a workaround, just for testing. I was connecting to Oracle, but the gist of it should be the same:

Local $aQuery
    _FileReadToArray(@DesktopDir & "\Payroll.sql", $aQuery)
    $sString = _ArrayToString($aQuery, " ")

$sCallersConnectionString = "Provider=MSDAORA.1;User ID=jeremiahlx;Password=Change123;Data Source=EDWPROD;Persist Security Info=False"
$sCallersSQLStatement = StringTrimLeft($sString, 3)

Basically, it reads the sql file into an array, parses to a string, and then calls that string for the Statement. It's a bit of a dirty hack, but it will at least tell you if you're getting the syntax wrong when typing it into the script, or if there is something else going on.

Edited July 18, 2013 by JLogan3o13

[GreenCan]

Hello to you all,

I wonder, if there's anyone who can give me answer on the question if a SQL query with joined tables does work with adodb and the "open" command

Klassieker,

I confirm that you can join tables. That works fine.

I use a slightly different DSN-less connection string using (MSAccess 2010) but that shouldn't be an issue

DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c:filesdata.md;uid=;pwd=;

I always use

    $oRS.CursorType = 2

    $oRS.LockType = 3

$adoRs.Open($SQLt_Orders, $oConn)

and I am using this SQL on my CATRaxx mdb database, works perfectly well

SELECT TOP 100
    Track.ArtistSort AS "Artist",
    Track.Title AS "Track Title",
    (INT(Track.Length/60) + round(((Track.Length/60) - INT(Track.Length/60))*0.60,2)) AS "Length",
    (SELECT PersonalRating.SortBy FROM PersonalRating WHERE Track.PersonalRatingID = PersonalRating.PersonalRatingID) AS "Track Rating", 
    (SELECT PersonalRating.PersonalRating FROM PersonalRating WHERE Track.PersonalRatingID = PersonalRating.PersonalRatingID) AS "Rating", 
    Album.Title AS "Album Title",
    (SELECT PersonalRating.SortBy FROM PersonalRating WHERE Album.PersonalRatingID = PersonalRating.PersonalRatingID) AS "Album Rating", 
    Track.Index AS "Track Nr",
    Track.Released AS "Track Released", 
    LEFT(Track.LastPlayed, 19) AS "Last Played",
    IIF(Track.Instrumental = -1,'Yes','')  AS "Instrumental",
    Track.Notes_PlainText AS "Notes"    
FROM
    Album 
        INNER JOIN Track ON Album.AlbumID = Track.AlbumID
     
WHERE 
    Track.LastPlayed<>null
ORDER BY 
    Track.LastPlayed DESC

As you didn't publish the complete script, it's hard to say what goes wrong, but if you fire two consecutive SQL's you have to open 2 connections, something like this (this is a SQL to a CSV file)

expandcollapse popup

Func FireSQL($connection, $search)
    Local $sResult = "", $adoCon, $adoRs, $adoCon2, $adoRs2, $adoSQL, $iRow
    $adoCon = ObjCreate("ADODB.Connection")
    $adoCon.Open($connection)
    If @error Then Exit MsgBox(48, "FireSQL Error", "Connection error: " & @error)
    $adoRs = ObjCreate("ADODB.Recordset")
    ; count rows
    $adoSQL = "SELECT count(*) as Count FROM CrewPhone.csv"

    $adoRs.CursorType = 2
    $adoRs.LockType = 3
    $adoRs.Open($adoSQL, $adoCon)

    With $adoRs
        If .RecordCount Then
            $sResult = $sResult & "" & .Fields("Count").Value & @CR
        EndIf
    EndWith
    ;ConsoleWrite(@ScriptLineNumber & " " & $sResult & @CR)
    Local $aResult[$sResult + 1][5]
    $aResult[0][0] = $sResult
    $adoCon2 = ObjCreate("ADODB.Connection")
    $adoCon2.Open($connection)
    If @error Then Exit MsgBox(48, "FireSQL Error", "Connection error " & @error)
    $adoRs2 = ObjCreate("ADODB.Recordset")

    If StringLen($search) > 0 Then
        $adoSQL = "SELECT * FROM CrewPhone.csv where instr(`FullName`,'" & $search & "')>0"
    Else
        $adoSQL = "SELECT * FROM CrewPhone.csv"
    EndIf

    $adoRs2.CursorType = 2
    $adoRs2.LockType = 3
    $adoRs2.Open($adoSQL, $adoCon2)
    $iRow = 0
    With $adoRs2
        If .RecordCount Then
            While Not .EOF
                $iRow += 1
                $aResult[$iRow][0] = .Fields("FullName").Value
                $aResult[$iRow][1] = .Fields("MobileTelephoneNumber").Value
                $aResult[$iRow][2] = .Fields("BusinessTelephoneNumber").Value
                $aResult[$iRow][3] = .Fields("Email1Address").Value
                $aResult[$iRow][4] = .Fields("Email2Address").Value
                .MoveNext
            WEnd
        EndIf
    EndWith

    $adoCon.Close
    $adoCon2.Close  
    Return $aResult
EndFunc   ;==>FireSQL

[Klassieker]

Sorry, for not responding to the suggestions.

I lost my complete include folder and also my testenvironment. Don't know why, but I cant find it back.

Because of the testing, I didn't backup my folders.

Shame on me.

I wil come back with the string I've  made in Acces (old version) to use it in Autoit.