Hello,
I use SQLEXPRESS and need a sample how to append or insert binary data 
in a table. Actually I transfer the data with a server side cursor, but 
because of communction problems sometimes I loose the cursor. So I would 
like to chante to SQL statements for insert and append.
I have images and xml files where some of them are stored in a dbf table.
The structure of the SQL table is:
ID             INT(10) NOT NULL DEFAULT 0,
xml            TEXT      DEFAULT NULL,
datum          DATE      DEFAULT NULL,
image          MEDIUMBLOB DEFAULT NULL,
pdf1           MEDIUMBLOB DEFAULT NULL,

Field pdf1 is a file, the other fields are from a dbf table:
ID	N 	10
XML     M        8
DATUM   D        8
IMAGE   M        8   JPEG

Has anybody a sample for a SQL statment tha can handel this, especially 
the BLOB fields ?

regards
Rudolf

---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

Hi Rudolf.
I use SQLExpress and maybe with this:

Local oStmt, i, oCursor
Local cConn := "DBQ=data\test.xls;Driver={Microsoft Excel Driver 
(*.xls)};ReadOnly=0;MaxScanRows=0;FirstRowHasNames=1"
Local oConn := SQLConnection():new()

IF !oConn:driverConnect(nil, cConn)
   MsgBox("Unable to connect to server!")
   QUIT

   else

    oStmt := oConn:NewStatement()
    oStmt:DisplayErrors := .T.

    oStmt:SQLString := 'INSERT INTO MySheet (Name, Amount) VALUES (?,?)'
    for i := 1 to 6
       oStmt:Execute( Replicate(Chr(64+i),10), i)
    next
    oStmt:destroy()

ENDIF

If not sure that Boris can help you.

Can you send me one message to my personal adress for see one think about 
this?

Regards.
César.



"Rudolf Reinthaler" escribió en el mensaje de 
noticias:45d8e026$703555f6$42b04@news.alaska-software.com...

Hello,
I use SQLEXPRESS and need a sample how to append or insert binary data
in a table. Actually I transfer the data with a server side cursor, but
because of communction problems sometimes I loose the cursor. So I would
like to chante to SQL statements for insert and append.
I have images and xml files where some of them are stored in a dbf table.
The structure of the SQL table is:
ID             INT(10) NOT NULL DEFAULT 0,
xml            TEXT      DEFAULT NULL,
datum          DATE      DEFAULT NULL,
image          MEDIUMBLOB DEFAULT NULL,
pdf1           MEDIUMBLOB DEFAULT NULL,

Field pdf1 is a file, the other fields are from a dbf table:
ID N 10
XML     M        8
DATUM   D        8
IMAGE   M        8   JPEG

Has anybody a sample for a SQL statment tha can handel this, especially
the BLOB fields ?

regards
Rudolf

---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus 
Schutz ist aktiv.
http://www.avast.com

For BLOB via SqlExpress it is done this way:

#include "sql.ch"
#include "sqlext.ch"

function dosomestuff()
   local cBlob := ""
   local oNew, oCol
   local nlen := 0
   LOCAL nId := 1000


cBlob := MemoRead("somefile.rtf")   //if non-text type file then use 
fopen/fread/fclose!

oConnect := SQlConnection(INSERT_PARAMETERS_HERE_DEPENDS_ON_SYS_AND_PROGRAM)

//get a blank statement
oNew := oConnect:NewStatement()

//basic string
oNew:SQLString := "INSERT INTO gcbin (doid,dado) VALUES 
("+Ltrim(Str(nId,11,0))+",?)"
nLen := len(cBlob)

//additional value object with blob data
oCol := SQLValue():new(cBlob, SQL_LONGVARBINARY, SQL_C_BINARY,nLen, 0, nLen)

//execute statement with
oNew:Execute({cBlob, oCol})
oNew:Destroy()


/*
mysql> show columns from gcbin;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| doid  | int(11)    | YES  |     | NULL    |       |
| dado  | mediumblob | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)



*/

RETURN .T.  //or whatever needed






On 20.10.2014 15:01, "Rudolf Reinthaler" wrote:
> Hello,
> I use SQLEXPRESS and need a sample how to append or insert binary data
> in a table. Actually I transfer the data with a server side cursor, but
> because of communction problems sometimes I loose the cursor. So I would
> like to chante to SQL statements for insert and append.
> I have images and xml files where some of them are stored in a dbf table.
> The structure of the SQL table is:
> ID             INT(10) NOT NULL DEFAULT 0,
> xml            TEXT      DEFAULT NULL,
> datum          DATE      DEFAULT NULL,
> image          MEDIUMBLOB DEFAULT NULL,
> pdf1           MEDIUMBLOB DEFAULT NULL,
>
> Field pdf1 is a file, the other fields are from a dbf table:
> ID    N     10
> XML     M        8
> DATUM   D        8
> IMAGE   M        8   JPEG
>
> Has anybody a sample for a SQL statment tha can handel this, especially
> the BLOB fields ?
>
> regards
> Rudolf
>
> ---
> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
> Schutz ist aktiv.
> http://www.avast.com
>

Hello,
thank you all, the example with SQLValue() looks interesting, will try 
to add more than one Blob fields. I think the Array for parameters
How can I add the UTF-8 encoded xml to the textfield ?
regards
Rudolf



Am 20.10.2014 15:51, schrieb Matej Jurac:
> For BLOB via SqlExpress it is done this way:
>
> #include "sql.ch"
> #include "sqlext.ch"
>
> function dosomestuff()
>    local cBlob := ""
>    local oNew, oCol
>    local nlen := 0
>    LOCAL nId := 1000
>
>
> cBlob := MemoRead("somefile.rtf")   //if non-text type file then use
> fopen/fread/fclose!
>
> oConnect :=
> SQlConnection(INSERT_PARAMETERS_HERE_DEPENDS_ON_SYS_AND_PROGRAM)
>
> //get a blank statement
> oNew := oConnect:NewStatement()
>
> //basic string
> oNew:SQLString := "INSERT INTO gcbin (doid,dado) VALUES
> ("+Ltrim(Str(nId,11,0))+",?)"
> nLen := len(cBlob)
>
> //additional value object with blob data
> oCol := SQLValue():new(cBlob, SQL_LONGVARBINARY, SQL_C_BINARY,nLen, 0,
> nLen)
>
> //execute statement with
> oNew:Execute({cBlob, oCol})
> oNew:Destroy()
>
>
> /*
> mysql> show columns from gcbin;
> +-------+------------+------+-----+---------+-------+
> | Field | Type       | Null | Key | Default | Extra |
> +-------+------------+------+-----+---------+-------+
> | doid  | int(11)    | YES  |     | NULL    |       |
> | dado  | mediumblob | YES  |     | NULL    |       |
> +-------+------------+------+-----+---------+-------+
> 2 rows in set (0.00 sec)
>
>
>
> */
>
> RETURN .T.  //or whatever needed
>
>
>
>
>
>
> On 20.10.2014 15:01, "Rudolf Reinthaler" wrote:
>> Hello,
>> I use SQLEXPRESS and need a sample how to append or insert binary data
>> in a table. Actually I transfer the data with a server side cursor, but
>> because of communction problems sometimes I loose the cursor. So I would
>> like to chante to SQL statements for insert and append.
>> I have images and xml files where some of them are stored in a dbf table.
>> The structure of the SQL table is:
>> ID             INT(10) NOT NULL DEFAULT 0,
>> xml            TEXT      DEFAULT NULL,
>> datum          DATE      DEFAULT NULL,
>> image          MEDIUMBLOB DEFAULT NULL,
>> pdf1           MEDIUMBLOB DEFAULT NULL,
>>
>> Field pdf1 is a file, the other fields are from a dbf table:
>> ID    N     10
>> XML     M        8
>> DATUM   D        8
>> IMAGE   M        8   JPEG
>>
>> Has anybody a sample for a SQL statment tha can handel this, especially
>> the BLOB fields ?
>>
>> regards
>> Rudolf
>>
>> ---
>> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
>> Schutz ist aktiv.
>> http://www.avast.com
>>
>


---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

For UTF-8 the only way is to binary open,read and close file with 
Fopen/fread/fclose into cBlob var. Everything else is the same.

Mind also, that there are sometime issues when using mysql via odbc and 
reading/writing UTF-8 strings into char,varchar type fields (but not blob).

Also take into account that UTF-8 is multi-byte encoding so you have to 
resize char/varchar fields accordingly.



On 20.10.2014 16:11, "Rudolf Reinthaler" wrote:
> Hello,
> thank you all, the example with SQLValue() looks interesting, will try
> to add more than one Blob fields. I think the Array for parameters
> How can I add the UTF-8 encoded xml to the textfield ?
> regards
> Rudolf
>
>
>
> Am 20.10.2014 15:51, schrieb Matej Jurac:
>> For BLOB via SqlExpress it is done this way:
>>
>> #include "sql.ch"
>> #include "sqlext.ch"
>>
>> function dosomestuff()
>>    local cBlob := ""
>>    local oNew, oCol
>>    local nlen := 0
>>    LOCAL nId := 1000
>>
>>
>> cBlob := MemoRead("somefile.rtf")   //if non-text type file then use
>> fopen/fread/fclose!
>>
>> oConnect :=
>> SQlConnection(INSERT_PARAMETERS_HERE_DEPENDS_ON_SYS_AND_PROGRAM)
>>
>> //get a blank statement
>> oNew := oConnect:NewStatement()
>>
>> //basic string
>> oNew:SQLString := "INSERT INTO gcbin (doid,dado) VALUES
>> ("+Ltrim(Str(nId,11,0))+",?)"
>> nLen := len(cBlob)
>>
>> //additional value object with blob data
>> oCol := SQLValue():new(cBlob, SQL_LONGVARBINARY, SQL_C_BINARY,nLen, 0,
>> nLen)
>>
>> //execute statement with
>> oNew:Execute({cBlob, oCol})
>> oNew:Destroy()
>>
>>
>> /*
>> mysql> show columns from gcbin;
>> +-------+------------+------+-----+---------+-------+
>> | Field | Type       | Null | Key | Default | Extra |
>> +-------+------------+------+-----+---------+-------+
>> | doid  | int(11)    | YES  |     | NULL    |       |
>> | dado  | mediumblob | YES  |     | NULL    |       |
>> +-------+------------+------+-----+---------+-------+
>> 2 rows in set (0.00 sec)
>>
>>
>>
>> */
>>
>> RETURN .T.  //or whatever needed
>>
>>
>>
>>
>>
>>
>> On 20.10.2014 15:01, "Rudolf Reinthaler" wrote:
>>> Hello,
>>> I use SQLEXPRESS and need a sample how to append or insert binary data
>>> in a table. Actually I transfer the data with a server side cursor, but
>>> because of communction problems sometimes I loose the cursor. So I would
>>> like to chante to SQL statements for insert and append.
>>> I have images and xml files where some of them are stored in a dbf
>>> table.
>>> The structure of the SQL table is:
>>> ID             INT(10) NOT NULL DEFAULT 0,
>>> xml            TEXT      DEFAULT NULL,
>>> datum          DATE      DEFAULT NULL,
>>> image          MEDIUMBLOB DEFAULT NULL,
>>> pdf1           MEDIUMBLOB DEFAULT NULL,
>>>
>>> Field pdf1 is a file, the other fields are from a dbf table:
>>> ID    N     10
>>> XML     M        8
>>> DATUM   D        8
>>> IMAGE   M        8   JPEG
>>>
>>> Has anybody a sample for a SQL statment tha can handel this, especially
>>> the BLOB fields ?
>>>
>>> regards
>>> Rudolf
>>>
>>> ---
>>> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
>>> Schutz ist aktiv.
>>> http://www.avast.com
>>>
>>
>
>
> ---
> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
> Schutz ist aktiv.
> http://www.avast.com
>

Hello Matej,
thank you, I will change the XML fields to MEDIUMBLOB.
In your sample the SQL string has only one question mark, but you pass 
two parameters in the execute, is this correct ? I think for each ? I 
need one parameter.
regards
Rudolf

Am 21.10.2014 07:52, schrieb Matej Jurac:
>
> For UTF-8 the only way is to binary open,read and close file with
> Fopen/fread/fclose into cBlob var. Everything else is the same.
>
> Mind also, that there are sometime issues when using mysql via odbc and
> reading/writing UTF-8 strings into char,varchar type fields (but not blob).
>
> Also take into account that UTF-8 is multi-byte encoding so you have to
> resize char/varchar fields accordingly.
>
>
>
> On 20.10.2014 16:11, "Rudolf Reinthaler" wrote:
>> Hello,
>> thank you all, the example with SQLValue() looks interesting, will try
>> to add more than one Blob fields. I think the Array for parameters
>> How can I add the UTF-8 encoded xml to the textfield ?
>> regards
>> Rudolf
>>
>>
>>
>> Am 20.10.2014 15:51, schrieb Matej Jurac:
>>> For BLOB via SqlExpress it is done this way:
>>>
>>> #include "sql.ch"
>>> #include "sqlext.ch"
>>>
>>> function dosomestuff()
>>>    local cBlob := ""
>>>    local oNew, oCol
>>>    local nlen := 0
>>>    LOCAL nId := 1000
>>>
>>>
>>> cBlob := MemoRead("somefile.rtf")   //if non-text type file then use
>>> fopen/fread/fclose!
>>>
>>> oConnect :=
>>> SQlConnection(INSERT_PARAMETERS_HERE_DEPENDS_ON_SYS_AND_PROGRAM)
>>>
>>> //get a blank statement
>>> oNew := oConnect:NewStatement()
>>>
>>> //basic string
>>> oNew:SQLString := "INSERT INTO gcbin (doid,dado) VALUES
>>> ("+Ltrim(Str(nId,11,0))+",?)"
>>> nLen := len(cBlob)
>>>
>>> //additional value object with blob data
>>> oCol := SQLValue():new(cBlob, SQL_LONGVARBINARY, SQL_C_BINARY,nLen, 0,
>>> nLen)
>>>
>>> //execute statement with
>>> oNew:Execute({cBlob, oCol})
>>> oNew:Destroy()
>>>
>>>
>>> /*
>>> mysql> show columns from gcbin;
>>> +-------+------------+------+-----+---------+-------+
>>> | Field | Type       | Null | Key | Default | Extra |
>>> +-------+------------+------+-----+---------+-------+
>>> | doid  | int(11)    | YES  |     | NULL    |       |
>>> | dado  | mediumblob | YES  |     | NULL    |       |
>>> +-------+------------+------+-----+---------+-------+
>>> 2 rows in set (0.00 sec)
>>>
>>>
>>>
>>> */
>>>
>>> RETURN .T.  //or whatever needed
>>>
>>>
>>>
>>>
>>>
>>>
>>> On 20.10.2014 15:01, "Rudolf Reinthaler" wrote:
>>>> Hello,
>>>> I use SQLEXPRESS and need a sample how to append or insert binary data
>>>> in a table. Actually I transfer the data with a server side cursor, but
>>>> because of communction problems sometimes I loose the cursor. So I
>>>> would
>>>> like to chante to SQL statements for insert and append.
>>>> I have images and xml files where some of them are stored in a dbf
>>>> table.
>>>> The structure of the SQL table is:
>>>> ID             INT(10) NOT NULL DEFAULT 0,
>>>> xml            TEXT      DEFAULT NULL,
>>>> datum          DATE      DEFAULT NULL,
>>>> image          MEDIUMBLOB DEFAULT NULL,
>>>> pdf1           MEDIUMBLOB DEFAULT NULL,
>>>>
>>>> Field pdf1 is a file, the other fields are from a dbf table:
>>>> ID    N     10
>>>> XML     M        8
>>>> DATUM   D        8
>>>> IMAGE   M        8   JPEG
>>>>
>>>> Has anybody a sample for a SQL statment tha can handel this, especially
>>>> the BLOB fields ?
>>>>
>>>> regards
>>>> Rudolf
>>>>
>>>> ---
>>>> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
>>>> Schutz ist aktiv.
>>>> http://www.avast.com
>>>>
>>>
>>
>>
>> ---
>> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
>> Schutz ist aktiv.
>> http://www.avast.com
>>
>


---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com