converting pointer to value

converting pointer to value

[Björn Lundin]

Hi!
I am trying to implement clob handling towards a database
using the ODBC binding at <http://gnade.sourceforge.net/>

The ODBC API itself is somewhat awkward for this, but I have some Ada 
issues as well.

In one call I do the binding of data to a bind variable.

   insert into table A vales (?,?)

where the '?' are my bind variables in the sql.

Now, when data is small I call bindcol via

   Gnu.Db.Sqlcli.Sqlbindparameter

and put the wanted value into the binding.

But with CLOBs, there are too much data, so instead of setting
the actual data, one is to indicate which column to put the
data into - at a later time.

So then I call exec, it indicates by resultcode
that no all values are present.

I then get to call

     Rc := Sqlparamdata (Statement_Handle,Pvalue'Access);

with spec like this to find out what column misses value.
This is the column mentioned above

    function SQLParamData (StatementHandle : SQLHSTMT;
                           pValue          : access SQLPOINTER)
                          return SQLRETURN;

so I get back the column in pValue.
pValue is declared as
     Pvalue : aliased Sqlpointer;

I did put in a SQLINTEGER into the previous call, and want that back.

I also have the definitions
    type SQLPOINTER is private;

    type SQLINTEGER   is range -(2 ** 31) .. +(2 ** 31) - 1;
    for SQLINTEGER'Size use 32;
....
private
    type SQLPOINTER is new System.Storage_Elements.Integer_Address;

and there is a utility functions

    function To_Address (P : SQLPOINTER) return System.Address;


And here's my problem

from the call
     Rc := Sqlparamdata (Statement_Handle,Pvalue'Access);
how do get Pvalue as an integer value?

Running it in debug in GPS I hoover over the variable
and I see the correct value
(the one I feed into it earlier) but how
do I get a hold of it?

this code looks like

    type SQLSMALLINT  is range -(2 ** 15) .. +(2 ** 15) - 1;
    for SQLSMALLINT'Size use 16;

    type SQLRETURN is new SQLSMALLINT;


   procedure Exec (Statement_Handle : in Sqlhstmt) is
     RC :  SQLRETURN := SQLExecute (Statement_Handle);
     Pvalue : aliased Sqlpointer;
     Rc2 :  SQLRETURN := 0;
    begin
     Text_Io.Put_Line ("Exec: " & Rc'Img);

     Rc2 := Sqlparamdata (Statement_Handle,Pvalue'Access);
     Text_Io.Put_Line ("SQLParamData1: " & Rc2'Img );

     how to get the Pvalue as an SQLRETURN?


-- 
Björn

Re: converting pointer to value

[Dmitry A. Kazakov]

On 2021-03-04 16:59, Björn Lundin wrote:

> I am trying to implement clob handling towards a database
> using the ODBC binding at <http://gnade.sourceforge.net/>

I do not think GNADE is supported and correct with the changes MS made 
to ODBC after the support was dropped.

> The ODBC API itself is somewhat awkward for this, but I have some Ada 
> issues as well.

There are actually two ODBC interfaces depending on the machine 
architecture, 32- and 64-bit. E.g. SQLSETPOSIROW is 16-bit or 64-bit, 
SQLLEN is 32- or 64-bit. That was the change MS did to ODBC for a decade 
ago, I believe.

> In one call I do the binding of data to a bind variable.
> 
>    insert into table A vales (?,?)
> 
> where the '?' are my bind variables in the sql.
> 
> Now, when data is small I call bindcol via
> 
>    Gnu.Db.Sqlcli.Sqlbindparameter
> 
> and put the wanted value into the binding.
> 
> But with CLOBs, there are too much data, so instead of setting
> the actual data, one is to indicate which column to put the
> data into - at a later time.

I do not understand this. Binding binds a variable to a prepared 
statement. You change the variable before each execution of the statement:

    prepare
    bind x to column 1
    bind y to column 2
    bind z to column 3

    x := 1;
    y := 20;
    z := 100;
    execute

    x := 11;
    y := -20;
    z := 10440;
    execute

    ...

Binding can be thought as by-reference parameter passing.

In case of blobs, if the size vary, you have to rebind it with another 
size before another statement execution. Unfortunately column size is a 
parameter of SQLBindParameter rather than the parameter data. If the 
size is same you only change the buffer contents. The buffer itself can 
be allocated once for its maximal size.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: converting pointer to value

[Shark8]

On Thursday, March 4, 2021 at 8:59:48 AM UTC-7, björn lundin wrote:

> this code looks like 
> 
> type SQLSMALLINT is range -(2 ** 15) .. +(2 ** 15) - 1; 
> for SQLSMALLINT'Size use 16; 
> 
> type SQLRETURN is new SQLSMALLINT; 
> 
> 
> how to get the Pvalue as an SQLRETURN? 

Ok, in Ada the creation of a new type, even if only deriving it via NEW, makes a type which is incompatible with the old type.
Thus you can say TYPE TEMPERATURE IS NEW INTEGER; and TYPE SPEED IS NEW INTEGER; and values of these types are NOT interchangeable even though both have the same internal representation and properties that INTEGER does. The way that you can use these different types together is by casting; given X : SPEED := 3; you can say Y :  TEMPERATURE := SPEED(X); -- which works because they have the same internal representation if they did not have the same representation you would have to use UNCHECKED_CONVERSION.

An example where you might need differing representations is modeling the x86 -- you could have something like:
Type Integer_Register is new Integer; -- IIRC, AX is 16-bits, divided into bytes AH and AL.
Type HL_Byte_Register is record
  High, Low : Interfaces.Unsigned_8;
end record;

AX : Integer_Register:= 16#EF10#;
...
Declare
  Function Convert is new Ada.Unchecked_Conversion(Integer_Register, HL_Byte_Register);
  Function Convert is new Ada.Unchecked_Conversion(HL_Byte_Register, Integer_Register);
  Temp : HL_Byte_Register := convert( AX );
Begin
  -- Stuff w/ bytes.
  AX:= Convert( Temp );
End;

In the code you present, SQLRETURN derives from SQLSMALLINT, so you can say SQLSMALLINT( Return_Value )... yes, I know this isn't pvalue as sqlreturn, we'll get there.

> 
> function SQLParamData (StatementHandle : SQLHSTMT; 
> pValue : access SQLPOINTER) 
> return SQLRETURN; 
> 

So this returns a number, of the same representation that the 16-bit SQLSMALLINT has.

> so I get back the column in pValue. 
> pValue is declared as 
> Pvalue : aliased Sqlpointer; 
...
> type SQLPOINTER is private;
....
> private
> type SQLPOINTER is new System.Storage_Elements.Integer_Address;

And here we have SQLPOINTER, which is private, but which is something of the same representation that Integer_Address has; let's assume that the ADDRESS type is implementation-defined, and a private type.
What does this mean?
It means that we don't know what the size of ADDRESS (and thus SQLPOINTER) actually is, but assuming you're on a 32- or 64-bit machine it's likely 2 to 4 times as large as the 16-bit SQLSMALLINT-- which is a good indication that a simple UNCHECKED_CONVERSION is the wrong answer.

Try looking at the operations which have SQLPOINTER as a parameter/result.

Re: converting pointer to value

[Dmitry A. Kazakov]

On 2021-03-04 17:55, Shark8 wrote:

>> type SQLPOINTER is private;
> ....
>> private
>> type SQLPOINTER is new System.Storage_Elements.Integer_Address;
> 
> And here we have SQLPOINTER, which is private, but which is something of the same representation that Integer_Address has; let's assume that the ADDRESS type is implementation-defined, and a private type.

SQLPOINTER is System.Address.

> What does this mean?
> It means that we don't know what the size of ADDRESS (and thus SQLPOINTER) actually is, but assuming you're on a 32- or 64-bit machine it's likely 2 to 4 times as large as the 16-bit SQLSMALLINT-- which is a good indication that a simple UNCHECKED_CONVERSION is the wrong answer.

You need not to know that:

    declare
       P : SQLPOINTER := ...;
       A : System.Address;
       pragma Import (Ada, A);
       for A'Address use P'Address;
       T : My_Fancy_Object;
       pragma Import (Ada, My_Fancy_Object);
       for T'Address use A;
    begin
       ... -- Use T at P

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: converting pointer to value

[Shark8]

On Thursday, March 4, 2021 at 10:35:01 AM UTC-7, Dmitry A. Kazakov wrote:
> On 2021-03-04 17:55, Shark8 wrote: 
> 
> >> type SQLPOINTER is private; 
> > .... 
> >> private 
> >> type SQLPOINTER is new System.Storage_Elements.Integer_Address; 
> > 
> > And here we have SQLPOINTER, which is private, but which is something of the same representation that Integer_Address has; let's assume that the ADDRESS type is implementation-defined, and a private type.
> SQLPOINTER is System.Address.

Which is implementation-defined.
Even though most programmers would assert that address and integer are the same thing, this isn't a given; segmented architectures would more naturally map to records.

> > What does this mean? 
> > It means that we don't know what the size of ADDRESS (and thus SQLPOINTER) actually is, but assuming you're on a 32- or 64-bit machine it's likely 2 to 4 times as large as the 16-bit SQLSMALLINT-- which is a good indication that a simple UNCHECKED_CONVERSION is the wrong answer.
> You need not to know that: 
> 
> declare 
> P : SQLPOINTER := ...; 
> A : System.Address; 
> pragma Import (Ada, A); 
> for A'Address use P'Address; 
> T : My_Fancy_Object; 
> pragma Import (Ada, My_Fancy_Object); 
> for T'Address use A; 
> begin 
> ... -- Use T at P

None of that addresses the issue that he asked: "how to get the Pvalue as an SQLRETURN?"
One of these is 16-bits, the other is derived from System.Storage_Elements.Integer_Address.
Sure, you can overlay, and that could be valid in some cases, but I simply don't have enough information here to say if that would be a valid solution and it's much more likely that it would be completely unintended.

Re: converting pointer to value

[Simon Wright]

Shark8 <onewingedshark@gmail.com> writes:

> if they did not have the same representation you would
> have to use UNCHECKED_CONVERSION

and you'd be in a world of pain

Re: converting pointer to value

[Shark8]

On Thursday, March 4, 2021 at 1:09:24 PM UTC-7, Simon Wright wrote:
> Shark8 writes: 
> 
> > if they did not have the same representation you would 
> > have to use UNCHECKED_CONVERSION
> and you'd be in a world of pain
Not necessarily, as the registers of x86 show with AX overlaid upon AH+AL, absolutely not the same representation, unchecked_conversion (or address-overlay) would be a perfectly fine way to model the situation.
But the given situation: a 16-bit integer and an address-type? / That's just asking for pain.

Re: converting pointer to value

[Dmitry A. Kazakov]

On 2021-03-04 20:38, Shark8 wrote:
> On Thursday, March 4, 2021 at 10:35:01 AM UTC-7, Dmitry A. Kazakov wrote:
>> On 2021-03-04 17:55, Shark8 wrote:
>>
>>>> type SQLPOINTER is private;
>>> ....
>>>> private
>>>> type SQLPOINTER is new System.Storage_Elements.Integer_Address;
>>>
>>> And here we have SQLPOINTER, which is private, but which is something of the same representation that Integer_Address has; let's assume that the ADDRESS type is implementation-defined, and a private type.
>> SQLPOINTER is System.Address.
> 
> Which is implementation-defined.

No, it defined this way

    typedef void * SQLPOINTER;

so for all purposes it is System.Address.

> None of that addresses the issue that he asked: "how to get the Pvalue as an SQLRETURN?"

OK, then the answer is you don't.

As the rule QDBC does not overlay different types. There are special 
pointer values though, like 1 etc. Integer_Address can be used to encode 
them. They are constants.

P.S. I think the OP refers to SQLParamData/SQLPutData mechanism. There 
is no overloading either. It works this way:

    prepare
    bind to special "address" value, 1, I believe

    execute
    if SQLParamData = need_data then
       loop
          SQLPutData (next chunk)
       end loop;
    end if;

I do not think it is worth using, unless the data are so big that they 
are not in the memory, or read from a stream chunk by chunk etc. Clearly 
it is a very expensive method. A normal bind would be a better choice 
for most applications.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: converting pointer to value

[Björn Lundin]

Den 2021-03-04 kl. 17:50, skrev Dmitry A. Kazakov:
> On 2021-03-04 16:59, Björn Lundin wrote:
> 
>> I am trying to implement clob handling towards a database
>> using the ODBC binding at <http://gnade.sourceforge.net/>
> 
> I do not think GNADE is supported and correct with the changes MS made 
> to ODBC after the support was dropped.
> 
> There are actually two ODBC interfaces depending on the machine 
> architecture, 32- and 64-bit. E.g. SQLSETPOSIROW is 16-bit or 64-bit, 
> SQLLEN is 32- or 64-bit. That was the change MS did to ODBC for a decade 
> ago, I believe.

Yeah, I doubt it i supported - but I find the feature I use working.
A colleague did fix the 64bit changes,

I should have mentioned that this is Linux on 64bit that I try.
It will run on windows 64 bit (which is the same code) and I might
port it to Windows 32-bit - but that I have not decided yet.




> 
>> In one call I do the binding of data to a bind variable.
>>
>>    insert into table A vales (?,?)
>>
>> where the '?' are my bind variables in the sql.
>>
>> Now, when data is small I call bindcol via
>>
>>    Gnu.Db.Sqlcli.Sqlbindparameter
>>
>> and put the wanted value into the binding.
>>
>> But with CLOBs, there are too much data, so instead of setting
>> the actual data, one is to indicate which column to put the
>> data into - at a later time.
> 
> I do not understand this. Binding binds a variable to a prepared 
> statement. You change the variable before each execution of the statement:

hmm, I usually rebind after an execute. I now realize I don't have to.
thanks.


> 
> In case of blobs, if the size vary, you have to rebind it with another 
> size before another statement execution. Unfortunately column size is a 
> parameter of SQLBindParameter rather than the parameter data. If the 
> size is same you only change the buffer contents. The buffer itself can 
> be allocated once for its maximal size.

hmm I did not think of that. But - if the clob is big enough - that will 
not help. But yes - my use case has likely clobs less than a few Mb.

Anyway, some docs* I found describes Clob handling as

use bind to bind nothing to the col - but tag it
execute (will then return NEED_MORE_DATA)
ParamData will return NEED_MORE_DATA and indicate what col that needs 
the data (which is the problem i want to solve first)
loop
   putDAta
   exit when put enough data
loop
final call to ParamData will now indicate SUCCESS.


And I got that part to work to some extent. (if I putData in chunks of 
10 bytes and I said size= 100. But not if size = 104. Likely an error on 
my part)
And yes that small size is just me testing. chunks will be in larger 
sizes when I get it to work.

docs*
<https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlputdata-function?view=sql-server-ver15>

<https://jeffpar.github.io/kbarchive/kb/128/Q128720/>



/Björn








> 


-- 
Björn

Re: converting pointer to value

[Björn Lundin]

Den 2021-03-04 kl. 17:55, skrev Shark8:
> On Thursday, March 4, 2021 at 8:59:48 AM UTC-7, björn lundin wrote:
> 
>> this code looks like
>>
>> type SQLSMALLINT is range -(2 ** 15) .. +(2 ** 15) - 1;
>> for SQLSMALLINT'Size use 16;
>>
>> type SQLRETURN is new SQLSMALLINT;
>>
>>
>> how to get the Pvalue as an SQLRETURN?
> 
> Ok, in Ada the creation of a new type, even if only deriving it via NEW, makes a type which is incompatible with the old type.
> Thus you can say TYPE TEMPERATURE IS NEW INTEGER; and TYPE SPEED IS NEW INTEGER; and values of these types are NOT interchangeable even though both have the same internal representation and properties that INTEGER does. The way that you can use these different types together is by casting; given X : SPEED := 3; you can say Y :  TEMPERATURE := SPEED(X); -- which works because they have the same internal representation if they did not have the same representation you would have to use UNCHECKED_CONVERSION.
> 

well, yes.


...


> It means that we don't know what the size of ADDRESS (and thus SQLPOINTER) actually is, but assuming you're on a 32- or 64-bit machine it's likely 2 to 4 times as large as the 16-bit SQLSMALLINT-- which is a good indication that a simple UNCHECKED_CONVERSION is the wrong answer.

I think I first settle with 64bit.
64bit os and 64 bit compiler

> 
> Try looking at the operations which have SQLPOINTER as a parameter/result.

I did and the only operation usefull here is the mentioned

utility function

    function To_Address (P : SQLPOINTER) return System.Address;



-- 
Björn

Re: converting pointer to value

[Dmitry A. Kazakov]

On 2021-03-05 08:06, Björn Lundin wrote:

> Anyway, some docs* I found describes Clob handling as

> use bind to bind nothing to the col - but tag it
> execute (will then return NEED_MORE_DATA)
> ParamData will return NEED_MORE_DATA and indicate what col that needs 

Yes, it returns in its buffer address + offset calculated as if 
parameters were an array. Here address is 1, if I remember correctly.

But you can just ignore that stuff as you know the column you write 
already. There is only one blob. If other parameters are bound normally 
or literals you just call PutData with the first chunk ignoring anything 
  but the return code of ParamData.

> the data (which is the problem i want to solve first)
> loop
>    putDAta
>    exit when put enough data
> loop
> final call to ParamData will now indicate SUCCESS.
> 
> 
> And I got that part to work to some extent. (if I putData in chunks of 
> 10 bytes and I said size= 100. But not if size = 104. Likely an error on 
> my part)

Did you specify 104 as the total size in BindParameter?

> And yes that small size is just me testing. chunks will be in larger 
> sizes when I get it to work.

It will be awfully slow, I guess.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

Re: converting pointer to value

[Björn Lundin]

Den 2021-03-04 kl. 18:35, skrev Dmitry A. Kazakov:

> You need not to know that:
> 
>     declare
>        P : SQLPOINTER := ...;
>        A : System.Address;
>        pragma Import (Ada, A);
>        for A'Address use P'Address;
>        T : My_Fancy_Object;
>        pragma Import (Ada, My_Fancy_Object);
>        for T'Address use A;
>     begin
>        ... -- Use T at P
> 


I did try this
procedure Exec (Statement_Handle : in Sqlhstmt) is
     RC :  SQLRETURN := SQLExecute (Statement_Handle);
     Pvalue : aliased Sqlpointer;
     Sent: Integer := 0;
     Datalen : Integer := 14;
     Buffer  : String  (1..10) := (others => ' ');
     S_Ptr   : Ptr_String;
     Sa      : aliased system.Address;
     type Ia is access all SQLINTEGER;
     Psa : Ia;
    begin
       Text_Io.Put_Line ("Exec: " & Rc'Img);
     --test for unset clob
       Rc := Sqlparamdata (Statement_Handle,Pvalue'Access);
       Text_Io.Put_Line ("SQLParamData1: " & Rc'Img );
       Sa := to_Address(Pvalue);
       declare
         I : SQLINTEGER ;
         for I'Address use Sa;
       begin
          Text_Io.Put_Line ("SQLParamData1.2: " & I'Img );
       end;
       if Rc /= Sql_Need_Data then
         return;
       end if;

and as a result I got

SQLParamData1.2:  4790608

(intel 64bit Linux)

I expected 5
Later today I'll try the truick with pragma Import.
However I don't really see/understand the difference.




--
Björn

Re: converting pointer to value

[Björn Lundin]

Den 2021-03-04 kl. 22:27, skrev Dmitry A. Kazakov:
> On 2021-03-04 20:38, Shark8 wrote:
>> On Thursday, March 4, 2021 at 10:35:01 AM UTC-7, Dmitry A. Kazakov wrote:
>>> On 2021-03-04 17:55, Shark8 wrote:
>>>
>>>>> type SQLPOINTER is private;
>>>> ....
>>>>> private
>>>>> type SQLPOINTER is new System.Storage_Elements.Integer_Address;
>>>>
>>>> And here we have SQLPOINTER, which is private, but which is 
>>>> something of the same representation that Integer_Address has; let's 
>>>> assume that the ADDRESS type is implementation-defined, and a 
>>>> private type.
>>> SQLPOINTER is System.Address.
>>
>> Which is implementation-defined.
> 
> No, it defined this way
> 
>     typedef void * SQLPOINTER;
> 
> so for all purposes it is System.Address.
> 
>> None of that addresses the issue that he asked: "how to get the Pvalue 
>> as an SQLRETURN?"
> 
> OK, then the answer is you don't.
> 
> As the rule QDBC does not overlay different types. There are special 
> pointer values though, like 1 etc. Integer_Address can be used to encode 
> them. They are constants.
> 
> P.S. I think the OP refers to SQLParamData/SQLPutData mechanism. There 

Yes - I am. But the problem at hand was more of Ada nature than handling 
tha ODBC API (eventhough yuo've started med thinking about if I'm doing 
it the right way)

> is no overloading either. It works this way:
> 
>     prepare
>     bind to special "address" value, 1, I believe
> 
>     execute
>     if SQLParamData = need_data then
>        loop
>           SQLPutData (next chunk)
>        end loop;
>     end if;
> 
> I do not think it is worth using, unless the data are so big that they 
> are not in the memory, or read from a stream chunk by chunk etc. Clearly 
> it is a very expensive method. A normal bind would be a better choice 
> for most applications.

Yes. The use case at hand is with data fitting into RAM.
But that might change - hence the PutData way.

But I will reconsider, and contemplate if I really need that.

-- 
Björn

Re: converting pointer to value

[Björn Lundin]

Den 2021-03-04 kl. 22:00, skrev Shark8:
> On Thursday, March 4, 2021 at 1:09:24 PM UTC-7, Simon Wright wrote:
>> Shark8 writes:
>>
>>> if they did not have the same representation you would
>>> have to use UNCHECKED_CONVERSION
>> and you'd be in a world of pain
> Not necessarily, as the registers of x86 show with AX overlaid upon AH+AL, absolutely not the same representation, unchecked_conversion (or address-overlay) would be a perfectly fine way to model the situation.
> But the given situation: a 16-bit integer and an address-type? / That's just asking for pain.
> 

hmm, yes.
But perhaps I should cast the 16 bit to a 64 bit variable
and then do unchecked conversion?

I'll spend the afternoon trying different approaches

-- 
Björn

Re: converting pointer to value

[Björn Lundin]

Den 2021-03-05 kl. 08:44, skrev Dmitry A. Kazakov:
> On 2021-03-05 08:06, Björn Lundin wrote:
> 
>> Anyway, some docs* I found describes Clob handling as
> 
>> use bind to bind nothing to the col - but tag it
>> execute (will then return NEED_MORE_DATA)
>> ParamData will return NEED_MORE_DATA and indicate what col that needs 
> 
> Yes, it returns in its buffer address + offset calculated as if 
> parameters were an array. Here address is 1, if I remember correctly.
> 
> But you can just ignore that stuff as you know the column you write 
> already. 

I can't. This is code fitting as glue between ODBC binding and 
application code.
Meaning some of my colleagues could define a clob as any column

>There is only one blob.

and actually, that is not ceratain.
Some colleagues actually defined a table containing two clobs.
And there really is not limit (more than practical use)

If other parameters are bound normally
> or literals you just call PutData with the first chunk ignoring anything 
>   but the return code of ParamData.

yes, and that is how I got it to work while trying and testing
But it won't hold in the end. I got to get the column(s) back (or 
rethink the use of the API)

>>
>> And I got that part to work to some extent. (if I putData in chunks of 
>> 10 bytes and I said size= 100. But not if size = 104. Likely an error 
>> on my part)
> 
> Did you specify 104 as the total size in BindParameter?

Yes. And I 10 chunks of 10 bytes + 1 chunk of 4 bytes.
I tried null-terminate the last chunk, both by j
* just by appending an ASCI.NUL (5 bytes for last chunk - 105 bytes in 
total)
* replacing last byte in last chunk with ASCII.NUL - 104 bytes in total

No go still


> It will be awfully slow, I guess.
Likely, but I did not measure anything yet.
I think the usecase for it will be uncommon, so I might tolerate 
'somewhat' slow execution.


-- 
Björn

Re: converting pointer to value

[Björn Lundin]

Den 2021-03-04 kl. 18:35, skrev Dmitry A. Kazakov:
> On 2021-03-04 17:55, Shark8 wrote:
> 
>>> type SQLPOINTER is private;
>> ....
>>> private
>>> type SQLPOINTER is new System.Storage_Elements.Integer_Address;
>>
>> And here we have SQLPOINTER, which is private, but which is something 
>> of the same representation that Integer_Address has; let's assume that 
>> the ADDRESS type is implementation-defined, and a private type.
> 
> SQLPOINTER is System.Address.
> 
>> What does this mean?
>> It means that we don't know what the size of ADDRESS (and thus 
>> SQLPOINTER) actually is, but assuming you're on a 32- or 64-bit 
>> machine it's likely 2 to 4 times as large as the 16-bit SQLSMALLINT-- 
>> which is a good indication that a simple UNCHECKED_CONVERSION is the 
>> wrong answer.
> 
> You need not to know that:
> 
>     declare
>        P : SQLPOINTER := ...;
>        A : System.Address;
>        pragma Import (Ada, A);
>        for A'Address use P'Address;
>        T : My_Fancy_Object;
>        pragma Import (Ada, My_Fancy_Object);
>        for T'Address use A;
>     begin
>        ... -- Use T at P
> 


so I tried this version:

procedure Exec (Statement_Handle : in Sqlhstmt) is
     Rc      :  Sqlreturn := Sqlexecute (Statement_Handle);
     Pvalue  : aliased Sqlpointer;
     Sent    : Integer := 0;
     Datalen : Integer := 14;
     Buffer  : String  (1..10) := (others => ' ');
     S_Ptr   : Ptr_String;

     A       :  System.Address;
     pragma Import(Ada,A);
     for A'Address use Pvalue'Address;
     I       : Sqlinteger ;
     pragma Import(Ada,I);
     for I'Address use A;

   begin
     --test for unset clob
     Rc := Sqlparamdata (Statement_Handle,Pvalue'Access);
     Text_Io.Put_Line ("SQLParamData1.2: " & I'Img );


SQLParamData1.2:  4790608


I'm thinking that I may be dealing with a pointer to a pointer here?
hmm, needs more testing. I changed the expected value from 5 to 7. Still 
a bit off...



-- 
Björn

Re: converting pointer to value

[Björn Lundin]

Den 2021-03-05 kl. 12:02, skrev Björn Lundin:

> 
> SQLParamData1.2:  4790608
> 
> 
> I'm thinking that I may be dealing with a pointer to a pointer here?
> hmm, needs more testing. I changed the expected value from 5 to 7. Still 
> a bit off...
> 

The way I put the value 5 or 7 into the API is

procedure Bind_Parameter(Statement    : in out Statement_Type;
                            Column_Index : in Positive;
                            Value        : in CLOB;
                            Null_Value   : in Boolean := False) is
     A : Bind_Parameter_Type;
     L : Sqlulen := Sqlulen(Length(Value));
     Rc : Sqlreturn := 0;
     Local_Length : Sqllen ;
   begin

     Local_Length := Sqllen(Sqllen(-100) -Sqllen(L)); 
--Sqllen'(SQL_LEN_DATA_AT_EXEC_OFFSET  - L);

     A.String_Pointer := new String'(To_String(Value) & Ascii.Nul);

     if Null_Value then
       A.Length_Pointer := new Sqllen'(Sql_Null_Data);
     else
       A.Length_Pointer := new Sqllen'(Local_Length);
     end if;

      Rc := gnu.Db.Sqlcli.Sqlbindparameter
          (Statementhandle  => Statement.Get_Handle,
           Parameternumber  => Sql_Parameter_Number (Column_Index),
           Inputoutputtype  => Sql_Param_Input,
           Valuetype        => Sql_C_Char,
           Parametertype    => Sql_Char,
           Columnsize       => L,
           Decimaldigits    => 0,
           Value            => 7,         --<-- here
           Bufferlength     => 0,
           Strlen_Or_Indptr => A.Length_Pointer);

   exception
     when  Gnu.Db.Sqlcli.Table_Not_Found => raise Sql.No_Such_Object;
   end Bind_Parameter;


   where Bind_parameter_Type contains
     Length_Pointer : aliased PTR_SQLLEN := null;
     String_Pointer : aliased PTR_STRING := null;


and PTR_types defined as
type PTR_STRING is access all String;
Type PTR_SQLLEN is access all SQLLEN;

and SQLLEN is a signed 64-bit integer

and SQLBindParameter looks like


function SQLBindParameter (StatementHandle  : in SQLHSTMT;
                               ParameterNumber  : in SQL_Parameter_Number;
                               InputOutputType  : in SQL_Parameter_Type;
                               ValueType        : in SQL_C_DATA_TYPE;
                               ParameterType    : in SQL_DATA_TYPE;
                               ColumnSize       : in SQLULEN;
                               DecimalDigits    : in SQLSMALLINT;
                               Value            : in SQLINTEGER;
                               BufferLength     : in SQLLEN;
                               StrLen_Or_IndPtr : access SQLLEN)
                              return SQLRETURN is
       function BindParameter (StatementHandle  : in SQLHSTMT;
                               ParameterNumber  : in SQL_Parameter_Number;
                               InputOutputType  : in SQL_Parameter_Type;
                               ValueType        : in SQL_C_DATA_TYPE;
                               ParameterType    : in SQL_DATA_TYPE;
                               ColumnSize       : in SQLULEN;
                               DecimalDigits    : in SQLSMALLINT;
                               Value            : in SQLINTEGER;
                               BufferLength     : in SQLLEN;
                               StrLen_Or_IndPtr : access SQLLen)
                              return SQLRETURN;
       pragma Import (Stdcall, BindParameter, "SQLBindParameter");
    begin
       return BindParameter (StatementHandle,
                             ParameterNumber,
                             InputOutputType,
                             ValueType,
                             ParameterType,
                             ColumnSize,
                             DecimalDigits,
                             Value,
                             BufferLength,
                             StrLen_Or_IndPtr);
    end SQLBindParameter;







/Björn


-- 
Björn

Re: converting pointer to value

[Dmitry A. Kazakov]

On 2021-03-05 12:57, Björn Lundin wrote:
> Den 2021-03-05 kl. 12:02, skrev Björn Lundin:
> 
>>
>> SQLParamData1.2:  4790608
>>
>>
>> I'm thinking that I may be dealing with a pointer to a pointer here?
>> hmm, needs more testing. I changed the expected value from 5 to 7. 
>> Still a bit off...
>>
> 
> The way I put the value 5 or 7 into the API is
> 
> procedure Bind_Parameter(Statement    : in out Statement_Type;
>                             Column_Index : in Positive;
>                             Value        : in CLOB;
>                             Null_Value   : in Boolean := False) is
>      A : Bind_Parameter_Type;
>      L : Sqlulen := Sqlulen(Length(Value));
>      Rc : Sqlreturn := 0;
>      Local_Length : Sqllen ;
>    begin
> 
>      Local_Length := Sqllen(Sqllen(-100) -Sqllen(L)); 
> --Sqllen'(SQL_LEN_DATA_AT_EXEC_OFFSET  - L);
> 
>      A.String_Pointer := new String'(To_String(Value) & Ascii.Nul);
> 
>      if Null_Value then
>        A.Length_Pointer := new Sqllen'(Sql_Null_Data);
>      else
>        A.Length_Pointer := new Sqllen'(Local_Length);
>      end if;
> 
>       Rc := gnu.Db.Sqlcli.Sqlbindparameter
>           (Statementhandle  => Statement.Get_Handle,
>            Parameternumber  => Sql_Parameter_Number (Column_Index),
>            Inputoutputtype  => Sql_Param_Input,
>            Valuetype        => Sql_C_Char,
>            Parametertype    => Sql_Char,
>            Columnsize       => L,
>            Decimaldigits    => 0,
>            Value            => 7,         --<-- here
>            Bufferlength     => 0,
>            Strlen_Or_Indptr => A.Length_Pointer);
> 
>    exception
>      when  Gnu.Db.Sqlcli.Table_Not_Found => raise Sql.No_Such_Object;
>    end Bind_Parameter;
> 
> 
>    where Bind_parameter_Type contains
>      Length_Pointer : aliased PTR_SQLLEN := null;
>      String_Pointer : aliased PTR_STRING := null;
> 
> 
> and PTR_types defined as
> type PTR_STRING is access all String;
> Type PTR_SQLLEN is access all SQLLEN;
> 
> and SQLLEN is a signed 64-bit integer
> 
> and SQLBindParameter looks like
> 
> 
> function SQLBindParameter (StatementHandle  : in SQLHSTMT;
>                                ParameterNumber  : in SQL_Parameter_Number;
>                                InputOutputType  : in SQL_Parameter_Type;
>                                ValueType        : in SQL_C_DATA_TYPE;
>                                ParameterType    : in SQL_DATA_TYPE;
>                                ColumnSize       : in SQLULEN;
>                                DecimalDigits    : in SQLSMALLINT;
>                                Value            : in SQLINTEGER;

This looks wrong because SQLINTEGER is 32-bit. Why do not you use 
Package System.Storage_Elements.Integer_Address instead, in order to 
place an integer there? It must be SQL_DATA_AT_EXEC which is -1, not 7. 
Alternatively you could use ptrdiff_t from Interfaces.C, because ARM is 
kind of ambiguous whether Integer_Address is signed.

-- 
Regards,
Dmitry A. Kazakov
http://www.dmitry-kazakov.de

[SOLVED] Re: converting pointer to value

[Björn Lundin]

Den 2021-03-04 kl. 18:35, skrev Dmitry A. Kazakov:

Dmitry, this

> 
> You need not to know that:
> 
>     declare
>        P : SQLPOINTER := ...;
>        A : System.Address;
>        pragma Import (Ada, A);
>        for A'Address use P'Address;
>        T : My_Fancy_Object;
>        pragma Import (Ada, My_Fancy_Object);
>        for T'Address use A;
>     begin
>        ... -- Use T at P
> 

combined with

 > This looks wrong because SQLINTEGER is 32-bit. Why do not you use
 > Package System.Storage_Elements.Integer_Address instead, in order to
 > place an integer there?

put me on the right track.
The actual Ada question - rather than ODBC-API question - was how I get 
the value at a certain address.

First - there has to be a pointer to a value - meaning I needed a new 
overload of SqlBindParameter that takes an integer pointer as argument, 
not the integer itself (here called value_pointer)

declare
     L            : SQLULEN := SQLULEN(Length(Tclob));
     Rc           : Sqlreturn := 0;
     Local_Length : Sqllen := Sqllen(Sql_Len_Data_At_Exec(Sqlinteger(L)));
     Length_Pointer   : aliased PTR_SQLLEN     := null;
     Value_Pointer    : aliased PTR_SQLINTEGER := null;
   begin
     Length_Pointer := new Sqllen'(Local_Length);
     Value_Pointer := new SQLINTEGER'(1);

     Rc := Gnu.Db.Sqlcli.Sqlbindparameter
        (Statementhandle  => Statement_Handle,
         Parameternumber  => 1,
         Inputoutputtype  => Sql_Param_Input,
         Valuetype        => Sql_C_Char,
         Parametertype    => Sql_Longvarchar,
         Columnsize       => L,
         Decimaldigits    => 0,
         Value            => Value_Pointer,
         Bufferlength     => 0,
         Strlen_Or_Indptr => Length_Pointer);
     Text_Io.Put_Line("bind clob rc" & Rc'Img);
   end;


then I needed to understand that in call to
Rc := SqlParamdata (Statement_Handle, pvalue'Access);
pvalue itself contains the address of the value_pointer above,
and that I can only tie that address to a variable once the value is known.

   declare
     Rc         :  Sqlreturn := Sqlexecute (Statement_Handle);
     Pvalue     : aliased Sqlpointer;
     Sent       : Integer := 0;
     Datalen    : Integer := 0;
     Buffer_Len : Natural := 0;
     Chunk_Size : Natural := 1024*10; -- 10 k chunks
     S_Ptr      : Ptr_String;
     N          : Positive := 1;
     From,To    : Positive := 1;
   begin
     Text_Io.Put_Line ("Exec: " & Rc'Img);
     -- get address of value_pointer
     Rc := Sqlparamdata (Statement_Handle,Pvalue'Access);
     declare
       Value    : aliased Sqlinteger ;
       Add : System.Address := Gnu.Db.Sqlcli.To_Address(Pvalue);
       for Value'Address use Add;
     begin
       Text_Io.Put_Line ("SQLParamData1: " & Rc'Img );
       --print it and use later on
       Text_Io.Put_Line ("SQLParamData1.2: '" & Value'Img  & "'");
     end;

So now I can putData to clob - I tried 100Mb, and that is all I need.


     -- tclob is an unbounded string containg some Mb of text
     Datalen := Length(Tclob);

     while Sent < Datalen loop
       From := N;
       if Datalen - Sent >= Chunk_Size then
         To := N+ Chunk_Size -1;
       else
         To := N+ Datalen - Sent -1;
       end if;

       Buffer_Len := To - From +1;

       S_Ptr :=  new String'(Slice(Tclob, From, To));
       N := N + Chunk_Size;

       Rc := Sqlputdata(Statementhandle => Statement_Handle,
                        Data            => To_Sqlpointer(S_Ptr),
                        Strlen_Or_Ind   => Sqllen(Buffer_Len));

       Sent := Sent + S_Ptr.all'Length ;

     end loop;
     Rc := Sqlparamdata (Statement_Handle, Pvalue'Access);
     Text_Io.Put_Line ("SQLParamData2.sist: " & Rc'Img);

   end;


I found some API error on my part.
The code above does not use pvalue, but I will need it later on.

Thanks for putting me on the right track




-- 
Björn