Save String To Odbc

[roiyjet]

Hi 

 

I am using windows 7 ans access 2010 

I change the format to TEXT in access 

I'm   writing number  into the database without any problem

but I cant write string even if the command is running fine inside access SQL query .

 

private string mess

mess='high temperature'

command='INSERT INTO Alarms ( Description, Startt,Temp) VALUES ('+ mess+ ','+ Temp1.Time+ ',' +Temp1[0]+')'

 i Also try  command='INSERT INTO Alarms ( Description, Startt,Temp) VALUES (''"High Temperature",'+ Temp1.Time+ ',' +Temp1[0]+')'

 

maybe you know why its not working ? 

 

Roy

[AzeoTech]

You have to have quotes inside your sql statement.  Then you also need quotes to build your string.  So you need to pick one form of quotes for the SQL statement, and one form to build the string.  In both your cases, you mixed the quotes.  So, let's say you use single quotes to build the string, and double quotes inside the SQL statement:

 

command='INSERT INTO Alarms ( Description, Startt,Temp) VALUES ("'+ mess+ '",'+ Temp1.Time+ ',' +Temp1[0]+')'

 

Now, the best way to debug this sort of thing is to simply put a ? statement after you build the string to make sure it looks like what you want:

 

? command

 

Then you'd be able to quickly tell if you messed up the SQL formatting.

[AzeoTech]

BTW: there is a variable:

 

db.debugPrint

 

If you set this to 1, it will cause all sql statements to be printed to the command / alert window as they are executed.  This is a handy tool for debugging.  Set it back to 0 to disable.

[roiyjet]

unfortunately its not work , the command I get is fine but I am getting error while I try it as String

( I change in access to Number , test it  and its work fine )  

[AzeoTech]

You've got to give me more than that. Its certainly a syntax error in your sql statement.  Please post exactly what the resultant sql statement is (by using the ? statement to print it out, or debugPrint)

[roiyjet]

OK i am sending the db.debugPrint

 

Here I am trying write String and the access format is text 

where line 28 is  qr = db.Query(dbase, command)

 

DB.Open:DSN=Alarms;ODBC;UID=;PWD=;

DB.Query:INSERT INTO Alarms ( Description, Startt,Temp) VALUES ("high temperature",1408573849.499,0.641405878969);

08/20/14 22:30:49.505

O1004 Unable to run query: test Line 28 - Uncaught error in sequence test

 

 

Here i am writing number and its working fine

 

DB.Open:DSN=Alarms;ODBC;UID=;PWD=;

DB.Query:INSERT INTO Alarms ( Description, Startt,Temp) VALUES (23,1408573821.078,0.847697205678);

DB.CloseQuery

[AzeoTech]

The problem is most likely you are using db.query().  You should only use query() or queryToClass() when you are actually performing a query (i.e. SELECT).  Any other function you have to use db.execute().  db.execute() will send the string to the database as is, so any errors are related to your SQL syntax.  db.query() and db.queryToClass() actually look at the SQL statement and do things with it to process the returned data correctly, and as such only work with SELECT.

[roiyjet]

The same problem with the execute()  

its work fine with number but not with string 

 

I wrote

private string mess

mess='high temperature'

 

  command='INSERT INTO Alarms ( Description) VALUES ("'+ mess  +'")'

   dbase=db.Open('Alarms')

    db.execute(dbase, command)   

   db.Close(dbase)

 

I get  

DB.Open:DSN=Alarms;ODBC;UID=;PWD=;

DB.Execute:INSERT INTO Alarms ( Description) VALUES ("high temperature")

08/20/14 23:51:12.965

Unable to execute: HY010: test Line 27 - Uncaught error in sequence test

 

[AzeoTech]

Then the problem is in the Alarms table schema.  Please post the result of DESCRIBE or however you'd like to get the format of the table.

[roiyjet]

Description Text 255

AggregateType: -1

AllowZeroLength: True

AppendOnly: False

Attributes: Variable Length

CollatingOrder: General

ColumnHidden: False

ColumnOrder: Default

ColumnWidth: Default

CurrencyLCID: 0

DataUpdatable: False

DisplayControl: Text Box

GUID: {guid {9E8C83FF-2579-49DC-9AC9-CCC1D5D272B2}}

IMEMode: 0

IMESentenceMode: 3

OrdinalPosition: 1

Required: False

ResultType: 0

SourceField: Description

[roiyjet]

when I copy the command to sql query inside the access its work fine 

[AzeoTech]

Sorry, that's not the table format.  I want to know what type of field Description is.

 

Also, I don't know, but Access may require a semicolon even through ODBC.  MySQL does not.

[thuatnguyenbk]

Hi admin, i have a similar problem. but i want to read a text columm in a table on mySQL database. Please give me some script to read.

 

Thanks.

[AzeoTech]

Please do DESCRIBE so I can see exactly what the table format is.

[thuatnguyenbk]

Hi admin, this is my tabel, and i want to read field content  to a global string smsContent. Please help me.

 

Many thanks.

 

 

Field        Type     Null    Key     Default  Extra           

-----------  -------  ------  ------  -------  ----------------

id           int(11)  NO      MUL     (NULL)   auto_increment  

idUse        int(11)  YES             (NULL)                   

Tel          int(11)  YES             (NULL)                   

Content      text     YES             (NULL)                   

Status       int(11)  YES             (NULL)                   

TimeRequest  double   YES             (NULL)                   

TimeSent     double   YES             (NULL)               

[AzeoTech]

ok, you would want to do something like:

 

private dbase = db.open("myODBCdatasource")

private datain = db.queryToClass(dbase, "select Content from myTable;")

smsContent = datain.content

db.close(dbase)

 

You can, of course make dbase global and open() once at startup instead of every time you read.

[thuatnguyenbk]

Thanks to reply, i has just tested with your code but i can't read a TEXT value, it's return 0. Please see some attached file. 

[AzeoTech]

It needs to be type char or varchar.

[thuatnguyenbk]

Oh, many thanks to admin, it's work. I can read a CHAR and VARCHAR column. Do you know why it' not work when a column is format as  TEXT.

[AzeoTech]

Type TEXT is a blob format, meaning it has an undefined length.  That means that you could store text strings that are megabytes or even gigabytes, depending on the database.  This makes it much more complicated to retrieve the values over ODBC, and also makes it much slower.  For these reasons, we never implemented a blob retrieval.  In the 10 years or so that we have supported ODBC, this is the first time a customer has tried to access a text format.

 

TEXT is slower and less efficient even on the database side, so if you can, you really should use VARCHAR instead of TEXT.  Note that VARCHAR(255) does not actually take 255 bytes when stored (in most databases).  There is still a length byte.  The difference is that the max length is known when the table is created and so the database can optimize for it.  With blob types, most databases simply create their own sort of internal file system and store the blob data separate from the main table.  Exactly how this is done depends on the database.