XProfan

English

Uwe
''Pascal''
Niemeier

Hi people!

have time a little bit with the Possibility played, Images o.Ä. in Databases to take off. here The Results...

at that write langer Strings there apparently no Maximalgröße; several 100 KB are with Standartfunktionen no trouble, presupposed The db supported suitable Feldtypen:
Compile Mark Separation

****************** Demo: Beliebig lange Strings in DB schreiben
****************** Profan : 11.1 und höher
****************** Windows: XP SP3
****************** kein © (Standartfunktionen)
window 50,50-500,500
declare Exec$
--Access
var mdb$=X:ExperimenteSQL-APIAccessTest1.mdb
var Init$=Driver={Microsoft Access Driver (*.mdb)};Dbq=+mdb$+;Uid=;Pwd=;
--SQL Express
var Init$=Driver={SQL Server};Server=(local)SQLExpress;UID=sa;PWD=sql;
SQLInit(Init$)
Exec$=DROP TABLE TCS
SQLExec Exec$,0---Fehlermeldung, wenn db nicht vorhanden => Egal!
Exec$=CREATE TABLE TCS (Feld1 MEMO)---------Access > FeldTyp MEMO
Exec$=CREATE TABLE TCS (Feld1 VARCHAR(MAX))--SQLExpress > FeldTyp VARCHAR(MAX)
SQLExec Exec$,0--db anlegen
declare Text#
var File$=C:2TestBild2.jpg-----------------------Quellpfad bitte anpassen!
var Size&=filesize(File$)
dim Text#,Size&
assign #1,File$
openrw #1
blockread(#1,Text#,0,Size&)
closerw #1
var Text$=char$(Text#,0,Size&)
dispose Text#
Text$=encode64(Text$)
Exec$=INSERT INTO TCS (Feld1) VALUES (+Text$+)
SQLExec Exec$,0--keine Größenbegrenzung beim Schreiben!
SQLDone
waitkey

solely at reading the data go selbige trimmed, therefore here a SQL-API-Possibility to Auswertung:
Compile Mark Separation

****************** demonstration: discretionary long Strings from DB reading
****************** Profan : 11.1 and higher
****************** windows: XP SP3
****************** © by TCS (Uwe Pascal Niemeier) 022009
--Listbox-list becomes as Error-log uses
--and in the Fehlerfall automatically displayed.
--it becomes only the 1. area the 1. found Datensatzes ausgewertet!
--means SQL-request properly arrange!
window 50,50-500,500
 $H windows.ph
def SQLAllocHandle(3) !odbc32,SQLAllocHandle
def SQLError(8 ) !odbc32,SQLError
def SQLExecDirect(3) !odbc32,SQLExecDirect
def SQLGetData(6) !odbc32,SQLGetData
def SQLFetch(1) !odbc32,SQLFetch
def SQLFreeHandle(2) !odbc32,SQLFreeHandle

proc SQLErrorLog-----------------------------------------SQLErrorLog

    parameters From$
    declare Error$
    SQLError(&SQLEnv,&SQLDBC,stmt&,0,0,Error#,512,0)
    Error$=string $(Error#,0)
    case len(Error$):addstring From$+ : +Error$
    endproc-------------------------------------------------------------

    proc SQLGetDataEx---------------------------------------SQLGetDataEx

        parameters Exec$
        declare Data#,Data$,Size&,stmt&,Error#
        dim Error#,512
        clearlist
        SQLAllocHandle(3,&SQLDBC,addr(stmt&))--3=SQL_HANDLE_STMT
        SQLErrorLog(SQLAllocHandle)
        SQLExecDirect(stmt&,addr(Exec$),-3)-- -3=SQL_NTS
        SQLErrorLog(SQLExecDirect)
        dim Data#,1--Dummy-DIM
        SQLFetch(stmt&)
        SQLErrorLog(SQLFetch)
        SQLGetData(stmt&,1,99,Data#,0,addr(Size&))--99=SQL_C_DEFAULT
        --Fehlerabfrage would To small Puffer report (Dummy-appeal wg. Size&)

        if Size&>0

            dim Data#,Size&+1
            SQLGetData(stmt&,1,99,Data#,Size&+1,addr(Size&))--99=SQL_C_DEFAULT
            SQLErrorLog(SQLGetData)
            Data$=char$(Data#,0,Size&)

        endif

        SQLFreeHandle(3,stmt&)--3=SQL_HANDLE_STMT
        dispose Data#,Error#
        case %getcount>-1:listbox$(Error,2)
        return Data$
        endproc-------------------------------------------------------------
        ---Access
        var mdb$=X:ExperimenteSQL-APIAccessTest1.mdb
        var Init$=coachman={Microsoft Access coachman (*.mdb)};Dbq=+mdb$+;Uid=;Pwd=;
        --SQL Express
        var Init$=coachman={SQL Server};Server=(local)SQLExpress;UID=sa;PWD=sql;
        SQLInit(Init$)
        var Exec$=SELECT TOP 1 area1 FROM TCS
        var Text$=SQLGetDataEx(Exec$)
        Text$=decode64(Text$)
        declare Text#
        dim Text#,len(Text$)+1
        char Text#,0=Text$
        assign #1,C:2TestTest.jpg------------------------Zielpfad Please adjust!
        erase #1
        openrw #1
        blockwrite #1,Text#,0,len(Text$)
        closerw #1
        dispose Text#
        SQLDone

Getestet (How one sees) with Access-dbs (*.mdb) (Access must unavailable his) and MS SQL Express 2005.
whether The data with encode64() codiert go must, is möglicherweise of Feldtyp abhängig; with supra named dbs went not without. in the Zweifelsfall try.

Perhaps kanns someone need

SeeYou
Pascal

02/15/09 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

Thomas
Kallenberger

Hello Pascal,
The API sees well from - I ought to the whole solely inverse having - d.h. Data from of/ one Textdatei in a Access-scheduler bring. Perhaps has someone a suggestion! row for row reading is somehow not so prickelnd.

thanks for Your trouble!
love Regards
Thomas

03/30/10 ▲

Vollständiges Quote

Absatzweise Quote Zeilenweise Quote

Michael
W.

the whole again with quotation marks and Kommentarzeichen. The are somewhere verschollen...
Compile Mark Separation

'****************** Demo: Beliebig lange Strings in DB schreiben
'****************** Profan : 11.1 und höher
'****************** Windows: XP SP3
'****************** kein © (Standartfunktionen)
window 50,50-500,500
declare Exec$
'--Access
var mdb$="X:ExperimenteSQL-APIAccessTest1.mdb"
var Init$="Driver={Microsoft Access Driver (*.mdb)};Dbq=+mdb$+;Uid=;Pwd=;"
'--SQL Express
var Init$="Driver={SQL Server};Server=(local)SQLExpress;UID=sa;PWD=sql;"
SQLInit(Init$)
Exec$="DROP TABLE TCS"
SQLExec Exec$,0'---Fehlermeldung, wenn db nicht vorhanden => Egal!
Exec$="CREATE TABLE TCS (Feld1 MEMO)"'---------Access > FeldTyp MEMO
Exec$="CREATE TABLE TCS (Feld1 VARCHAR(MAX))"'--SQLExpress > FeldTyp VARCHAR(MAX)
SQLExec Exec$,0'--db anlegen
declare Text#
var File$="C:2TestBild2.jpg"'-----------------------Quellpfad bitte anpassen!
var Size&=filesize(File$)
dim Text#,Size&
assign #1,File$
openrw #1
blockread(#1,Text#,0,Size&)
closerw #1
var Text$=char$(Text#,0,Size&)
dispose Text#
Text$=encode64(Text$)
Exec$="INSERT INTO TCS (Feld1) VALUES (" + "Text$" + ")"
SQLExec Exec$,0'--keine Größenbegrenzung beim Schreiben!
SQLDone
waitkey

Compile Mark Separation

'****************** demonstration: discretionary long Strings from DB reading
'****************** Profan : 11.1 and higher
'****************** windows: XP SP3
'****************** © by TCS (Uwe Pascal Niemeier) 022009
'--Listbox-list becomes as error-log uses
'--and in the Fehlerfall automatically displayed.
'--it'll only the 1. area the 1. found Datensatzes ausgewertet!
'--means SQL-request properly arrange!
window 50,50-500,500
 $H windows.ph
def SQLAllocHandle(3) !odbc32,SQLAllocHandle
def SQLError(8 ) !odbc32,SQLError
def SQLExecDirect(3) !odbc32,SQLExecDirect
def SQLGetData(6) !odbc32,SQLGetData
def SQLFetch(1) !odbc32,SQLFetch
def SQLFreeHandle(2) !odbc32,SQLFreeHandle

proc SQLErrorLog'-----------------------------------------SQLErrorLog

    parameters From$
    declare Error$
    SQLError(&SQLEnv,&SQLDBC,stmt&,0,0,Error#,512,0)
    Error$=string $(Error#,0)
    case len(Error$) : addstring From$+" : "+Error$

endproc'-------------------------------------------------------------

proc SQLGetDataEx'---------------------------------------SQLGetDataEx

    parameters Exec$
    declare Data#,Data$,Size&,stmt&,Error#
    dim Error#,512
    clearlist
    SQLAllocHandle(3,&SQLDBC,addr(stmt&))'--3=SQL_HANDLE_STMT
    SQLErrorLog("SQLAllocHandle")
    SQLExecDirect(stmt&,addr(Exec$),-3)'-- -3=SQL_NTS
    SQLErrorLog("SQLExecDirect")
    dim Data#,1'--Dummy-DIM
    SQLFetch(stmt&)
    SQLErrorLog("SQLFetch")
    SQLGetData(stmt&,1,99,Data#,0,addr(Size&))'--99=SQL_C_DEFAULT
    '--Fehlerabfrage would To small Puffer report (Dummy-appeal wg. Size&)

    if Size& > 0

        dim Data#,Size&+1
        SQLGetData(stmt&,1,99,Data#,Size&+1,addr(Size&))'--99=SQL_C_DEFAULT
        SQLErrorLog("SQLGetData")
        Data$=char$(Data#,0,Size&)

    endif

    SQLFreeHandle(3,stmt&)'--3=SQL_HANDLE_STMT
    dispose Data#,Error#
    case %getcount > -1 : listbox$(Error,2)
    return Data$

endproc'-------------------------------------------------------------

'---Access
var mdb$="X:ExperimenteSQL-APIAccessTest1.mdb"
var Init$="Driver={Microsoft Access coachman (*.mdb)};Dbq=+mdb$+;Uid=;Pwd=;"
'--SQL Express
var Init$="Driver={SQL Server};Server=(local)SQLExpress;UID=sa;PWD=sql;"
SQLInit(Init$)
var Exec$="SELECT TOP 1 area1 FROM TCS"
var Text$=SQLGetDataEx(Exec$)
Text$=decode64(Text$)
declare Text#
dim Text#,len(Text$)+1
char Text#,0 = Text$
assign #1,"C:2TestTest.jpg"'------------------------Zielpfad Please adjust!
erase #1
openrw #1
blockwrite #1,Text#,0,len(Text$)
closerw #1
dispose Text#
SQLDone